I've been thinking about this some more and reading the SQL99 spec. In the original thread that added these warnings [0], which was linked earlier in this thread by Nathan, the following assertion was made:
> After that, you get to the General Rules, which pretty clearly say that > trying to grant privileges you don't have grant option for is just a > warning and not an error condition. (Such privileges will not be in the > set of "identified privilege descriptors".) > > AFAICS the specification for REVOKE is exactly parallel. I think it is true that for both GRANT and REVOKE, if a privilege was specified in the statement and a corresponding privilege does not exist in the identified set then a warning should be issued. However, the meaning of "identified set" is different between GRANT and REVOKE. In GRANT the identified set is defined as 4) A set of privilege descriptors is identified. The privilege descriptors identified are those defining, for each <action> explicitly or implicitly in <privileges>, that <action> on O held by A with grant option. Essentially it is all privileges specified in the GRANT statement on O **where by A is the grantee with a grant option**. In REVOKE the identified set is defined as 1) Case: a) If the <revoke statement> is a <revoke privileges statement>, then for every <grantee> specified, a set of privilege descriptors is identified. A privilege descriptor P is said to be identified if it belongs to the set of privilege descriptors that defined, for any <action> explicitly or implicitly in <privileges>, that <action> on O, or any of the objects in S, granted by A to <grantee>. Essentially it is all privileges specified in the REVOKE statement on O **where A is the grantor and the grantee is one of the grantees specified in the REVOKE statement**. In fact as far as I can tell, the ability to revoke a privilege does not directly depend on having a grant option for that privilege, it only depends on being the grantor of the specified privilege. However, our code in restrict_and_check_grant doesn't match this. It treats the rules for GRANTs and REVOKEs the same, in that you need a grant option to execute either. It's possible that due to the abandoned privilege rules that it is impossible for a privilege to exist where the grantor doesn't also have a grant option on that privilege. I haven't read that part of the spec closely enough. As a consequence of how the identified set is defined for REVOKE, not only should a warning be issued in the example from my previous email, but I think a warning should also be issued even if the grantee has no privileges on O. For example, ``` test=# SELECT current_role; current_role -------------- joe (1 row) test=# CREATE TABLE t (); CREATE TABLE test=# CREATE ROLE r1; CREATE ROLE test=# SELECT relacl FROM pg_class WHERE relname = 't'; relacl -------- (1 row) test=# REVOKE SELECT ON t FROM r1; REVOKE ``` Here the identified set for the REVOKE statement is empty. So there is no corresponding privilege descriptor in the identified set for the SELECT privilege in the REVOKE statement. So a warning should be issued. Recall: 18) If the <revoke statement> is a <revoke privileges statement>, then: a) For every combination of <grantee> and <action> on O specified in <privileges>, if there is no corresponding privilege descriptor in the set of identified privilege descriptors, then a completion condition is raised: warning — privilege not revoked Essentially the meaning of the warning for REVOKE does not mean "you tried to revoke a privilege but you don't have a grant option", it means "you tried to revoke a privilege (where you are the grantor), but such a privilege does not exist". Thanks, Joe Koshakow [0] https://postgr.es/m/20040511091816.E9887CF519E%40www.postgresql.com