Re: Privilege on schema 'public' not revokable

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Vincent de Phily
Sent: Wednesday, February 29, 2012 1:57 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] 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.