Re: database privileges and access control

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