public inbox for [email protected]  

help / color / mirror / Atom feed

From: Konstantin Knizhnik <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Mystery with REVOKE PRIVILEGE
Date: Tue, 20 Jan 2026 21:04:39 +0200
Message-ID: <[email protected]> (raw)

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.

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Mystery with REVOKE PRIVILEGE
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox