Re: 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.

Going from memory here…

“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.

David J.