Re: revoke on database not working as expected

From: Stijn Hoop <stijn(at)win(dot)tue(dot)nl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: revoke on database not working as expected
Date: 2005-08-29 20:00:48
Message-ID: 20050829200048.GU67999@pcwin002.win.tue.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jeffrey Melloy 2005-08-29 20:09:27 Re: POSS. FEATURE REQ: "Dynamic" Views
Previous Message Tom Lane 2005-08-29 19:07:59 Re: revoke on database not working as expected