Re: Mystery with REVOKE PRIVILEGE

On 20/01/2026 9:26 PM, Tom Lane wrote:
> Konstantin Knizhnik <knizhnik(at)garret(dot)ru> writes:
>> I found some very confusing behaviour of REVOKE PRIVILEGE.
>> ...
>> where <ADMIN_ROLE> is any role with admin permissions under which you
>> logged in.
> "admin permissions" is not well-defined terminology in Postgres.
> I thought perhaps you meant "superuser", but some experimentation
> indicates that that role doesn't need any special permissions, it
> only has to be the table owner to produce the strange behavior.
>
>> So granted read privilege was not revoked.
> This seems like it may be a consequence of this statement in
> the REVOKE man page:
>
> If the role executing REVOKE holds privileges indirectly via more
> than one role membership path, it is unspecified which containing
> role will be used to perform the command. In such cases it is best
> practice to use SET ROLE to become the specific role you want to
> do the REVOKE as. Failure to do so might lead to revoking
> privileges other than the ones you intended, or not revoking
> anything at all.
>
> In this example, "somebody" is a member of the table owner role
> as well as having some privilege granted directly, so the ambiguity
> does exist. I didn't dig further than that. It does seem sad
> if we fail to revoke a privilege that is an exact match, though.
>
> regards, tom lane

Thank you for clarification.
The problem really seems to be in `select_best_grantor` - it choose
"wrong" grantor.
It seems to be the bug because as a result of this operation no
privilege is revoked at all (because then `merge_acl_with_grant` found
no match).
I wonder if `select_best_grantor` should always prefer exact match?

Why such use case actually happen?
We need to drop role (reader). But it can not be dropped because it was
granted some privileges.
And attempt to revoke all given privileges doesn't not succeed.