| From: | Konstantin Knizhnik <knizhnik(at)garret(dot)ru> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Mystery with REVOKE PRIVILEGE |
| Date: | 2026-01-20 19:04:39 |
| Message-ID: | 85cd06c6-7b2e-483e-b05d-d5ff87b0168d@garret.ru |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Eisentraut | 2026-01-20 19:07:15 | Re: Make copyObject work in C++ |
| Previous Message | Andres Freund | 2026-01-20 19:03:45 | Re: Add missing JIT inline pass for llvm>=17 |