Postgres Pro
Downloads
Home   >   mailing lists

Thread: How does connect privilege works?

How does connect privilege works?

From
Shridhar Daithankar
Date:

Hello,

 

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?

 

------------

shridhar@bheem ~$ createuser testuser1

Shall the new role be a superuser? (y/n) n

Shall the new role be allowed to create databases? (y/n) y

Shall the new role be allowed to create more new roles? (y/n) y

 

shridhar@bheem ~$ createdb -O testuser1 testdb1

 

shridhar@bheem ~$ createdb testdb2

 

shridhar@bheem ~$ psql testdb2

psql (9.1.4)

Type "help" for help.

 

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

 

 

shridhar@bheem ~$ psql test

psql (9.1.4)

Type "help" for help.

 

test=# select version();

version

---------------------------------------------------------------------------------------------------------

PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.0 20120505 (prerelease), 64-bit

(1 row)

 

------------

--

Regards

Shridhar

Re: How does connect privilege works?

From
Craig Ringer
Date:
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

Re: How does connect privilege works?

From
"Albe Laurenz"
Date:
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