Re: Extended security/restriction to any role with login access

Hello, Domingo,

My question is why do your users need access to pgadmin3?
I have not used pgadmin3; we use phpPgAdmin.  I can restrict access to that by putting it behind .htaccess.  That is only users with a user name in .htacess can run phpPgAdmin.  In the case of pgadmin3, shouldn't you be able to restrict access to it by setting privs at the operating system level?  With phpPgAdmin, I can also restrict it so a user can only see the databases the s/he owns.  Postgres owns my databases so I can't do it this way, but it could be done.

Carol

On Jun 26, 2008, at 1:04 PM, Domingo Alvarez Duarte wrote:

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 !