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
