pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 17: protecting role attributes between verification and COMMIT
6+ messages / 4 participants
[nested] [flat]
* PostgreSQL 17: protecting role attributes between verification and COMMIT
@ 2026-10-23 21:20 Dustin Sanza <dustin.sanza@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Dustin Sanza @ 2026-10-23 21:20 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
@ 2026-10-24 07:27 Olivier Gautherot <ogautherot@gautherot.net>
parent: Dustin Sanza <dustin.sanza@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Olivier Gautherot @ 2026-10-24 07:27 UTC (permalink / raw)
To: Dustin Sanza <dustin.sanza@gmail.com>; +Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
@ 2026-10-25 07:32 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Dustin Sanza <dustin.sanza@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2026-10-25 07:32 UTC (permalink / raw)
To: Dustin Sanza <dustin.sanza@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
@ 2026-10-25 13:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2026-10-25 13:07 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Dustin Sanza <dustin.sanza@gmail.com>; pgsql-general@lists.postgresql.org
Laurenz Albe <laurenz.albe@cybertec.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
@ 2026-10-25 14:04 Dustin Sanza <dustin.sanza@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Dustin Sanza @ 2026-10-25 14:04 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; laurenz.albe@cybertec.at; +Cc: pgsql-general@lists.postgresql.org
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@sss.pgh.pa.us> wrote:
> Laurenz Albe <laurenz.albe@cybertec.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL 17: protecting role attributes between verification and COMMIT
@ 2026-10-25 19:04 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Dustin Sanza <dustin.sanza@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2026-10-25 19:04 UTC (permalink / raw)
To: Dustin Sanza <dustin.sanza@gmail.com>; tgl@sss.pgh.pa.us; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-10-25 19:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-23 21:20 PostgreSQL 17: protecting role attributes between verification and COMMIT Dustin Sanza <dustin.sanza@gmail.com>
2026-10-24 07:27 ` Olivier Gautherot <ogautherot@gautherot.net>
2026-10-25 07:32 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-25 13:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-25 14:04 ` Dustin Sanza <dustin.sanza@gmail.com>
2026-10-25 19:04 ` Laurenz Albe <laurenz.albe@cybertec.at>
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