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

My users will not need to use pgadmin3, the whole problem is any user with
login access to a postgresql server can use pgadmin3 to connect and see all
databases/roles/functions/table-definitions/schemas. This is not a problem
with pgadmin3 but with postgresql server.

Look at it in a general way someone write one application that will connect
to postgresql server (intranet or internet) the user of that application
will see mor or less data/functionality based on her/his credentials
(username/password) that are stored on postgresql and assigned grant access
to the correspondig part of the database.

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.

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.

On Thu, Jun 26, 2008 at 7:34 PM, Carol Walter <walterc(at)indiana(dot)edu> wrote:

> 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 !
>>
>
>