| From: | Dustin Sanza <dustin(dot)sanza(at)gmail(dot)com> |
|---|---|
| To: | tgl(at)sss(dot)pgh(dot)pa(dot)us, laurenz(dot)albe(at)cybertec(dot)at |
| Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: PostgreSQL 17: protecting role attributes between verification and COMMIT |
| Date: | 2026-10-25 14:04:08 |
| Message-ID: | CAL1nGzSPQdVJ32EeyH43kCtn24v34d5fUZy5B_UP84ys+U5r3w@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Adrian Klaver | 2026-10-25 14:42:55 | Re: Debugging --- ERROR: function does not exist |
| Previous Message | Mark | 2026-10-25 13:26:18 | Re: Debugging --- ERROR: function does not exist |