Re: Missing warning on revokes with grant options

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