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(at)plukwa(dot)net