From: Domingo Alvarez Duarte <mingodad@gmail.com>
To: Carol Walter <walterc@indiana.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: Extended security/restriction to any role with login access
Date: Fri, 27 Jun 2008 07:43:51 +0200
Message-ID: <c2f38c3e0806262243o3e169093kb6c4aa6bf26f232a@mail.gmail.com> (raw)
In-Reply-To: <2D85373C-758F-4545-BE65-22A5E5A4B3A8@indiana.edu>
References: <c2f38c3e0806261004k75bc16f1j9d1e9047320f5d1f@mail.gmail.com>
<2D85373C-758F-4545-BE65-22A5E5A4B3A8@indiana.edu>
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@indiana.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 !
>>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mingodad@gmail.com, walterc@indiana.edu
Subject: Re: Extended security/restriction to any role with login access
In-Reply-To: <c2f38c3e0806262243o3e169093kb6c4aa6bf26f232a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox