Thread

1. Fix races conditions in DropRole() and GrantRole()

   Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-04T07:47:08Z

   Hi hackers,
   
   While working on [1], I observed that DropRole() and GrantRole() have the same
   "use stale data after the lock is acquired" issues.
   
   Indeed, DropRole() and GrantRole() resolve the role name to an OID before acquiring
   LockSharedObject() on the role. A concurrent session that commits a DROP ROLE
   between the read and the lock acquisition leaves the first session acting on a
   stale OID.
   
   Examples:
   
   1/ DROP ROLE + concurrent DROP ROLE
   
   CREATE ROLE testrole;
   gdb breakpoint at user.c:1198 (before LockSharedObject) on session 1
   
   session 1: DROP ROLE testrole;
   session 1 is paused by the breakpoint
   session 2: DROP ROLE testrole;
   continue session 1 produces:
   ERROR: could not find tuple for role 24662
   
   2/ GRANT ROLE + concurrent DROP ROLE
   
   CREATE ROLE testrole;
   CREATE ROLE testmember;
   gdb breakpoint at user.c:1716 (before LockSharedObject) on session 1
   
   session 1: GRANT testrole TO testmember;
   session is paused by the breakpoint
   session 2: DROP ROLE testrole;
   continue session 1: GRANT ROLE succeeds
   
   It produces an orphaned pg_auth_members entry:
   
   postgres=#   SELECT m.member::regrole, m.roleid, r.rolname
     FROM pg_auth_members m
     LEFT JOIN pg_roles r ON m.roleid = r.oid
     WHERE r.oid IS NULL;
      member   | roleid | rolname
   ------------+--------+---------
    testmember |  16386 |
   
   
   The patch attached fixes the races by using the same approach as
   RangeVarGetRelidExtended(): It encapsulates name resolution, permission checking
   (via a caller-supplied callback), and lock acquisition inside a retry loop driven
   by SharedInvalidMessageCounter. If invalidation messages arrive between name
   resolution and locking, indicating concurrent DDL, the function retries.
   
   The lock is kept across retries and only released if the name resolves to a
   different OID on the next iteration.
   
   Two callbacks are provided:
   - RoleNameCallbackForDropRole(): checks current/session user, superuser attribute,
   and ADMIN OPTION privilege before locking. This is similar to what DropRole() is
   currently doing before LockSharedObject().
   - RoleNameCallbackForGrantRole(): calls check_role_membership_authorization() to
   verify the current user can grant/revoke membership. This is similar to what GrantRole()
   is currently doing before calling AddRoleMems()/DelRoleMems().
   
   DropRole() and GrantRole() now call RoleNameGetOid() with appropriate lock
   levels.
   
   Remark:
   
   AlterRole() does not need the fix because it calls CatalogTupleUpdate() on the
   pg_authid tuple before AddRoleMems(), which blocks a concurrent DROP ROLE.
   
   [1]: https://postgr.es/m/akZUpiDa1UfmzYxL%40bdtpg
   
   Regards,
   
   -- 
   Bertrand Drouvot
   PostgreSQL Contributors Team
   RDS Open Source Databases
   Amazon Web Services: https://aws.amazon.com
   

2. Re: Fix races conditions in DropRole() and GrantRole()

   Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-06T13:22:39Z

   Hi,
   
   On Sat, Jul 04, 2026 at 07:47:08AM +0000, Bertrand Drouvot wrote:
   > The patch attached fixes the races by using the same approach as
   > RangeVarGetRelidExtended(): It encapsulates name resolution, permission checking
   > (via a caller-supplied callback), and lock acquisition inside a retry loop driven
   > by SharedInvalidMessageCounter. If invalidation messages arrive between name
   > resolution and locking, indicating concurrent DDL, the function retries.
   
   Also I think we could make use of the same approach to fix one of the issue that
   was discussed in [1], means:
   
   "
   create role my_group;
   create role dropped_member;
   
   Session 1: begin;grant my_group to dropped_member;
   Session 2: drop role dropped_member;
   Session 1: commit;
   "
   
   producing an orphaned pg_auth_members entry:
   
   postgres=# SELECT m.member, m.roleid, r.rolname
       FROM pg_auth_members m
       LEFT JOIN pg_roles r ON m.member = r.oid
       WHERE r.oid IS NULL;
    member | roleid | rolname
   --------+--------+---------
     16385 |  16384 |
   (1 row)
   
   
   0002 fixes this by acquiring AccessShareLock on each resolved role within
   roleSpecsToIds(), ensuring the role cannot be dropped while any caller is using
   its OID.
   
   It also fixes other cases that would produce orphaned pg_auth_members entries,
   like "ALTER GROUP my_group ADD USER dropped_member" or
   "CREATE ROLE new_group ROLE dropped_member" with a concurrent DROP of
   dropped_member.
   
   Note that DropOwnedObjects() and ReassignOwnedObjects() check has_privs_of_role()
   after the lock is acquired. Moving those into a callback could be done if we feel
   the need.
   
   [1]: https://postgr.es/m/CAM6Zo8woa62ZFHtMKox6a4jb8qQ%3Dw87R2L0K8347iE-juQL2EA%40mail.gmail.com
   
   Regards,
   
   -- 
   Bertrand Drouvot
   PostgreSQL Contributors Team
   RDS Open Source Databases
   Amazon Web Services: https://aws.amazon.com