Thread: Privilege on schema 'public' not revokable

Hi,

after having been locked-out of the public schema by mistake (which I fixed with "GRANT ALL ON SCHEMA public TO 'foobar'"), I wanted to update my privilege-check script to take the schema into account, but I'm running into the following behaviour :

> $ psql db_foo

> psql (9.1.2)

> Type "help" for help.

>

> db_foo=# REVOKE CREATE ON SCHEMA public FROM foouser;

> REVOKE

> db_foo=# SELECT has_schema_privilege('foouser', 'public', 'CREATE');

> has_schema_privilege

> ----------------------

> t

> (1 row)

>

> db_foo=# \q

> $ psql db_foo -U foouser

> psql (9.1.2)

> Type "help" for help.

>

> db_foo=> create table tokill2(a int);

> CREATE TABLE

> db_foo=>

This is observable in 9.1.2 and 8.3.18 (gentoo linux packages). Same issue with USAGE privilege. 'foouser' is neither a superuser nor the owner of any database object.

Either I misunderstood something, or something is going awry (I expected has_schema_privilege() to return 'f', and not being allowed to create a table as user 'foouser').

I'm also wondering how I managed to revoke the privilege (symptom: "no such table 'foobar'" messages when logged-in as the problematic user) in the first place. I was using slony 2.1 to migrate from PG8.3 to PG 9.1. Not all migrated databases were affected by the problem.

As an aside, is there any better way to check existing privileges ? I need to call the has_*_privilege() function for each privilege type to get an exact view, which is cumbersome. I was using pg_class.relacl before, but it doesn't support all object types and is not an official interface. Some of the tables in information_schema look ideal, but again not all object types are covered.

Thanks in advance.

--

Vincent de Phily

"David Johnston" <polobo@yahoo.com> writes:
> [mailto:pgsql-general-owner@postgresql.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

On Wednesday 29 February 2012 14:14:19 Tom Lane wrote:

> "David Johnston" <polobo@yahoo.com> writes:

> > [mailto:pgsql-general-owner@postgresql.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