PostgreSQL 17: protecting role attributes between verification and COMMIT

Поиск
Список
Период
Сортировка
Искать

PostgreSQL 17: protecting role attributes between verification and COMMIT

От:
Dustin Sanza <dustin.sanza@gmail.com>
Дата:
Hi,

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.

The COMMENT documentation states that it acquires SHARE UPDATE
EXCLUSIVE on its target:
https://www.postgresql.org/docs/17/sql-comment.html

The PG17 source appears to have AddRoleMems/DelRoleMems acquire that
lock mode on the granted role:
https://github.com/postgres/postgres/blob/REL_17_6/src/backend/commands/user.c

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?

A descriptive-metadata write may be acceptable; silently normalizing
the authority being verified is not. Advisory locks alone would not
cover administrative sessions that do not participate. For example,
after the final successful check, another session must not be able to
change a protected role's INHERIT or LOGIN attribute and commit before
the release transaction does.

If this requires stronger privileges, connection exclusion, or a
different transaction design, a source reference or existing
concurrency test explaining the boundary would be very helpful.
Hosted-provider permissions will be checked separately; this question
is about PostgreSQL 17 core behavior.

Please keep me copied on replies, as I am not subscribed.

Thank you,
Dustin Sanza


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

От:
Olivier Gautherot <ogautherot@gautherot.net>
Дата:
Hi Dustin,

El sáb, 24 de oct de 2026, 8:45 a.m., Dustin Sanza <dustin.sanza@gmail.com> escribió:
Hi,

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 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?

As I understand it, you may alter roles between transactions for that user, not during a transaction.

<...>
Please keep me copied on replies, as I am not subscribed.

Thank you,
Dustin Sanza

Best regards
Olivier

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

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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


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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Laurenz Albe  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


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

От:
Dustin Sanza <dustin.sanza@gmail.com>
Дата:
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  wrote:
> Laurenz Albe  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


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

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Sun, 2026-10-25 at 09:04 -0500, Dustin Sanza wrote:
> 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?

Yes.  Row and table locks are held until the end of the transaction.

> 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?

For that, you'd also need a SHARE lock on pg_auth_members.

> I'm particularly interested in avoiding any verification-to-COMMIT gap
> without modifying the role attributes being verified.

There is no race condition with either row or table locks.

> I understand that hosted-provider privileges are a separate question,
> and I'll investigate those independently.

In a hosted database, you don't get a superuser, so you couldn't
use neither an exclusive row lock nor a SHARE table lock on catalog
tables.

Yours,
Laurenz Albe


FAQ