Date: Mon, 6 Jul 2026 13:22:39 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Fix races conditions in DropRole() and GrantRole()
Message-ID: <akusH9lDGd6kx2rz@bdtpg>
References: <aki6fMNLUx6+BR8K@bdtpg>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="cOHldBwZEf1OqVbN"
Content-Disposition: inline
In-Reply-To: <aki6fMNLUx6+BR8K@bdtpg>
Archived-At: <https://www.postgresql.org/message-id/akusH9lDGd6kx2rz%40bdtpg>
Precedence: bulk


--cOHldBwZEf1OqVbN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--cOHldBwZEf1OqVbN
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="v2-0001-Add-RoleNameGetOid-with-invalidation-based-retry-.patch"