Shridhar Daithankar wrote: > I am trying to setup a cluster for trac databases and want to isolate each db, by assigning a specific > user to a DB. > > I followed the documentation but as shown in the following example, limiting access by connect does > not seem to be working. > > What am I missing?
The fact that by default the CONNECT privilege is granted to PUBLIC, so everybody can connect. > shridhar@bheem ~$ createuser testuser1 > shridhar@bheem ~$ createdb testdb2 > > shridhar@bheem ~$ psql testdb2 > testdb2=# revoke connect ON database testdb2 FROM testuser1; > REVOKE > testdb2=# \q > > shridhar@bheem ~$ psql -U testuser1 testdb2 > psql (9.1.4) > Type "help" for help. > testdb2=> \q PostgreSQL privileges are additive, you cannot specifically deny a privilege to a certain user when the privilege is granted to PUBLIC. The REVOKE-Statement you quote does nothing (no CONNECT privilege was granted to "testuser1"). What you'll have to do is first REVOKE CONNECT ON DATABASE testdb2 FROM PUBLIC and then grant it to those users that you want to be able to connect. A good idea would be to create a role "testdb2_users", grant this role CONNECT and then add everybody to the role who should be allowed to connect. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
