The following bug has been logged online:
Bug reference: 3319
Logged by: Pedro Gimeno
Email address: [EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
