Thread: revoke on database not working as expected
Hi, I'm running into a setup problem (I guess) while trying to prevent a user from creating tables in a database. The setup is a FreeBSD 5.4 database server accessed from a FreeBSD 6.0 development box, both running PostgreSQL 8.0.3. This is what I have configured on the database server (firsa): %%% [pgsql@firsa] </net/postgresql> tail pg_hba.conf local all @users md5 host all @users 127.0.0.1/32 md5 host all @users 192.168.1.0/24 md5 local privtest testpriv md5 host privtest testpriv 127.0.0.1/32 md5 host privtest testpriv 192.168.1.0/24 md5 [pgsql@firsa] </net/postgresql> cat users stijn %%% This is what I do on the dev box (tangaloor): %%% [stijn@tangaloor] <~> host tangaloor tangaloor.lzee.sandcat.nl has address 192.168.1.105 [stijn@tangaloor] <~> psql -U stijn -h firsa template1 Password: Welcome to psql 8.0.3, the PostgreSQL interactive terminal. template1=# \du List of users User name | User ID | Attributes | Groups -----------+---------+----------------------------+-------- pgsql | 1 | superuser, create database | stijn | 100 | superuser, create database | template1=# create database privtest; CREATE DATABASE template1=# create user testpriv password 'password'; CREATE USER template1=# \c privtest testpriv Password: You are now connected to database "privtest" as user "testpriv". privtest=> create table foo (i varchar(40)); CREATE TABLE privtest=> \c template1 stijn Password: You are now connected to database "template1" as user "stijn". template1=# revoke all on database privtest from testpriv; REVOKE template1=# \c privtest testpriv Password: You are now connected to database "privtest" as user "testpriv". privtest=> create table bar (i varchar(40)); CREATE TABLE %%% My question is: why can I still create table 'bar', in a database that was not created by user 'testpriv', having explicitly revoked all privileges on that database by a superuser? What access credential am I missing? I've searched the handbook but all it says is 'use \z in psql to view privileges' and that doesn't list general database privileges. Thanks for any clues. Please CC me as I'm not subscribed. --Stijn -- The right half of the brain controls the left half of the body. This means that only left handed people are in their right mind.
Stijn Hoop <stijn@win.tue.nl> writes: > template1=# revoke all on database privtest from testpriv; That doesn't do what you evidently think it does --- it revokes the right to create temp tables, and the right to create new schemas, but not every right in existence. Please read the GRANT/REVOKE manual pages. (Hint: revoking CREATE on the public schema would get you closer to what you want.) regards, tom lane
On Mon, Aug 29, 2005 at 03:07:59PM -0400, Tom Lane wrote: > Stijn Hoop <stijn@win.tue.nl> writes: > > template1=# revoke all on database privtest from testpriv; > > That doesn't do what you evidently think it does --- it revokes the > right to create temp tables, and the right to create new schemas, but > not every right in existence. Please read the GRANT/REVOKE manual > pages. (Hint: revoking CREATE on the public schema would get you closer > to what you want.) > > regards, tom lane Thanks for answering! I assure you that I read those pages. In fact, quoted from: http://www.postgresql.org/docs/8.0/interactive/sql-grant.html "Depending on the type of object, the initial default privileges may include granting some privileges to PUBLIC. The default is no public access for tables, schemas, and tablespaces;" This misled me greatly. Apparently this is only for explicitly created schema's? Anyway, I found out about psql's \dn+, and that in combination with your hint was enough: %%% You are now connected to database "privtest" as user "stijn". privtest=# revoke create on schema public from public; REVOKE privtest=# \c privtest testpriv Password: You are now connected to database "privtest" as user "testpriv". privtest=> create table plover (i varchar(40)); ERROR: permission denied for schema public %%% which is what I was after. Many thanks! Might I suggest a hint in this direction somewhere in the text of REVOKE and GRANT? --Stijn -- It's harder to read code than to write it. -- Joel Spolsky, http://www.joelonsoftware.com/articles/fog0000000069.html