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