Re: Privilege on schema 'public' not revokable

On Wednesday 29 February 2012 14:14:19 Tom Lane wrote:
> "David Johnston" <polobo(at)yahoo(dot)com> writes:
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Vincent de Phily
> >
> >> [ this doesn't do anything: ]
> >> db_foo=# REVOKE CREATE ON SCHEMA public FROM foouser;
> >
> > "foouser" is obtaining its permission to "CREATE" on the "public " schema
> > via global/PUBLIC permissions (i.e., via inheritance). Revoking only
> > removes an explicitly granted permission but does not institute a block
> > for
> > an inherited permission. You would need to revoke the global permission
> > to
> > CREATE on "public" and then only GRANT it back to those users/roles that
> > you wish to have it - all others will then effectively lose that ability.
> Correct. Note where it says in the GRANT manual page that a user's
> effective rights are the sum of those granted to PUBLIC, those granted
> directly to him, and those granted to roles he is a member of. Rights
> granted to PUBLIC are available to everybody, full stop, and can't be
> selectively blocked.
>
> regards, tom lane

Ah thanks, that's what I had been thinking during the night, but you confirmed
it. Leaves me wondering how I lost the public permission in the first place,
but hey :p

Also, how do I see the privileges granted to public on schema ?

--
Vincent de Phily