BUG #3319: Superuser can't revoke grants on a schema given by aother user - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3319
Logged by:          Pedro Gimeno
Email address:      pgsql@personal.formauri.es
PostgreSQL version: 8.2.4
Operating system:   Linux
Description:        Superuser can't revoke grants on a schema given by
aother user
Details:

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. If a SET ROLE to the grantor is issued then the REVOKE
works as expected. I have not tried with a CREATE privilege.

Here's a test case suitable for psql:

CREATE USER user1;
CREATE USER user2;
CREATE DATABASE test1;
\c test1
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO user1 WITH GRANT OPTION;
SET SESSION AUTHORIZATION user1;
GRANT USAGE ON SCHEMA public TO user2;
RESET SESSION AUTHORIZATION;
REVOKE USAGE ON SCHEMA public FROM user2;
\dn+ public
-- {...,user1=U*/postgres,user2=U/user1}
-- note user2 has still usage privileges
SET ROLE user1;
REVOKE USAGE ON SCHEMA public FROM user2;
RESET ROLE;
\dn+ public
-- {...,user1=U*/postgres}
-- this is expected