On Mon, Aug 29, 2005 at 03:07:59PM -0400, Tom Lane wrote: > Stijn Hoop <stijn@xxxxxxxxxx> 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
Attachment:
pgpi3SoZ7Bof5.pgp
Description: PGP signature
