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

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

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 이정호 2026-10-25 13:17:16 Request for Official Technical Clarification on PostgreSQL statement_timeout Error
Previous Message Tom Lane 2026-10-25 13:00:09 Re: Debugging --- ERROR: function does not exist