Re: revoke on database not working as expected

On Mon, Aug 29, 2005 at 03:07:59PM -0400, Tom Lane wrote:
> Stijn Hoop <stijn(at)win(dot)tue(dot)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