On Fri, 2026-10-23 at 14:20 -0700, Dustin Sanza wrote: > I'm looking for a supported PostgreSQL 17 mechanism to keep selected > role attributes and memberships stable between verification and > COMMIT. This is a design question, not a bug report. > > Assume the transaction's role is NOSUPERUSER, has CREATEROLE and ADMIN > OPTION on the relevant non-superuser roles, but has no direct UPDATE > privilege on system catalogs. Other legitimately authorized > administrative sessions may be running; this is not an attempt to > constrain a malicious superuser. > > The intended sequence is: > 1. Acquire the necessary protection for a finite, identified set of roles. > 2. Verify their relevant attributes and memberships against an > expected state; abort on unexpected drift. > 3. Complete a release-state change and COMMIT without a competing role > change invalidating that verification in the meantime. > > [...] > > My reading is that COMMENT ON ROLE could therefore serialize > membership GRANT/REVOKE for that granted role, but does not by itself > cover concurrent ALTER ROLE attribute changes. I have not run a > concurrency reproduction yet. > > The narrow question: under the privileges above, is there supported > SQL that prevents or safely serializes concurrent ALTER ROLE attribute > changes until COMMIT without first rewriting those attributes and > potentially concealing unexpected drift?
I don't think so. You could use a SECURITY DEFINER function owned by a superuser that LOCK pg_catalog.pg_authid IN SHARE MODE; to prevent concurrent role modifications. Yours, Laurenz Albe
