| 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
| 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 |