Re: [GENERAL] How does connect privilege works?
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
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How does connect privilege works?
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
