Hi Hackers, I noticed some confusing behavior with REVOKE recently. Normally if REVOKE fails to revoke anything a warning is printed. For example, see the following scenario:
``` test=# SELECT current_role; current_role -------------- joe (1 row) test=# CREATE ROLE r1; CREATE ROLE test=# CREATE TABLE t (); CREATE TABLE test=# GRANT SELECT ON TABLE t TO r1; GRANT test=# SET ROLE r1; SET test=> REVOKE SELECT ON TABLE t FROM r1; WARNING: no privileges could be revoked for "t" WARNING: no privileges could be revoked for column "tableoid" of relation "t" WARNING: no privileges could be revoked for column "cmax" of relation "t" WARNING: no privileges could be revoked for column "xmax" of relation "t" WARNING: no privileges could be revoked for column "cmin" of relation "t" WARNING: no privileges could be revoked for column "xmin" of relation "t" WARNING: no privileges could be revoked for column "ctid" of relation "t" REVOKE test=> SELECT relacl FROM pg_class WHERE relname = 't'; relacl ----------------------------- {joe=arwdDxtm/joe,r1=r/joe} (1 row) ``` However, if the REVOKE fails and the revoker has a grant option on the privilege, then no warning is emitted. For example, see the following scenario: ``` test=# SELECT current_role; current_role -------------- joe (1 row) test=# CREATE ROLE r1; CREATE ROLE test=# CREATE TABLE t (); CREATE TABLE test=# GRANT SELECT ON TABLE t TO r1 WITH GRANT OPTION; GRANT test=# SET ROLE r1; SET test=> REVOKE SELECT ON TABLE t FROM r1; REVOKE test=> SELECT relacl FROM pg_class WHERE relname = 't'; relacl ------------------------------ {joe=arwdDxtm/joe,r1=r*/joe} (1 row) ``` The warnings come from restrict_and_check_grant() in aclchk.c. The psuedo code is if (revoked_privileges & available_grant_options == 0) emit_warning() In the second example, `r1` does have the proper grant options so no warning is emitted. However, the revoke has no actual effect. Reading through the docs [0], I'm not actually sure if the REVOKE in the second example should succeed or not. At first it says: > A user can only revoke privileges that were granted directly by that > user. If, for example, user A has granted a privilege with grant > option to user B, and user B has in turn granted it to user C, then > user A cannot revoke the privilege directly from C. Which seems pretty clear that you can only revoke privileges that you directly granted. However later on it says: > As long as some privilege is available, the command will proceed, but >it will revoke only those privileges for which the user has grant > options. ... > while the other forms will issue a warning if grant options for any > of the privileges specifically named in the command are not held. Which seems to imply that you can revoke a privilege as long as you have a grant option on that privilege. Either way I think the REVOKE should either fail and emit a warning OR succeed and emit no warning. I wasn't able to locate where the check for > A user can only revoke privileges that were granted directly by that > user. is in the code, but we should probably just add a warning there. - Joe Koshakow [0] https://www.postgresql.org/docs/15/sql-revoke.html