Re: Mystery with REVOKE PRIVILEGE

On 27/01/2026 10:10 PM, Konstantin Knizhnik wrote:
>
> On 22/01/2026 6:35 PM, Tom Lane wrote:
>> Konstantin Knizhnik <knizhnik@garret.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
>
>
> Can I ask one more question.
> What do you think about the following (similar) scenario:
>
>
> create role creator superuser;
> set role creator;
> create role reader;
> create role somebody;
> grant reader to somebody;
> grant creator to somebody;
> create table t(x integer);
> grant select on table t to somebody with grant option;
> begin;
> set local role somebody;
> grant select on table t to reader;
> commit;
>
> drop owned by reader cascade;
> drop role reader;
> ERROR:  role "reader" cannot be dropped because some objects depend on it
> DETAIL:  privileges for table t
>
>
> What standard is saying about DROP OWNER BY ... CASCADE?
> Should it delete reader's privileges in this case?
>
> There is simple "know-how" in Postgres  how to drop role having 
> dependent objects:
> REASSIGN OWNED BY ... TO ...;
> DROP OWNED BY ...;
>
> But it doesn't work in the case above.
> It it necessary to manually locate and drop all granted privileges.
> And there are more than 10 kind of objects in Postgres to which 
> privileges is granted.
> So if you need to write procedure which is guaranteed to drop any 
> role, then there is no simple solution, is it?
>
>

I can propose such straightforward patch fixing this issue with "DROP 
OWNED BY...":
it just removes from ACL any items with specified grantee.