Re: PostgreSQL 17: protecting role attributes between verification and COMMIT

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Dustin Sanza <dustin(dot)sanza(at)gmail(dot)com>, tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
Date: 2026-10-25 19:04:51
Message-ID: ed3758696d5aeba417890eab6e1b55874021335a.camel@cybertec.at
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2026-10-25 19:55:53 Re: Debugging --- ERROR: function does not exist
Previous Message Tom Lane 2026-10-25 18:00:20 Re: Debugging --- ERROR: function does not exist