Re: database privileges and access control

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