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
