On 20/01/2026 9:26 PM, Tom Lane wrote:
Konstantin Knizhnik <[email protected]> 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.
