October 18th, 2002, 11:46 PM
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.
October 19th, 2002, 09:33 AM
pg_ident.conf & pg_hba.conf
If you do not want use GRANT and REVOKE,
you can use pg_ident.conf and pg_hba.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
i1 user1 postgres
i1 user3 postgres
i2 user2 postgres
i2 user3 postgres
Programmer of Rapid Internet Development Department
Alar Information Technologies, www.alarit.com
October 20th, 2002, 04:09 PM
an easy way to do this is to simply create the database as the user you'd like to restrict the database to
PostgreSQL web Hosting http://www.pghoster.com
October 21st, 2002, 01:11 AM
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'.
Last edited by ggnanaraj; October 21st, 2002 at 06:31 AM.
October 21st, 2002, 05:31 AM
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
GRANT SELECT ON hris TO u_hris