PostgreSQL 17: protecting role attributes between verification and COMMIT
PostgreSQL 17: protecting role attributes between verification and COMMIT
От:
Dustin Sanza <dustin.sanza@gmail.com>
Дата:
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
Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
От:
Olivier Gautherot <ogautherot@gautherot.net>
Дата:
Hi Dustin,
El sáb, 24 de oct de 2026, 8:45 a.m., Dustin Sanza <dustin.sanza@gmail.com> escribió:
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 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?
As I understand it, you may alter roles between transactions for that user, not during a transaction.
<...>
Please keep me copied on replies, as I am not subscribed.
Thank you,
Dustin Sanza
Best regards
Olivier
Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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
Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Laurenz Albe writes: > On Fri, 2026-10-23 at 14:20 -0700, Dustin Sanza wrote: >> 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. Or SELECT ... FROM pg_catalog.pg_authid FOR UPDATE, which would have the advantage of locking only relevant rows not the whole catalog. regards, tom lane
Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
От:
Dustin Sanza <dustin.sanza@gmail.com>
Дата:
Hi Tom and Laurenz, Thank you both. The distinction between a table-level SHARE lock and row-level FOR UPDATE is very helpful. I have two follow-up questions about PostgreSQL 17 core behavior: 1. Would SELECT FOR UPDATE on the relevant pg_authid rows reliably serialize concurrent ALTER ROLE attribute updates until the locking transaction commits, assuming the lock is acquired through a sufficiently privileged function? 2. For membership changes in pg_auth_members, would a separate catalog-level lock be necessary to protect against both changes to existing memberships and insertion of new memberships? Or is there a supported narrower mechanism that covers those cases? I'm particularly interested in avoiding any verification-to-COMMIT gap without modifying the role attributes being verified. I understand that hosted-provider privileges are a separate question, and I'll investigate those independently. Thank you again, Dustin Sanza On Sun, 25 Oct 2026 09:07:16 -0400, Tom Lane wrote: > Laurenz Albe writes: > > On Fri, 2026-10-23 at 14:20 -0700, Dustin Sanza wrote: > >> 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. > > Or SELECT ... FROM pg_catalog.pg_authid FOR UPDATE, which would have > the advantage of locking only relevant rows not the whole catalog. > > regards, tom lane
Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Sun, 2026-10-25 at 09:04 -0500, Dustin Sanza wrote: > I have two follow-up questions about PostgreSQL 17 core behavior: > > 1. Would SELECT FOR UPDATE on the relevant pg_authid rows reliably > serialize concurrent ALTER ROLE attribute updates until the locking > transaction commits, assuming the lock is acquired through a > sufficiently privileged function? Yes. Row and table locks are held until the end of the transaction. > 2. For membership changes in pg_auth_members, would a separate > catalog-level lock be necessary to protect against both changes to > existing memberships and insertion of new memberships? Or is there a > supported narrower mechanism that covers those cases? For that, you'd also need a SHARE lock on pg_auth_members. > I'm particularly interested in avoiding any verification-to-COMMIT gap > without modifying the role attributes being verified. There is no race condition with either row or table locks. > I understand that hosted-provider privileges are a separate question, > and I'll investigate those independently. In a hosted database, you don't get a superuser, so you couldn't use neither an exclusive row lock nor a SHARE table lock on catalog tables. Yours, Laurenz Albe