From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Dustin Sanza <dustin.sanza@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
Date: Sun, 25 Oct 2026 08:32:51 +0100
Message-ID: <3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at> (raw)
In-Reply-To: <CAL1nGzS2JFLqUhjNh5VaRHCZ9Y92oBvUUiaX8k3i6josY-CS7Q@mail.gmail.com>
References: <CAL1nGzS2JFLqUhjNh5VaRHCZ9Y92oBvUUiaX8k3i6josY-CS7Q@mail.gmail.com>
On Fri, 2026-10-23 at 14:20 -0700, Dustin Sanza wrote:
> I'm looking for a supported PostgreSQL 17 mechanism to keep selected
> role attributes and memberships stable between verification and
> COMMIT. This is a design question, not a bug report.
>
> Assume the transaction's role is NOSUPERUSER, has CREATEROLE and ADMIN
> OPTION on the relevant non-superuser roles, but has no direct UPDATE
> privilege on system catalogs. Other legitimately authorized
> administrative sessions may be running; this is not an attempt to
> constrain a malicious superuser.
>
> The intended sequence is:
> 1. Acquire the necessary protection for a finite, identified set of roles.
> 2. Verify their relevant attributes and memberships against an
> expected state; abort on unexpected drift.
> 3. Complete a release-state change and COMMIT without a competing role
> change invalidating that verification in the meantime.
>
> [...]
>
> My reading is that COMMENT ON ROLE could therefore serialize
> membership GRANT/REVOKE for that granted role, but does not by itself
> cover concurrent ALTER ROLE attribute changes. I have not run a
> concurrency reproduction yet.
>
> 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.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: laurenz.albe@cybertec.at, dustin.sanza@gmail.com
Subject: Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
In-Reply-To: <3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at>
References: <CAL1nGzS2JFLqUhjNh5VaRHCZ9Y92oBvUUiaX8k3i6josY-CS7Q@mail.gmail.com> <3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox