pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedREVOKE's CASCADE protection doesn't work with INHERITed table owners
7+ messages / 4 participants
[nested] [flat]
* REVOKE's CASCADE protection doesn't work with INHERITed table owners
@ 2026-06-24 21:57 Jacob Champion <jacob.champion@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jacob Champion @ 2026-06-24 21:57 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi all,
I'm pretty sure the following is unintended behavior. It looks
potentially related to [1] as well.
TL;DR: The protection in recursive_revoke() against broken GRANT
OPTION chains doesn't seem to work properly when the grantee also
holds the privileges of the grantor.
= Setup =
With the following (contrived, minimized from the actual) setup:
-- Set up a role hierarchy (as superuser "jacob")
CREATE ROLE admins;
CREATE ROLE bob;
GRANT admins TO bob;
-- Create two tables with different ownership
CREATE TABLE my_table(i int);
CREATE TABLE admin_table(i int);
ALTER TABLE admin_table OWNER TO admins;
-- Create a grant option chain on both tables
GRANT ALL ON TABLE my_table TO bob WITH GRANT OPTION;
GRANT ALL ON TABLE admin_table TO bob WITH GRANT OPTION;
SET ROLE bob;
GRANT ALL ON TABLE my_table TO bob;
GRANT ALL ON TABLE admin_table TO bob;
RESET ROLE;
Now we have the following ACLs:
=# SELECT relname, relowner::regrole, relacl FROM pg_class
WHERE relname LIKE '%_table';
-[ RECORD 1
]-------------------------------------------------------------------
relname | my_table
relowner | jacob
relacl | {jacob=arwdDxtm/jacob,bob=a*r*w*d*D*x*t*m*/jacob,bob=arwdDxtm/bob}
-[ RECORD 2
]-------------------------------------------------------------------
relname | admin_table
relowner | admins
relacl | {admins=arwdDxtm/admins,bob=a*r*w*d*D*x*t*m*/admins,bob=arwdDxtm/bob}
= Bug =
With that grant option chain, we try to prevent REVOKE [RESTRICT]
invocations that would cause problems:
=# REVOKE ALL ON TABLE my_table FROM bob;
ERROR: dependent privileges exist
HINT: Use CASCADE to revoke them too.
But this protection doesn't work for the admin_table...
=# REVOKE ALL ON TABLE admin_table FROM bob;
REVOKE
...resulting in an orphaned ACL.
-[ RECORD 2
]----------------------------------------------------------------
relname | admin_table
relowner | admins
relacl | {admins=arwdDxtm/admins,bob=arwdDxtm/bob}
Dump/restores of this situation result in complaints, since user "bob"
isn't able to recreate the grant.
I think the issue is in recursive_revoke()'s usage of aclmask(), which
in turn uses has_privs_of_role(). It doesn't seem like that's what was
wanted in this particular case... thoughts?
Thanks,
--Jacob
[1] https://postgr.es/m/CAM6Zo8wD7RtQNhbQHODc9DobiW+GpT=tnqOSMz4+mnzA9m0zMg@mail.gmail.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE's CASCADE protection doesn't work with INHERITed table owners
@ 2026-06-26 00:12 Jacob Champion <jacob.champion@enterprisedb.com>
parent: Jacob Champion <jacob.champion@enterprisedb.com>
0 siblings, 2 replies; 7+ messages in thread
From: Jacob Champion @ 2026-06-26 00:12 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
[moving to -hackers]
On Wed, Jun 24, 2026 at 2:57 PM Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
> TL;DR: The protection in recursive_revoke() against broken GRANT
> OPTION chains doesn't seem to work properly when the grantee also
> holds the privileges of the grantor.
More accurately: "when an intermediate grantor in the chain only
indirectly holds the ability to grant."
> I think the issue is in recursive_revoke()'s usage of aclmask(), which
> in turn uses has_privs_of_role(). It doesn't seem like that's what was
> wanted in this particular case... thoughts?
I propose changing that to aclmask_direct(), as in the attached, and
backpatching all the way down.
To try to prove to myself that this works, I added tests to pin each
of the three cases that are treated differently by aclmask_direct():
1. the grantor has indirect ownership privileges
2. the grantor has indirect grant options via INHERIT
3. the grantor has indirect grant options via PUBLIC (which is already
disallowed in practice)
I also tried to expand the existing comment, both to point out the
pitfall and to explain why the short-circuit works. But I've rewritten
it at least a dozen times, so if anyone can tell me whether I've made
sense and/or used the terminology appropriately, I'd appreciate it.
> I'm pretty sure the following is unintended behavior. It looks
> potentially related to [1] as well.
(To fix [1] I suspect we need to make a similar tweak to
check_circularity(), but I haven't looked into that yet.)
Thanks!
--Jacob
[1] https://postgr.es/m/CAM6Zo8wD7RtQNhbQHODc9DobiW+GpT=tnqOSMz4+mnzA9m0zMg@mail.gmail.com
Attachments:
[application/octet-stream] v1-0001-Prevent-broken-grant-chains-when-indirect-grant-o.patch (10.0K, ../../CAOYmi+n_hw=SC5V1i3BmqfZPfPBRaUSJc+BeOXEKDwRue+WYrg@mail.gmail.com/2-v1-0001-Prevent-broken-grant-chains-when-indirect-grant-o.patch)
download | inline diff:
From 1a0c40e4257d6b1fd19d850761dbbcc21800aef7 Mon Sep 17 00:00:00 2001
From: Jacob Champion <jacob.champion@enterprisedb.com>
Date: Thu, 25 Jun 2026 11:18:20 -0700
Subject: [PATCH v1] Prevent broken grant chains when indirect grant options
are held
recursive_revoke() is meant to protect against cases where a grantor
loses their grant option after issuing dependent grants to other roles.
In that case, the user must specify CASCADE to prune the dependent ACLs.
However, if a grantor happens to hold grant options (or table ownership)
indirectly, whether via INHERIT or SUPERUSER, recursive_revoke()
short-circuits and allows the REVOKE to break the grant chain. Symptoms
include general user confusion as well as problems during dump/restore,
which cannot recreate the original state.
To fix, switch from aclmask() to aclmask_direct().
Discussion: https://postgr.es/m/CAOYmi%2B%3DKTLd%2BXsEP%3DTDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ%40mail.gmail.com
Backpatch-through: 14
---
src/backend/utils/adt/acl.c | 19 +++--
src/test/regress/expected/privileges.out | 88 ++++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 44 ++++++++++++
3 files changed, 147 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 01caa12eca7..21a874392d9 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -102,6 +102,8 @@ static void check_circularity(const Acl *old_acl, const AclItem *mod_aip,
Oid ownerId);
static Acl *recursive_revoke(Acl *acl, Oid grantee, AclMode revoke_privs,
Oid ownerId, DropBehavior behavior);
+static AclMode aclmask_direct(const Acl *acl, Oid roleid, Oid ownerId,
+ AclMode mask, AclMaskHow how);
static AclMode convert_any_priv_string(text *priv_type_text,
const priv_map *privileges);
@@ -1344,10 +1346,19 @@ recursive_revoke(Acl *acl,
if (grantee == ownerId)
return acl;
- /* The grantee might still have some grant options via another grantor */
- still_has = aclmask(acl, grantee, ownerId,
- ACL_GRANT_OPTION_FOR(revoke_privs),
- ACLMASK_ALL);
+ /*
+ * The grantee might still have some grant options via another grantor,
+ * keeping the chain intact for those privileges. And if _all_ of the
+ * remaining privileges can still be granted by any role on the chain,
+ * we're done.
+ *
+ * Indirectly held grant options cannot keep the chain alive, so consult
+ * aclmask_direct() rather than aclmask(). (Otherwise, for example, a
+ * chain could be broken wherever the grantee was a superuser).
+ */
+ still_has = aclmask_direct(acl, grantee, ownerId,
+ ACL_GRANT_OPTION_FOR(revoke_privs),
+ ACLMASK_ALL);
revoke_privs &= ~ACL_OPTION_TO_PRIVS(still_has);
if (revoke_privs == ACL_NO_RIGHTS)
return acl;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index f6cc1a1029c..6840ff950c6 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1862,6 +1862,8 @@ CREATE TABLE atest4 (a int);
GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
GRANT UPDATE ON atest4 TO regress_priv_user2;
GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
+GRANT SELECT ON atest4 TO PUBLIC WITH GRANT OPTION; -- fail
+ERROR: grant options can only be granted to roles
SET SESSION AUTHORIZATION regress_priv_user2;
GRANT SELECT ON atest4 TO regress_priv_user3;
GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail
@@ -1896,6 +1898,91 @@ SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OP
t
(1 row)
+-- Test GRANT OPTION chains where the intermediate grantor has the privileges of
+-- the owner (regress_priv_group1 -> regress_priv_user4 -> regress_priv_user2)
+-- or inherits the option (regress_priv_group2 -> regress_priv_user1 ->
+-- regress_priv_user3).
+-- pin the group membership that we rely on below
+SELECT pg_has_role('regress_priv_user4', 'regress_priv_group1', 'MEMBER'),
+ pg_has_role('regress_priv_user1', 'regress_priv_group2', 'MEMBER');
+ pg_has_role | pg_has_role
+-------------+-------------
+ t | t
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_group1;
+CREATE TABLE atest4_groupowned (a int);
+GRANT SELECT ON atest4_groupowned TO regress_priv_user4 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_group2 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user1 WITH GRANT OPTION;
+SET SESSION AUTHORIZATION regress_priv_user4;
+GRANT SELECT ON atest4_groupowned TO regress_priv_user2;
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_group1;
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4; -- fail
+ERROR: dependent privileges exist
+HINT: Use CASCADE to revoke them too.
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- still true (inherited)
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1; -- fail
+ERROR: dependent privileges exist
+HINT: Use CASCADE to revoke them too.
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- still true (inherited)
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_group2 RESTRICT; -- ok
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
-- security-restricted operations
\c -
CREATE ROLE regress_sro_user;
@@ -3296,6 +3383,7 @@ DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;
DROP TABLE atest4;
+DROP TABLE atest4_groupowned;
DROP TABLE atest5;
DROP TABLE atest6;
DROP TABLE atestc;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 6cd9bb840ff..c9f80ba898b 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1205,6 +1205,7 @@ CREATE TABLE atest4 (a int);
GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
GRANT UPDATE ON atest4 TO regress_priv_user2;
GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
+GRANT SELECT ON atest4 TO PUBLIC WITH GRANT OPTION; -- fail
SET SESSION AUTHORIZATION regress_priv_user2;
@@ -1222,6 +1223,48 @@ SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false
SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+-- Test GRANT OPTION chains where the intermediate grantor has the privileges of
+-- the owner (regress_priv_group1 -> regress_priv_user4 -> regress_priv_user2)
+-- or inherits the option (regress_priv_group2 -> regress_priv_user1 ->
+-- regress_priv_user3).
+
+-- pin the group membership that we rely on below
+SELECT pg_has_role('regress_priv_user4', 'regress_priv_group1', 'MEMBER'),
+ pg_has_role('regress_priv_user1', 'regress_priv_group2', 'MEMBER');
+
+SET SESSION AUTHORIZATION regress_priv_group1;
+
+CREATE TABLE atest4_groupowned (a int);
+
+GRANT SELECT ON atest4_groupowned TO regress_priv_user4 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_group2 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user1 WITH GRANT OPTION;
+
+SET SESSION AUTHORIZATION regress_priv_user4;
+
+GRANT SELECT ON atest4_groupowned TO regress_priv_user2;
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user3;
+
+SET SESSION AUTHORIZATION regress_priv_group1;
+
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4; -- fail
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- true
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- true
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- false
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- still true (inherited)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1; -- fail
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- true
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- true
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- false
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- still true (inherited)
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_group2 RESTRICT; -- ok
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- false
-- security-restricted operations
\c -
@@ -1955,6 +1998,7 @@ DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;
DROP TABLE atest4;
+DROP TABLE atest4_groupowned;
DROP TABLE atest5;
DROP TABLE atest6;
DROP TABLE atestc;
--
2.34.1
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE's CASCADE protection doesn't work with INHERITed table owners
@ 2026-07-06 11:03 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: Jacob Champion <jacob.champion@enterprisedb.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ayush Tiwari @ 2026-07-06 11:03 UTC (permalink / raw)
To: Jacob Champion <jacob.champion@enterprisedb.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi,
On Fri, 26 Jun 2026 at 05:43, Jacob Champion <
jacob.champion@enterprisedb.com> wrote:
> [moving to -hackers]
>
> On Wed, Jun 24, 2026 at 2:57 PM Jacob Champion
> <jacob.champion@enterprisedb.com> wrote:
> > TL;DR: The protection in recursive_revoke() against broken GRANT
> > OPTION chains doesn't seem to work properly when the grantee also
> > holds the privileges of the grantor.
>
> More accurately: "when an intermediate grantor in the chain only
> indirectly holds the ability to grant."
>
> > I think the issue is in recursive_revoke()'s usage of aclmask(), which
> > in turn uses has_privs_of_role(). It doesn't seem like that's what was
> > wanted in this particular case... thoughts?
>
> I propose changing that to aclmask_direct(), as in the attached, and
> backpatching all the way down.
>
> To try to prove to myself that this works, I added tests to pin each
> of the three cases that are treated differently by aclmask_direct():
> 1. the grantor has indirect ownership privileges
> 2. the grantor has indirect grant options via INHERIT
> 3. the grantor has indirect grant options via PUBLIC (which is already
> disallowed in practice)
>
Thanks, this looks right to me. I traced recursive_revoke() and the
aclmask() -> aclmask_direct() switch makes sense to me, since a
grant is only ever attributed to a role that holds the option directly
(or the owner), it seems right that recursive_revoke() should judge
"still has it" the same way, rather than counting inherited/superuser
access? The three new test cases line up with that too.
I also tried to expand the existing comment, both to point out the
> pitfall and to explain why the short-circuit works. But I've rewritten
> it at least a dozen times, so if anyone can tell me whether I've made
> sense and/or used the terminology appropriately, I'd appreciate it.
One tiny comment question: the phrase "granted by any role on the chain"
in the new comment reads a little oddly to me, would something like
"still holds the option directly via another grantor" be closer to what
the code checks? Could just be me misreading it.
Apart from the above, patch LGTM.
> I'm pretty sure the following is unintended behavior. It looks
> > potentially related to [1] as well.
>
> (To fix [1] I suspect we need to make a similar tweak to
> check_circularity(), but I haven't looked into that yet.)
>
> [1]
> https://postgr.es/m/CAM6Zo8wD7RtQNhbQHODc9DobiW+GpT=tnqOSMz4+mnzA9m0zMg@mail.gmail.com
On check_circularity() for [1]: I tried the same aclmask_direct() swap,
but since it runs on every GRANT ... WITH GRANT OPTION, which
pg_dump/restore replays, erroring there could make restore/pg_upgrade of
an existing cluster (one already holding the [1] self-grant) fail. Feels
like it may need a companion dump/restore change first.
Regards,
Ayush
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE's CASCADE protection doesn't work with INHERITed table owners
@ 2026-07-08 22:54 Jacob Champion <jacob.champion@enterprisedb.com>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jacob Champion @ 2026-07-08 22:54 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Mon, Jul 6, 2026 at 4:03 AM Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
> Thanks, this looks right to me.
Thanks for the review!
> One tiny comment question: the phrase "granted by any role on the chain"
> in the new comment reads a little oddly to me, would something like
> "still holds the option directly via another grantor" be closer to what
> the code checks?
The first sentence is still "The grantee might still have some grant
options via another grantor," and I don't think adding "direct" would
have helped me understand this any better the first time I read it.
I'm definitely up for more bikeshedding, though, because I don't
really like what I have...
> On check_circularity() for [1]: I tried the same aclmask_direct() swap,
> but since it runs on every GRANT ... WITH GRANT OPTION, which
> pg_dump/restore replays, erroring there could make restore/pg_upgrade of
> an existing cluster (one already holding the [1] self-grant) fail.
Well, I think the complaint in [1] is that dump/restore *already*
fails, no? Can you provide an example of a correct (or benignly
incorrect) dump that would start failing?
As an aside, I'm not sure if check_circularity() is correctly
preventing cycles independently of this issue, so that part may end up
spiraling a bit.
Thanks,
--Jacob
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE's CASCADE protection doesn't work with INHERITed table owners
@ 2026-07-09 14:48 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: Jacob Champion <jacob.champion@enterprisedb.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ayush Tiwari @ 2026-07-09 14:48 UTC (permalink / raw)
To: Jacob Champion <jacob.champion@enterprisedb.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi,
On Thu, 9 Jul 2026 at 04:24, Jacob Champion <jacob.champion@enterprisedb.com>
wrote:
> On Mon, Jul 6, 2026 at 4:03 AM Ayush Tiwari <ayushtiwari.slg01@gmail.com>
> wrote:
>
> The first sentence is still "The grantee might still have some grant
> options via another grantor," and I don't think adding "direct" would
> have helped me understand this any better the first time I read it.
> I'm definitely up for more bikeshedding, though, because I don't
> really like what I have...
>
Fair. The part that tripped me up was actually the second sentence,
"granted by any role on the chain", since the code isn't checking whether
some role could grant them, but whether the grantee itself still holds
them directly via another grantor. Maybe something like "if the grantee
still holds a grant option directly through another grantor, that
privilege's chain is intact"? No strong opinion though.
> > On check_circularity() for [1]: I tried the same aclmask_direct() swap,
> > but since it runs on every GRANT ... WITH GRANT OPTION, which
> > pg_dump/restore replays, erroring there could make restore/pg_upgrade of
> > an existing cluster (one already holding the [1] self-grant) fail.
>
> Well, I think the complaint in [1] is that dump/restore *already*
> fails, no? Can you provide an example of a correct (or benignly
> incorrect) dump that would start failing?
>
The already-failing case in [1] is the one where the membership has
been revoked (REVOKE member FROM owner), which leaves a true orphan.
But the self-grant gets created as soon as an inheriting member does
the redundant WITH GRANT OPTION self-grant, and if the membership is
still in place, that cluster dumps and restores fine today.
CREATE ROLE owner_role;
CREATE ROLE member_role LOGIN;
GRANT owner_role TO member_role; -- membership kept
CREATE TABLE t (i int);
ALTER TABLE t OWNER TO owner_role;
GRANT SELECT ON t TO member_role WITH GRANT OPTION;
SET ROLE member_role;
GRANT SELECT ON t TO member_role WITH GRANT OPTION; -- self-grant
relacl = {owner_role=arwdDxtm/owner_role,
member_role=r*/owner_role,
member_role=r*/member_role}
pg_dump emits "SET SESSION AUTHORIZATION member_role; GRANT SELECT ON
t TO member_role WITH GRANT OPTION;". Restoring that into a fresh
cluster works today, but with the check_circularity() change it fails
with "grant options cannot be granted back to your own grantor".
So it's not that [1] is fine today; it's that the fix also breaks a
broader set of clusters that currently round-trip (membership still
present), including via pg_upgrade. That's why I think the
check_circularity() side needs a companion dump/restore change rather
than going in on its own.
> As an aside, I'm not sure if check_circularity() is correctly
> preventing cycles independently of this issue, so that part may end up
> spiraling a bit.
>
Agreed, I did not check that either.
Regards,
Ayush
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE's CASCADE protection doesn't work with INHERITed table owners
@ 2026-08-10 11:41 Kirill Reshke <reshkekirill@gmail.com>
parent: Jacob Champion <jacob.champion@enterprisedb.com>
1 sibling, 1 reply; 7+ messages in thread
From: Kirill Reshke @ 2026-08-10 11:41 UTC (permalink / raw)
To: Jacob Champion <jacob.champion@enterprisedb.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, 26 Jun 2026 at 05:13, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> [moving to -hackers]
>
> On Wed, Jun 24, 2026 at 2:57 PM Jacob Champion
> <jacob.champion@enterprisedb.com> wrote:
> > TL;DR: The protection in recursive_revoke() against broken GRANT
> > OPTION chains doesn't seem to work properly when the grantee also
> > holds the privileges of the grantor.
>
> More accurately: "when an intermediate grantor in the chain only
> indirectly holds the ability to grant."
>
> > I think the issue is in recursive_revoke()'s usage of aclmask(), which
> > in turn uses has_privs_of_role(). It doesn't seem like that's what was
> > wanted in this particular case... thoughts?
>
> I propose changing that to aclmask_direct(), as in the attached, and
> backpatching all the way down.
>
> To try to prove to myself that this works, I added tests to pin each
> of the three cases that are treated differently by aclmask_direct():
> 1. the grantor has indirect ownership privileges
> 2. the grantor has indirect grant options via INHERIT
> 3. the grantor has indirect grant options via PUBLIC (which is already
> disallowed in practice)
>
> I also tried to expand the existing comment, both to point out the
> pitfall and to explain why the short-circuit works. But I've rewritten
> it at least a dozen times, so if anyone can tell me whether I've made
> sense and/or used the terminology appropriately, I'd appreciate it.
>
> > I'm pretty sure the following is unintended behavior. It looks
> > potentially related to [1] as well.
>
> (To fix [1] I suspect we need to make a similar tweak to
> check_circularity(), but I haven't looked into that yet.)
>
> Thanks!
> --Jacob
>
> [1] https://postgr.es/m/CAM6Zo8wD7RtQNhbQHODc9DobiW+GpT=tnqOSMz4+mnzA9m0zMg@mail.gmail.com
Hi!
I think to fix [1] you pointing to we can use my patch [0]. I checked
that your patch doesn't fix my (and [1]) case and vice-versa: my patch
doesn't prevent problems in the revoke case.
With your v1 I run
CREATE ROLE r1 LOGIN;
CREATE ROLE r2 LOGIN;
CREATE ROLE r3;
GRANT r3 TO r2;
GRANT CREATE ON SCHEMA public TO r1;
SET ROLE r1;
CREATE VIEW v AS SELECT;
GRANT SELECT ON v TO r2 WITH GRANT OPTION; -- r2=r*/r1
GRANT SELECT ON v TO r3 WITH GRANT OPTION; -- r3=r*/r
SET ROLE r2;
GRANT SELECT ON v TO r2 WITH GRANT OPTION; -- r2=r*/r2 -- self
grant, already bad
and then successfully do REVOKE r3 FROM r2 leaving very bad grants.
Also I checked that my patch fixes [1] while patch is this thread not.
So I think we need both.
Also, another option is to adjust your patch so that it would reject
REVOKE r3 FROM r2 in my case, but I think we should fail on GRANT
SELECT ON v TO r2 WITH GRANT OPTION;
About your patch: LGTM
[0] https://www.postgresql.org/message-id/CALdSSPi5JaToi_p0%3DkPUynSTqUdqq8X-axRsrKL1TX0yt1KLiA%40mail.g...
[1] https://postgr.es/m/CAM6Zo8wD7RtQNhbQHODc9DobiW+GpT=tnqOSMz4+mnzA9m0zMg@mail.gmail.com
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: REVOKE's CASCADE protection doesn't work with INHERITed table owners
@ 2026-09-30 04:32 Paul Kim <mok03127@gmail.com>
parent: Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Paul Kim @ 2026-09-30 04:32 UTC (permalink / raw)
To: jacob.champion@enterprisedb.com; +Cc: reshkekirill@gmail.com; ayushtiwari.slg01@gmail.com; pgsql-hackers@lists.postgresql.org
Hi,
I reproduced the superuser case on PostgreSQL 18.4 and master
(5e592904025). Starting as a superuser:
CREATE ROLE o;
CREATE ROLE g;
CREATE ROLE r;
GRANT CREATE ON SCHEMA public TO o;
SET SESSION AUTHORIZATION o;
CREATE TABLE t (a int);
GRANT SELECT ON t TO g WITH GRANT OPTION;
SET SESSION AUTHORIZATION g;
GRANT SELECT ON t TO r;
RESET SESSION AUTHORIZATION;
ALTER ROLE g SUPERUSER;
REVOKE GRANT OPTION FOR SELECT ON t FROM g; -- no error
ALTER ROLE g NOSUPERUSER;
The REVOKE succeeds and leaves r=r/g in relacl, although g no longer
has the grant option. pg_dump emits the dependent grant under
SET SESSION AUTHORIZATION g. Restoring into a fresh database reports
"WARNING: no privileges were granted" and leaves r without SELECT.
With your patch, REVOKE fails with "dependent privileges exist";
with CASCADE, it removes r's grant.
Without GRANTED BY, a superuser's GRANT is recorded under the object
owner, which is why the sequence above has g grant before it is
promoted. On master, GRANTED BY g can also record the grant under g
while it is already a superuser.
I added a test for this case next to the atest4_groupowned tests.
Reverting just the acl.c change makes it fail: REVOKE succeeds, and
r keeps SELECT after CASCADE. The attached 0001 is your v1 rebased
onto master, with no changes to the code or tests; 0002 adds the
superuser test. Please feel free to fold 0002 into your patch.
I labeled them v2 only so the two apply as a set; renumber as you
like.
The pair applies with git am on REL_16_STABLE through REL_18_STABLE.
On 14 and 15, the acl.c prototype hunk needs git am -3. The core
regression suite passes on all five branches, and on master.
This sequence does not self-grant, so it does not exercise the
check_circularity issue discussed earlier in the thread.
Regards,
Paul Kim
Attachments:
[text/x-patch] v2-0001-Prevent-broken-grant-chains-when-indirect-grant-o.patch (10.0K, ../../179074273441.6180.2515281464552050662@mail.gmail.com/2-v2-0001-Prevent-broken-grant-chains-when-indirect-grant-o.patch)
download | inline diff:
From 0a811de40f3b4bb7fe5d4e43ea2f583a5275c0f6 Mon Sep 17 00:00:00 2001
From: Jacob Champion <jacob.champion@enterprisedb.com>
Date: Thu, 25 Jun 2026 11:18:20 -0700
Subject: [PATCH v2 1/2] Prevent broken grant chains when indirect grant
options are held
recursive_revoke() is meant to protect against cases where a grantor
loses their grant option after issuing dependent grants to other roles.
In that case, the user must specify CASCADE to prune the dependent ACLs.
However, if a grantor happens to hold grant options (or table ownership)
indirectly, whether via INHERIT or SUPERUSER, recursive_revoke()
short-circuits and allows the REVOKE to break the grant chain. Symptoms
include general user confusion as well as problems during dump/restore,
which cannot recreate the original state.
To fix, switch from aclmask() to aclmask_direct().
Discussion: https://postgr.es/m/CAOYmi%2B%3DKTLd%2BXsEP%3DTDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ%40mail.gmail.com
Backpatch-through: 14
---
src/backend/utils/adt/acl.c | 19 +++--
src/test/regress/expected/privileges.out | 88 ++++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 44 ++++++++++++
3 files changed, 147 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 913d715a8c8..8bcda719e4c 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -102,6 +102,8 @@ static void check_circularity(const Acl *old_acl, const AclItem *mod_aip,
Oid ownerId);
static Acl *recursive_revoke(Acl *acl, Oid grantee, AclMode revoke_privs,
Oid ownerId, DropBehavior behavior);
+static AclMode aclmask_direct(const Acl *acl, Oid roleid, Oid ownerId,
+ AclMode mask, AclMaskHow how);
static AclMode convert_any_priv_string(text *priv_type_text,
const priv_map *privileges);
@@ -1349,10 +1351,19 @@ recursive_revoke(Acl *acl,
if (grantee == ownerId)
return acl;
- /* The grantee might still have some grant options via another grantor */
- still_has = aclmask(acl, grantee, ownerId,
- ACL_GRANT_OPTION_FOR(revoke_privs),
- ACLMASK_ALL);
+ /*
+ * The grantee might still have some grant options via another grantor,
+ * keeping the chain intact for those privileges. And if _all_ of the
+ * remaining privileges can still be granted by any role on the chain,
+ * we're done.
+ *
+ * Indirectly held grant options cannot keep the chain alive, so consult
+ * aclmask_direct() rather than aclmask(). (Otherwise, for example, a
+ * chain could be broken wherever the grantee was a superuser).
+ */
+ still_has = aclmask_direct(acl, grantee, ownerId,
+ ACL_GRANT_OPTION_FOR(revoke_privs),
+ ACLMASK_ALL);
revoke_privs &= ~ACL_OPTION_TO_PRIVS(still_has);
if (revoke_privs == ACL_NO_RIGHTS)
return acl;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 9b7153c9cd5..4de9d749574 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1885,6 +1885,8 @@ CREATE TABLE atest4 (a int);
GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
GRANT UPDATE ON atest4 TO regress_priv_user2;
GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
+GRANT SELECT ON atest4 TO PUBLIC WITH GRANT OPTION; -- fail
+ERROR: grant options can only be granted to roles
SET SESSION AUTHORIZATION regress_priv_user2;
GRANT SELECT ON atest4 TO regress_priv_user3;
GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail
@@ -1919,6 +1921,91 @@ SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OP
t
(1 row)
+-- Test GRANT OPTION chains where the intermediate grantor has the privileges of
+-- the owner (regress_priv_group1 -> regress_priv_user4 -> regress_priv_user2)
+-- or inherits the option (regress_priv_group2 -> regress_priv_user1 ->
+-- regress_priv_user3).
+-- pin the group membership that we rely on below
+SELECT pg_has_role('regress_priv_user4', 'regress_priv_group1', 'MEMBER'),
+ pg_has_role('regress_priv_user1', 'regress_priv_group2', 'MEMBER');
+ pg_has_role | pg_has_role
+-------------+-------------
+ t | t
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_group1;
+CREATE TABLE atest4_groupowned (a int);
+GRANT SELECT ON atest4_groupowned TO regress_priv_user4 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_group2 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user1 WITH GRANT OPTION;
+SET SESSION AUTHORIZATION regress_priv_user4;
+GRANT SELECT ON atest4_groupowned TO regress_priv_user2;
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_group1;
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4; -- fail
+ERROR: dependent privileges exist
+HINT: Use CASCADE to revoke them too.
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- still true (inherited)
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1; -- fail
+ERROR: dependent privileges exist
+HINT: Use CASCADE to revoke them too.
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- still true (inherited)
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_group2 RESTRICT; -- ok
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
-- security-restricted operations
\c -
CREATE ROLE regress_sro_user;
@@ -3228,6 +3315,7 @@ DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;
DROP TABLE atest4;
+DROP TABLE atest4_groupowned;
DROP TABLE atest5;
DROP TABLE atest6;
DROP TABLE atestc;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 0113dfa5dab..c34f5196fe5 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1226,6 +1226,7 @@ CREATE TABLE atest4 (a int);
GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
GRANT UPDATE ON atest4 TO regress_priv_user2;
GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
+GRANT SELECT ON atest4 TO PUBLIC WITH GRANT OPTION; -- fail
SET SESSION AUTHORIZATION regress_priv_user2;
@@ -1243,6 +1244,48 @@ SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false
SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+-- Test GRANT OPTION chains where the intermediate grantor has the privileges of
+-- the owner (regress_priv_group1 -> regress_priv_user4 -> regress_priv_user2)
+-- or inherits the option (regress_priv_group2 -> regress_priv_user1 ->
+-- regress_priv_user3).
+
+-- pin the group membership that we rely on below
+SELECT pg_has_role('regress_priv_user4', 'regress_priv_group1', 'MEMBER'),
+ pg_has_role('regress_priv_user1', 'regress_priv_group2', 'MEMBER');
+
+SET SESSION AUTHORIZATION regress_priv_group1;
+
+CREATE TABLE atest4_groupowned (a int);
+
+GRANT SELECT ON atest4_groupowned TO regress_priv_user4 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_group2 WITH GRANT OPTION;
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user1 WITH GRANT OPTION;
+
+SET SESSION AUTHORIZATION regress_priv_user4;
+
+GRANT SELECT ON atest4_groupowned TO regress_priv_user2;
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+
+GRANT UPDATE ON atest4_groupowned TO regress_priv_user3;
+
+SET SESSION AUTHORIZATION regress_priv_group1;
+
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4; -- fail
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- true
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- true
+REVOKE SELECT ON atest4_groupowned FROM regress_priv_user4 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user2', 'atest4_groupowned', 'SELECT'); -- false
+SELECT has_table_privilege('regress_priv_user4', 'atest4_groupowned', 'SELECT'); -- still true (inherited)
+
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1; -- fail
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- true
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- true
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_user1 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user3', 'atest4_groupowned', 'UPDATE'); -- false
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- still true (inherited)
+REVOKE UPDATE ON atest4_groupowned FROM regress_priv_group2 RESTRICT; -- ok
+SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- false
-- security-restricted operations
\c -
@@ -1918,6 +1961,7 @@ DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;
DROP TABLE atest4;
+DROP TABLE atest4_groupowned;
DROP TABLE atest5;
DROP TABLE atest6;
DROP TABLE atestc;
--
2.50.1 (Apple Git-155)
[text/x-patch] v2-0002-Test-grant-option-chains-through-a-grantor-that-b.patch (4.0K, ../../179074273441.6180.2515281464552050662@mail.gmail.com/3-v2-0002-Test-grant-option-chains-through-a-grantor-that-b.patch)
download | inline diff:
From a9993f9a4ff90264aa10cb747d83ca58a4538fed Mon Sep 17 00:00:00 2001
From: Paul Kim <mok03127@gmail.com>
Date: Mon, 28 Sep 2026 10:17:34 +0900
Subject: [PATCH v2 2/2] Test grant option chains through a grantor that became
superuser
The previous commit tests inherited ownership and grant options.
Add coverage for an intermediate grantor that becomes a superuser
after issuing a grant.
Without the fix, revoking that role's grant option succeeds and leaves
its dependent grant in the ACL. After the role loses SUPERUSER,
dump/restore loses the dependent grant with a warning. Check that
RESTRICT (the default) rejects the revocation and CASCADE removes
the dependent grant.
Discussion: https://postgr.es/m/CAOYmi%2B%3DKTLd%2BXsEP%3DTDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ%40mail.gmail.com
---
src/test/regress/expected/privileges.out | 31 ++++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 29 ++++++++++++++++++++++
2 files changed, 60 insertions(+)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 4de9d749574..b6276b30d15 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2006,6 +2006,37 @@ SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE');
f
(1 row)
+-- Same, but the intermediate grantor has become a superuser since issuing its
+-- grant (regress_priv_user1 -> regress_priv_su -> regress_priv_user2).
+RESET SESSION AUTHORIZATION;
+CREATE ROLE regress_priv_su;
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE atest4_su (a int);
+GRANT SELECT ON atest4_su TO regress_priv_su WITH GRANT OPTION;
+SET SESSION AUTHORIZATION regress_priv_su;
+GRANT SELECT ON atest4_su TO regress_priv_user2;
+RESET SESSION AUTHORIZATION;
+ALTER ROLE regress_priv_su SUPERUSER;
+SET SESSION AUTHORIZATION regress_priv_user1;
+REVOKE GRANT OPTION FOR SELECT ON atest4_su FROM regress_priv_su; -- fail
+ERROR: dependent privileges exist
+HINT: Use CASCADE to revoke them too.
+SELECT has_table_privilege('regress_priv_user2', 'atest4_su', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE GRANT OPTION FOR SELECT ON atest4_su FROM regress_priv_su CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user2', 'atest4_su', 'SELECT'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+DROP TABLE atest4_su;
+DROP ROLE regress_priv_su;
-- security-restricted operations
\c -
CREATE ROLE regress_sro_user;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index c34f5196fe5..a05aae34b6b 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1287,6 +1287,35 @@ SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE');
REVOKE UPDATE ON atest4_groupowned FROM regress_priv_group2 RESTRICT; -- ok
SELECT has_table_privilege('regress_priv_user1', 'atest4_groupowned', 'UPDATE'); -- false
+-- Same, but the intermediate grantor has become a superuser since issuing its
+-- grant (regress_priv_user1 -> regress_priv_su -> regress_priv_user2).
+RESET SESSION AUTHORIZATION;
+CREATE ROLE regress_priv_su;
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+
+CREATE TABLE atest4_su (a int);
+
+GRANT SELECT ON atest4_su TO regress_priv_su WITH GRANT OPTION;
+
+SET SESSION AUTHORIZATION regress_priv_su;
+
+GRANT SELECT ON atest4_su TO regress_priv_user2;
+
+RESET SESSION AUTHORIZATION;
+ALTER ROLE regress_priv_su SUPERUSER;
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+
+REVOKE GRANT OPTION FOR SELECT ON atest4_su FROM regress_priv_su; -- fail
+SELECT has_table_privilege('regress_priv_user2', 'atest4_su', 'SELECT'); -- true
+REVOKE GRANT OPTION FOR SELECT ON atest4_su FROM regress_priv_su CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user2', 'atest4_su', 'SELECT'); -- false
+
+RESET SESSION AUTHORIZATION;
+DROP TABLE atest4_su;
+DROP ROLE regress_priv_su;
+
-- security-restricted operations
\c -
CREATE ROLE regress_sro_user;
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-09-30 04:32 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-24 21:57 REVOKE's CASCADE protection doesn't work with INHERITed table owners Jacob Champion <jacob.champion@enterprisedb.com>
2026-06-26 00:12 ` Jacob Champion <jacob.champion@enterprisedb.com>
2026-07-06 11:03 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-07-08 22:54 ` Jacob Champion <jacob.champion@enterprisedb.com>
2026-07-09 14:48 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-08-10 11:41 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-30 04:32 ` Paul Kim <mok03127@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox