DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=hAlzDf0Gg1jQEhFMuouxwZ5DBSZIKMvu7WdBuF79djeclK1jHEryznt/37OGe2Xn8F
	12oR0i1B0Njkj8ypaWlgop4sAv+dU2gD1yxHvAAqGE0+vq7LQ43xpD82uyBIn03PGjZk
	vIyo940iF3g1dlmvFC6h5NgbfCQZSqPqjR3Eo=
Message-ID: <c2f38c3e0806261004k75bc16f1j9d1e9047320f5d1f@mail.gmail.com>
Date: Thu, 26 Jun 2008 19:04:54 +0200
From: "Domingo Alvarez Duarte" <mingodad@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Extended security/restriction to any role with login access
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_5461_22723884.1214499894551"

------=_Part_5461_22723884.1214499894551
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

 Hello !

I'm trying to use postgresql in an application that by design will give
access to users to a subset of the database.

For example for customers access to products_view (wich will only show
public offers), orders (only their own orders).

I'll provide an application as user interface for the data.

For that I'll give for each of then a role in the database that will belong
to a group role customers_group.

The customers_group only has access to the views/functions that I'll
specify.

Till here no problem postgresql do that pretty well.

My concern is once I give login access to any user, even without grant
him/her any access to any database, he/she can using an application like
pgadmin3 view all databases/roles/functions/table-definitions on my server.
And that was not my intention.

Removing all from public doesn't work : revoke all on schema public from
public;

What I think would be the server behavior when I create a role with login
access an say that I only grant access to one view like this:

create role oneuser login;
grant select on somedatabase.someview to oneuser;

In that case when the user login the only thing he/she sees is the view
database.someview, even when they use pgadmin3 to connect.

Actually he/she can see with pgadmin3 : all databases, all roles and it's
right access, all tables on every database (no access to data), all
functions, all triggers, all table definitions.

The above isn't the intention to a user with a restrict view of the
database.

Can I achieve it actually, if not how hard could be to implement that in the
official release ?

Thanks in advance for any feedback/ideas !

------=_Part_5461_22723884.1214499894551
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


Hello !<br><br>I&#39;m trying to use postgresql in an application that by design will give access to users to a subset of the database.<br><br>For example for customers access to products_view (wich will only show public offers), orders (only their own orders).<br>

<br>I&#39;ll provide an application as user interface for the data.<br><br>For that I&#39;ll give for each of then a role in the database that will belong to a group role customers_group.<br><br>The customers_group only has access to the views/functions that I&#39;ll specify.<br>

<br>Till here no problem postgresql do that pretty well.<br><br>My
concern is once I give login access to any user, even without grant
him/her any access to any database, he/she can using an application
like pgadmin3 view all databases/roles/functions/table-definitions on
my server. And that was not my intention.<br>
<br>Removing all from public doesn&#39;t work : <span>revoke all on schema public from public;<br><br>What
I think would be the server behavior when I create a role with login
access an say that I only grant access to one view like this:<br>
<br>create role oneuser login;<br>grant select on somedatabase.someview to oneuser;<br><br>In
that case when the user login the only thing he/she sees is the view
database.someview, even when they use pgadmin3 to connect.<br>
<br>Actually he/she can see with pgadmin3 : all databases, all roles
and it&#39;s right access, all tables on every database (no access to
data), all functions, all triggers, all table definitions.<br><br>The above isn&#39;t the intention to a user with a restrict view of the database.<br>
<br>Can I achieve it actually, if not how hard could be to implement that in the official release ?<br><br>Thanks in advance for any feedback/ideas !</span>

------=_Part_5461_22723884.1214499894551--