public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bertrand Drouvot <[email protected]>
Subject: [PATCH v2 2/2] Protect role resolution in roleSpecsToIds() against concurrent DROP
Date: Mon, 6 Jul 2026 08:28:41 +0000
roleSpecsToIds() resolves role names to OIDs without acquiring any lock.
A concurrent DROP ROLE that commits between this resolution and the caller's use
of the OID leaves the caller operating on a stale OID, which can create orphaned
pg_auth_members entries.
Fix this by acquiring AccessShareLock on each resolved role within
roleSpecsToIds(), ensuring the role cannot be dropped while any caller is using
its OID.
Author: Bertrand Drouvot <[email protected]>
Reported-by: Virender Singla <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/aki6fMNLUx6%2BBR8K%40bdtpg
Discussion: https://postgr.es/m/CAM6Zo8woa62ZFHtMKox6a4jb8qQ%3Dw87R2L0K8347iE-juQL2EA%40mail.gmail.com
---
src/backend/commands/user.c | 13 +++++-
.../expected/role-membership-drop-member.out | 36 ++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/role-membership-drop-member.spec | 43 +++++++++++++++++++
4 files changed, 92 insertions(+), 1 deletion(-)
13.8% src/backend/commands/
42.9% src/test/isolation/expected/
42.2% src/test/isolation/specs/
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 5b869e91c17..05ec753f4f0 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -1778,6 +1778,8 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt)
* roleSpecsToIds
*
* Given a list of RoleSpecs, generate a list of role OIDs in the same order.
+ * Each role is locked with AccessShareLock to prevent concurrent DROP ROLE
+ * from removing it between resolution and the caller's catalog update.
*
* ROLESPEC_PUBLIC is not allowed.
*/
@@ -1792,7 +1794,16 @@ roleSpecsToIds(List *memberNames)
RoleSpec *rolespec = lfirst_node(RoleSpec, l);
Oid roleid;
- roleid = get_rolespec_oid(rolespec, false);
+ if (rolespec->roletype == ROLESPEC_CSTRING)
+ roleid = RoleNameGetOid(rolespec->rolename,
+ AccessShareLock, false,
+ NULL, NULL);
+ else
+ {
+ roleid = get_rolespec_oid(rolespec, false);
+ LockSharedObject(AuthIdRelationId, roleid, 0,
+ AccessShareLock);
+ }
result = lappend_oid(result, roleid);
}
return result;
diff --git a/src/test/isolation/expected/role-membership-drop-member.out b/src/test/isolation/expected/role-membership-drop-member.out
new file mode 100644
index 00000000000..e6cae59d2c9
--- /dev/null
+++ b/src/test/isolation/expected/role-membership-drop-member.out
@@ -0,0 +1,36 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_begin s1_grant s2_drop_member s1_commit
+step s1_begin: BEGIN;
+step s1_grant: GRANT regress_role_group TO regress_role_member;
+step s2_drop_member: DROP ROLE regress_role_member; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_member: <... completed>
+
+starting permutation: s1_begin s1_alter_add s2_drop_member s1_commit
+step s1_begin: BEGIN;
+step s1_alter_add: ALTER GROUP regress_role_group ADD USER regress_role_member;
+step s2_drop_member: DROP ROLE regress_role_member; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_member: <... completed>
+
+starting permutation: s1_begin s1_create_role s2_drop_member s1_commit
+step s1_begin: BEGIN;
+step s1_create_role: CREATE ROLE regress_role_new ROLE regress_role_member;
+step s2_drop_member: DROP ROLE regress_role_member; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_member: <... completed>
+
+starting permutation: s1_begin s1_drop_owned s2_drop_member s1_commit
+step s1_begin: BEGIN;
+step s1_drop_owned: DROP OWNED BY regress_role_member;
+step s2_drop_member: DROP ROLE regress_role_member; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_member: <... completed>
+
+starting permutation: s1_begin s1_reassign_owned s2_drop_member s1_commit
+step s1_begin: BEGIN;
+step s1_reassign_owned: REASSIGN OWNED BY regress_role_member TO regress_role_group;
+step s2_drop_member: DROP ROLE regress_role_member; <waiting ...>
+step s1_commit: COMMIT;
+step s2_drop_member: <... completed>
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index b8ebe92553c..8fb8b52b77f 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -128,3 +128,4 @@ test: matview-write-skew
test: lock-nowait
test: for-portion-of
test: ddl-dependency-locking
+test: role-membership-drop-member
diff --git a/src/test/isolation/specs/role-membership-drop-member.spec b/src/test/isolation/specs/role-membership-drop-member.spec
new file mode 100644
index 00000000000..e0140826e52
--- /dev/null
+++ b/src/test/isolation/specs/role-membership-drop-member.spec
@@ -0,0 +1,43 @@
+# Test that role membership commands properly lock the grantee/member
+# role to prevent concurrent DROP ROLE from creating orphaned # pg_auth_members
+# entries.
+
+setup
+{
+ CREATE ROLE regress_role_group;
+ CREATE ROLE regress_role_member;
+}
+
+teardown
+{
+ DROP ROLE IF EXISTS regress_role_group;
+ DROP ROLE IF EXISTS regress_role_member;
+ DROP ROLE IF EXISTS regress_role_new;
+}
+
+session s1
+step s1_begin { BEGIN; }
+step s1_grant { GRANT regress_role_group TO regress_role_member; }
+step s1_alter_add { ALTER GROUP regress_role_group ADD USER regress_role_member; }
+step s1_create_role { CREATE ROLE regress_role_new ROLE regress_role_member; }
+step s1_drop_owned { DROP OWNED BY regress_role_member; }
+step s1_reassign_owned { REASSIGN OWNED BY regress_role_member TO regress_role_group; }
+step s1_commit { COMMIT; }
+
+session s2
+step s2_drop_member { DROP ROLE regress_role_member; }
+
+# GRANT role TO member - concurrent DROP of the member
+permutation s1_begin s1_grant s2_drop_member s1_commit
+
+# ALTER ROLE ADD USER - concurrent DROP of the member
+permutation s1_begin s1_alter_add s2_drop_member s1_commit
+
+# CREATE ROLE ... ROLE member - concurrent DROP of the member
+permutation s1_begin s1_create_role s2_drop_member s1_commit
+
+# DROP OWNED BY role - concurrent DROP of the role
+permutation s1_begin s1_drop_owned s2_drop_member s1_commit
+
+# REASSIGN OWNED BY role - concurrent DROP of the role
+permutation s1_begin s1_reassign_owned s2_drop_member s1_commit
--
2.34.1
--cOHldBwZEf1OqVbN--
view thread (78+ messages) latest in thread
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]
Subject: Re: [PATCH v2 2/2] Protect role resolution in roleSpecsToIds() against concurrent DROP
In-Reply-To: <no-message-id-1855874@localhost>
* 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