Re: REVOKE FROM warning on grantor

=?ISO-8859-1?Q?=C9tienne?= BERSAC <etienne(dot)bersac(at)dalibo(dot)com> writes:
> I'll try to patch the behaviour to ensure an error if the REVOKE is
> ineffective.

I think we're unlikely to accept such a patch. By my reading, the way
we do it now is required by the SQL standard. The standard doesn't
seem to say that in so many words; what it does say (from SQL99) is

b) If the <revoke statement> is a <revoke role statement>, then
for every <grantee> specified, a set of role authorization
descriptors is identified. A role authorization descriptor is
said to be identified if it defines the grant of any of the
specified <role revoked>s to <grantee> with grantor A.

It does not say that that set must be nonempty. Admittedly it's not
very clear from this one point. However, if you look around in the
standard it seems clear that they expect no-op revokes to be no-ops
not errors. As an example, every type of DROP command includes an
explicit step to drop privileges attached to the object, with wording
like (this is for ALTER TABLE DROP COLUMN):

3) Let A be the <authorization identifier> that owns T. The
following <revoke statement> is effectively executed with
a current authorization identifier of "_SYSTEM" and without
further Access Rule checking:

REVOKE INSERT(CN), UPDATE(CN), SELECT(CN), REFERENCES(CN) ON
TABLE TN FROM A CASCADE

There is no special rule for the case that all (or any...) of those
privileges were previously revoked; but if that case is supposed to be
an error, there would have to be an exception here, or you couldn't
drop such columns.

Even taking the position that this is an unspecified point that we
could implement how we like, I don't think there's a sufficient
argument for changing behavior that's stood for a couple of decades.
(The spelling of the message has changed over the years, but giving a
warning not an error appears to go all the way back to 99b8f8451
where we implemented user groups.) It is certain that there are
applications out there that rely on this behavior and would break.

regards, tom lane