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

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

In response to

Responses

Browse pgsql-general by date

  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