Re: Mystery with REVOKE PRIVILEGE

Konstantin Knizhnik <knizhnik(at)garret(dot)ru> writes:
> But I wonder if we do refactoring of this revoke privileges stuff,
> should we also provide correct (expected) behaviour in case of missing
> grantor specification. i.e.

>      revoke all privileges on table <T> from <role>;

> If privileges to access this table were granted to this role by multiple
> grantors, then it is natural to expect that the statement above will
> remove all such grants and so as a result <role> can not access this
> table any more, rather than try to find best grantor and finally still
> leave privileges for this role, isn't it?

Unfortunately, the SQL spec is quite clear that REVOKE revokes only
privileges granted directly by the calling user (or the GRANTED BY
role, if that's given). We're already far outside the spec by
allowing select_best_grantor to locate an inherited role to do the
revoke as. I can't see reinterpreting it as "revoke all privileges
granted by anybody", even assuming that the calling user has
sufficient permissions to do that.

regards, tom lane