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).
Attachments:
select_best_grantor.patchtext/plain; charset=UTF-8; name=select_best_grantor.patchDownload+12-1
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:
/messages/by-id/aRYLkTpazxKhnS_w@nathan
--
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:
/messages/by-id/aRYLkTpazxKhnS_w@nathan
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:
/messages/by-id/aRYLkTpazxKhnS_w@nathanAh, 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 tWhat 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.