Thread: database privileges and access control

database privileges and access control

From
Roman Gavrilov
Date:
Hello,

I have 2 questions.
The first one (I know it been discussed already, but I did not find the
solution).
If I have user A and user B and database DB1 and database DB2 and only
local connections.


How can I configure the pg_hba.conf to let user A connect only to the
DB1 database and let user B connect only to the DB2 database.
The sameuser param is not good here.


local   sameuser        password
local   all                   password        admins

The file $PGDATA/admins contains the usernames of all users that allowed
to connect to all databases.
I tried to add next line
local    DB1               password        DB1_users
local    DB2               password        DB2_users

and added the users that allowed to connect to each database to those
files accordingly.
The user can connect to sameuser database but not to the DB1 or DB2
database with error incorrect password.

What is the problem ?


Second problem is :
How can I make users to see only the database that they own with the \l
command ?
I don't want users to see all the databases on this host but only those
that they own.

Thanks
Roman

--
-----------------------------------------------------------------------------
 Roman Gavrilov
 Aduva Inc., Web Development Services.
 work +972-3-7534373 mobile +972-54-834668
 romio@aduva.com, romio@netvision.net.il




Re: database privileges and access control

From
Roman Gavrilov
Date:
Artur Pietruk wrote:

>On Wed, Mar 05, 2003 at 03:37:16PM +0200, Roman Gavrilov wrote:
>
>
>>Hello,
>>
>>I have 2 questions.
>>If I have user A and user B and database DB1 and database DB2 and only
>>local connections.
>>
>>How can I configure the pg_hba.conf to let user A connect only to the
>>DB1 database and let user B connect only to the DB2 database.
>>The sameuser param is not good here.
>>
>>local   sameuser        password
>>local   all                   password        admins
>>
>>The file $PGDATA/admins contains the usernames of all users that allowed
>>to connect to all databases.
>>I tried to add next line
>>local    DB1               password        DB1_users
>>local    DB2               password        DB2_users
>>
>>and added the users that allowed to connect to each database to those
>>files accordingly.
>>The user can connect to sameuser database but not to the DB1 or DB2
>>database with error incorrect password.
>>
>>What is the problem ?
>>
>>
>
>    Which version of PostgreSQL are you using?
>
>    With PG 7.3 there is easy solution to your problem. In that
>version, there is "user" field, for pg_hba.conf:
>
># TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK    METHOD
>
>    For your PG configuration - try to change order of entries in
>pg_hba.conf (order does matter), e.g. make it something like this:
>
>====8<====
>local    DB1               password        DB1_users
>local    DB2               password        DB2_users
>local   all                   password        admins
>local   sameuser        password
>====8<====
>
>    Restart pgsql and see.
>
>
The version is 7.2.1
and I tried to play with the order like you saying , but still it didn't
help :(

>
>
>>Second problem is :
>>How can I make users to see only the database that they own with the \l
>>command ?
>>I don't want users to see all the databases on this host but only those
>>that they own.
>>
>>
>
>    AFAIK that's not possible. But I might be wrong - things were
>changing lately, check/search docs.
>
>    Best regards,
>
>

Thanks

--
-----------------------------------------------------------------------------
 Roman Gavrilov
 Aduva Inc., Web Development Services.
 work +972-3-7534373 mobile +972-54-834668
 romio@aduva.com, romio@netvision.net.il




Re: database privileges and access control

From
Artur Pietruk
Date:
On Wed, Mar 05, 2003 at 03:37:16PM +0200, Roman Gavrilov wrote:
> Hello,
>
> I have 2 questions.
> If I have user A and user B and database DB1 and database DB2 and only
> local connections.
>
> How can I configure the pg_hba.conf to let user A connect only to the
> DB1 database and let user B connect only to the DB2 database.
> The sameuser param is not good here.
>
> local   sameuser        password
> local   all                   password        admins
>
> The file $PGDATA/admins contains the usernames of all users that allowed
> to connect to all databases.
> I tried to add next line
> local    DB1               password        DB1_users
> local    DB2               password        DB2_users
>
> and added the users that allowed to connect to each database to those
> files accordingly.
> The user can connect to sameuser database but not to the DB1 or DB2
> database with error incorrect password.
>
> What is the problem ?

    Which version of PostgreSQL are you using?

    With PG 7.3 there is easy solution to your problem. In that
version, there is "user" field, for pg_hba.conf:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK    METHOD

    For your PG configuration - try to change order of entries in
pg_hba.conf (order does matter), e.g. make it something like this:

====8<====
local    DB1               password        DB1_users
local    DB2               password        DB2_users
local   all                   password        admins
local   sameuser        password
====8<====

    Restart pgsql and see.

> Second problem is :
> How can I make users to see only the database that they own with the \l
> command ?
> I don't want users to see all the databases on this host but only those
> that they own.

    AFAIK that's not possible. But I might be wrong - things were
changing lately, check/search docs.

    Best regards,
--
--- Artur Pietruk, arturp@plukwa.net

Re: database privileges and access control

From
Roman Gavrilov
Date:
Roman Gavrilov wrote:

> Artur Pietruk wrote:
>
>> On Wed, Mar 05, 2003 at 03:37:16PM +0200, Roman Gavrilov wrote:
>>
>>
>>> Hello,
>>>
>>> I have 2 questions.
>>> If I have user A and user B and database DB1 and database DB2 and
>>> only local connections.
>>>
>>> How can I configure the pg_hba.conf to let user A connect only to
>>> the DB1 database and let user B connect only to the DB2 database.
>>> The sameuser param is not good here.
>>>
>>> local   sameuser        password
>>> local   all                   password        admins
>>>
>>> The file $PGDATA/admins contains the usernames of all users that
>>> allowed to connect to all databases.
>>> I tried to add next line
>>> local    DB1               password        DB1_users
>>> local    DB2               password        DB2_users
>>>
>>> and added the users that allowed to connect to each database to
>>> those files accordingly.
>>> The user can connect to sameuser database but not to the DB1 or DB2
>>> database with error incorrect password.
>>>
>>> What is the problem ?
>>>
>>
>>
>>     Which version of PostgreSQL are you using?
>>
>>     With PG 7.3 there is easy solution to your problem. In that
>> version, there is "user" field, for pg_hba.conf:
>>
>> # TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK    METHOD
>>
>>     For your PG configuration - try to change order of entries in
>> pg_hba.conf (order does matter), e.g. make it something like this:
>>
>> ====8<====
>> local    DB1               password        DB1_users
>> local    DB2               password        DB2_users
>> local   all                   password        admins
>> local   sameuser        password
>> ====8<====
>>
>>     Restart pgsql and see.
>>
>>
> The version is 7.2.1
> and I tried to play with the order like you saying , but still it
> didn't help :(
>
>>
>>
>>> Second problem is :
>>> How can I make users to see only the database that they own with the
>>> \l command ?
>>> I don't want users to see all the databases on this host but only
>>> those that they own.
>>>
>>
>>
>>     AFAIK that's not possible. But I might be wrong - things were
>> changing lately, check/search docs.
>>
>>     Best regards,
>>
>>
>
> Thanks




--
-----------------------------------------------------------------------------
 Roman Gavrilov
 Aduva Inc., Web Development Services.
 work +972-3-7534373 mobile +972-54-834668
 romio@aduva.com, romio@netvision.net.il