pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (6+ messages)  latest in thread

Message-ID: <3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at>
Permalink:  ../3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at/
Also on:    postgresql.org/message-id/3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at

 · 

reply

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