Re: database privileges and access control

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