Hi, 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. The COMMENT documentation states that it acquires SHARE UPDATE EXCLUSIVE on its target: https://www.postgresql.org/docs/17/sql-comment.html The PG17 source appears to have AddRoleMems/DelRoleMems acquire that lock mode on the granted role: https://github.com/postgres/postgres/blob/REL_17_6/src/backend/commands/user.c 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? A descriptive-metadata write may be acceptable; silently normalizing the authority being verified is not. Advisory locks alone would not cover administrative sessions that do not participate. For example, after the final successful check, another session must not be able to change a protected role's INHERIT or LOGIN attribute and commit before the release transaction does. If this requires stronger privileges, connection exclusion, or a different transaction design, a source reference or existing concurrency test explaining the boundary would be very helpful. Hosted-provider permissions will be checked separately; this question is about PostgreSQL 17 core behavior. Please keep me copied on replies, as I am not subscribed. Thank you, Dustin Sanza
