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(at)aduva(dot)com, romio(at)netvision(dot)net(dot)il