Re: BUG #3319: Superuser can't revoke grants on a schema given by aother user

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Pedro Gimeno" <pgsql(at)personal(dot)formauri(dot)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