"Pedro Gimeno" <pgsql@personal.formauri.es> writes:
> When a USAGE grant on a SCHEMA is given by an user (non-superuser in my
> case), the superuser can't revoke it; instead the REVOKE statement is
> silently ignored.
This is not a bug. If you want to revoke the privilege, revoke the
GRANT OPTION you originally gave. For example:
test1=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+--------------------------------------------------------+------------------------
public | postgres | {postgres=UC/postgres,user1=U*/postgres,user2=U/user1} | Standard public schema
(1 row)
test1=# revoke grant option for usage on schema public from user1;
ERROR: dependent privileges exist
HINT: Use CASCADE to revoke them too.
test1=# revoke grant option for usage on schema public from user1 cascade;
REVOKE
test1=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+-----------------------------------------+------------------------
public | postgres | {postgres=UC/postgres,user1=U/postgres} | Standard public schema
(1 row)
Alternatively, since you are superuser, you can become user1 and revoke
the privilege he gave ...
regards, tom lane