Обсуждение: Mystery with REVOKE PRIVILEGE

Hi hackers!

I found some very confusing behaviour of REVOKE PRIVILEGE.
I wonder whether it is bug or feature:

create role reader;
create role somebody;
grant reader to somebody;
grant <ADMIN_ROLE> 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;
SELECT * FROM information_schema.role_table_grants AS rtg WHERE 
grantee='reader';
begin;
set local role somebody;
revoke all privileges on table t from reader granted by somebody;
commit;
SELECT * FROM information_schema.role_table_grants AS rtg WHERE 
grantee='reader';

where <ADMIN_ROLE> is any role with admin permissions under which you 
logged in.

The strange thing is output of last command:

postgres=# SELECT * FROM information_schema.role_table_grants AS rtg 
WHERE grantee='reader';
  grantor  | grantee | table_catalog | table_schema | table_name | 
privilege_type | is_grantable | with_hierarchy
----------+---------+---------------+--------------+------------+----------------+--------------+----------------
  somebody | reader  | postgres      | public       | t          | 
SELECT         | NO           | YES
(1 row)


So  granted read privilege was not revoked.
But if instead of REVOKE ALL PRIVILEGES` I do `REVOKE SELECT`, then 
everything is ok:

postgres=#  begin;
BEGIN
postgres=*# set local role somebody;
SET
postgres=*> revoke select on table t from reader granted by somebody;
REVOKE
postgres=*> commit;
COMMIT
postgres=# SELECT * FROM information_schema.role_table_grants AS rtg 
WHERE grantee='reader';
  grantor | grantee | table_catalog | table_schema | table_name | 
privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

The critical thing is this grant statement:

grant <ADMIN_ROLE> to somebody;

If it is skipped, then the problem is not reproduced.

Konstantin Knizhnik <knizhnik@garret.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

On 20/01/2026 9:26 PM, Tom Lane wrote:
> Konstantin Knizhnik <knizhnik@garret.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.

> On 20/01/2026 9:26 PM, Tom Lane wrote:
>> Konstantin Knizhnik <knizhnik@garret.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

> 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?

I mean something like this (see attached patch).

Konstantin Knizhnik <knizhnik@garret.ru> writes:
>> 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?

> I mean something like this (see attached patch).

I don't think "let's make select_best_grantor even more magic"
is the right approach.  IMO, if there is a GRANTED BY clause,
we should use exactly that grantor and not apply select_best_grantor
at all.  This is, for example, certainly the behavior needed for
pg_dump.

If I'm reading the SQL spec correctly, it only allows "GRANTED BY
CURRENT_ROLE" and "GRANTED BY CURRENT_USER", so we're already
extending the spec by accepting "GRANTED BY somebody".  But it's
really hard to find any justification for select_best_grantor in
the spec.  I think we ought to redefine that as a legacy behavior
we use in the absence of any explicit GRANTED BY clause.

            regards, tom lane

On Tue, Jan 20, 2026 at 04:32:31PM -0500, Tom Lane wrote:
> I don't think "let's make select_best_grantor even more magic"
> is the right approach.  IMO, if there is a GRANTED BY clause,
> we should use exactly that grantor and not apply select_best_grantor
> at all.  This is, for example, certainly the behavior needed for
> pg_dump.

I started on something like that here:

    https://postgr.es/m/aRYLkTpazxKhnS_w%40nathan

-- 
nathan

Nathan Bossart <nathandbossart@gmail.com> writes:
> On Tue, Jan 20, 2026 at 04:32:31PM -0500, Tom Lane wrote:
>> I don't think "let's make select_best_grantor even more magic"
>> is the right approach.  IMO, if there is a GRANTED BY clause,
>> we should use exactly that grantor and not apply select_best_grantor
>> at all.  This is, for example, certainly the behavior needed for
>> pg_dump.

> I started on something like that here:
>     https://postgr.es/m/aRYLkTpazxKhnS_w%40nathan

Ah, I wonder if that discussion was lurking in my hindbrain.
I just posted a different take on how to do it in that thread,
but the behavioral change should be the same.

            regards, tom lane

On 21/01/2026 1:07 AM, Tom Lane wrote:
> Nathan Bossart <nathandbossart@gmail.com> writes:
>> On Tue, Jan 20, 2026 at 04:32:31PM -0500, Tom Lane wrote:
>>> I don't think "let's make select_best_grantor even more magic"
>>> is the right approach.  IMO, if there is a GRANTED BY clause,
>>> we should use exactly that grantor and not apply select_best_grantor
>>> at all.  This is, for example, certainly the behavior needed for
>>> pg_dump.
>> I started on something like that here:
>>     https://postgr.es/m/aRYLkTpazxKhnS_w%40nathan
> Ah, I wonder if that discussion was lurking in my hindbrain.
> I just posted a different take on how to do it in that thread,
> but the behavioral change should be the same.
>
>             regards, tom lane


Thank you.
Fixing explicit grantor case is definitely the most critical thing.
And I completely agree with your patch.
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?

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

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?

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.