How restrict users to a database ?

I need to restrict users access to a database.

Say, 'user1' can access only database 'db1'
'user2' can access database 'db2'
'user3' can access databases 'db1' & 'db2'

Can I do this using any configuration file, like pg_hba.conf, etc.?

Thanks for the input.

Regards.

If you do not want use GRANT and REVOKE,
you can use pg_ident.conf and pg_hba.conf



pg_hda.conf
--

host db1 0.0.0.0 0.0.0.0 ident i1
host db2 0.0.0.0 0.0.0.0 ident i2


pg_ident.conf
--

i1 user1 postgres
i1 user3 postgres
i2 user2 postgres
i2 user3 postgres


----
Sincerely,
Andrew A.Romanchenko
Programmer of Rapid Internet Development Department
Alar Information Technologies, URL

an easy way to do this is to simply create the database as the user you'd like to restrict the database to

-niyogi

PostgreSQL web Hosting http://www.pghoster.com

From what I see, pg_ident.conf is only a mapping of OS usernames to PostgreSQL usernames. Correct me if I am wrong.

However, what I need is a mapping of database username to databases...

For eg., if 'u_hris' is database username and
if 'hris' & 'dev' are PostgreSQL databases...

... I need a mapping of PostgreSQL username 'u_hris' to PostgreSQL database 'hris'.

Thanks.

Only database users that have been granted explicit access to the database with GRANT can access that database. By default, nobody can access a database unless you tell them they can.

It will be something like this:


GRANT ALL ON hris TO u_hris

or

GRANT SELECT ON hris TO u_hris

etc, etc,

http://www.postgresql.org/idocs/ind...privileges.html