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 <[email protected]> wrote: > Laurenz Albe <[email protected]> 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
