How does connect privilege works?

On 08/07/2012 11:51 AM, Shridhar Daithankar wrote:

testdb2=# revoke connect ON database testdb2 FROM testuser1;

REVOKE

You can't revoke a permission that isn't set. PostgreSQL doesn't have
explicit deny rules, so you can only remove a grant.

The documentation on databases doesn't seem to cover that very well,
with no listing of the default permissions.

You probably want to:

REVOKE CONNECT ON DATABASE testdb2 FROM public;

which will leave only superusers and the database owner able to connect
to the DB. You can then:

GRANT CONNECT ON DATABASE testdb2 TO whoever;

--
Craig Ringer

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