pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedToday's Postgres Releases break login roles
9+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2024-11-24 5de08f136a Test "options=-crole=" and "ALTER DATABASE SET role".
2024-11-16 7b88529f43 1c05004a89 b0918c1286 edf80895f6 be062bfa54 c463338656 07c6e0f613 Fix per-session activation of ALTER {ROLE|DATABASE} SET role.
* Today's Postgres Releases break login roles
@ 2024-11-15 14:47 Etienne LAFARGE <etienne.lafarge@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Etienne LAFARGE @ 2024-11-15 14:47 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
Today, when upgrading from Postgres 15.8 to 15.9, we realized that the
login role set for a user (with ALTER USER my_user SET ROLE my_role) was
not automatically set upon login any more.
This is particularly problematic for users of HashiCorp Vault's dynamic
users (like us), who often rely on ALTER ROLE xxx SET ROLE yyy to make sure
that dynamic & short-lived users created by vault create postgres resources
as a long-lived role, and not as themselves.
We suspect this commit
<https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a5d2e6205;
to be the one that introduced this behavioral change.
I made a little testbench with docker compose so that the problem can be
reproduced easily and quickly (with comprehensive reproduction steps in the
README): https://github.com/elafarge/pg_role_bug_reproduction_testbench
(feel free to change the
I'm at your disposal if you have any questions.
Kind Regards,
-Étienne
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Today's Postgres Releases break login roles
@ 2024-11-15 23:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Etienne LAFARGE <etienne.lafarge@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2024-11-15 23:54 UTC (permalink / raw)
To: Etienne LAFARGE <etienne.lafarge@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Etienne LAFARGE <etienne.lafarge@gmail.com> writes:
> Today, when upgrading from Postgres 15.8 to 15.9, we realized that the
> login role set for a user (with ALTER USER my_user SET ROLE my_role) was
> not automatically set upon login any more.
Right, that was not intended. We'll fix it next week.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Today's Postgres Releases break login roles
@ 2024-11-16 04:53 Noah Misch <noah@leadboat.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Noah Misch @ 2024-11-16 04:53 UTC (permalink / raw)
To: Etienne LAFARGE <etienne.lafarge@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
On Fri, Nov 15, 2024 at 06:54:00PM -0500, Tom Lane wrote:
> Etienne LAFARGE <etienne.lafarge@gmail.com> writes:
> > Today, when upgrading from Postgres 15.8 to 15.9, we realized that the
> > login role set for a user (with ALTER USER my_user SET ROLE my_role) was
> > not automatically set upon login any more.
>
> Right, that was not intended. We'll fix it next week.
Per https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/
we're issuing releases next Thursday to fix this and fix a separate ABI break.
I've pushed postgr.es/c/7b88529 for $SUBJECT. To maximize time-in-buildfarm,
I've pushed it without a public comment period. I welcome post-commit review,
and I'm happy to amend the change if anyone finds something that would have
been cause for change during a standard opportunity for pre-commit review.
That said, given our wrap schedule, feedback after Saturday is less likely to
change next week's releases. Such feedback could still affect the 2025-02
releases, of course.
I overlooked this consequence of the 2024-11-14 change. I apologize.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Today's Postgres Releases break login roles
@ 2024-11-16 10:45 Matthew Woodcraft <matthew@woodcraft.me.uk>
parent: Etienne LAFARGE <etienne.lafarge@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Matthew Woodcraft @ 2024-11-16 10:45 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: Etienne LAFARGE <etienne.lafarge@gmail.com>
Etienne LAFARGE <etienne.lafarge@gmail.com> writes:
> Today, when upgrading from Postgres 15.8 to 15.9, we realized that the
> login role set for a user (with ALTER USER my_user SET ROLE my_role) was
> not automatically set upon login any more.
I noticed one other change with the 14.14 point release, which I think
is likely to be related.
Previously a command like this would work to select a role immediately after
login:
psql "options='-crole=foo'"
With 14.14 as far as I can tell it has no effect.
-M-
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Today's Postgres Releases break login roles
@ 2024-11-16 15:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Woodcraft <matthew@woodcraft.me.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2024-11-16 15:45 UTC (permalink / raw)
To: Matthew Woodcraft <matthew@woodcraft.me.uk>; +Cc: pgsql-bugs@lists.postgresql.org, Etienne LAFARGE <etienne.lafarge@gmail.com>
Matthew Woodcraft <matthew@woodcraft.me.uk> writes:
> I noticed one other change with the 14.14 point release, which I think
> is likely to be related.
> Previously a command like this would work to select a role immediately after
> login:
> psql "options='-crole=foo'"
> With 14.14 as far as I can tell it has no effect.
Yeah, same issue.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Today's Postgres Releases break login roles
@ 2024-11-16 18:34 Noah Misch <noah@leadboat.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Noah Misch @ 2024-11-16 18:34 UTC (permalink / raw)
To: Matthew Woodcraft <matthew@woodcraft.me.uk>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org, Etienne LAFARGE <etienne.lafarge@gmail.com>
On Sat, Nov 16, 2024 at 10:45:46AM -0500, Tom Lane wrote:
> Matthew Woodcraft <matthew@woodcraft.me.uk> writes:
> > I noticed one other change with the 14.14 point release, which I think
> > is likely to be related.
> > Previously a command like this would work to select a role immediately after
> > login:
> > psql "options='-crole=foo'"
> > With 14.14 as far as I can tell it has no effect.
>
> Yeah, same issue.
Agreed. I cut some corners by not directly testing that or ALTER DATABASE, so
here are some more tests. The output is the same on master @ now, on master @
2024-11-10 (before the regression), and on v12. It reproduces the trouble on
master @ 2024-11-12. That's sufficient confirmation for the moment, so I'll
hold this until the releases wrap and make it master-only.
Attachments:
test-PGOPTIONS-role-v1.patch (text/plain, 9.4K) download
Author: Noah Misch <noah@leadboat.com>
Commit: Noah Misch <noah@leadboat.com>
Test "options=-crole=" and "ALTER DATABASE SET role".
Commit 7b88529f4363994450bd4cd3c172006a8a77e222 fixed a regression
spanning these features, but it didn't test them. It did test code
paths sufficient for their present implementations, so no back-patch.
Reported by Matthew Woodcraft. Reviewed by FIXME.
Discussion: https://postgr.es/m/87iksnsbhx.fsf@golux.woodcraft.me.uk
diff --git a/src/test/modules/unsafe_tests/Makefile b/src/test/modules/unsafe_tests/Makefile
index d4ff227..a85c854 100644
--- a/src/test/modules/unsafe_tests/Makefile
+++ b/src/test/modules/unsafe_tests/Makefile
@@ -2,6 +2,8 @@
REGRESS = rolenames setconfig alter_system_table guc_privs
REGRESS_OPTS = \
+ --create-role=regress_authenticated_user_db_sr \
+ --create-role=regress_authenticated_user_db_ssa \
--create-role=regress_authenticated_user_sr \
--create-role=regress_authenticated_user_ssa
diff --git a/src/test/modules/unsafe_tests/expected/setconfig.out b/src/test/modules/unsafe_tests/expected/setconfig.out
index 6a021d9..5f42443 100644
--- a/src/test/modules/unsafe_tests/expected/setconfig.out
+++ b/src/test/modules/unsafe_tests/expected/setconfig.out
@@ -1,24 +1,92 @@
-- This is borderline unsafe in that an additional login-capable user exists
-- during the test run. Under installcheck, a too-permissive pg_hba.conf
-- might allow unwanted logins as regress_authenticated_user_ssa.
+-- Setup catalog state.
+ALTER USER regress_authenticated_user_db_ssa superuser;
ALTER USER regress_authenticated_user_ssa superuser;
CREATE ROLE regress_session_user;
CREATE ROLE regress_current_user;
+GRANT regress_current_user TO regress_authenticated_user_db_sr;
GRANT regress_current_user TO regress_authenticated_user_sr;
+GRANT regress_session_user TO regress_authenticated_user_db_ssa;
GRANT regress_session_user TO regress_authenticated_user_ssa;
+DO $$BEGIN EXECUTE format(
+ 'ALTER DATABASE %I SET session_authorization = regress_session_user',
+ current_catalog); END$$;
ALTER ROLE regress_authenticated_user_ssa
SET session_authorization = regress_session_user;
ALTER ROLE regress_authenticated_user_sr SET ROLE = regress_current_user;
-\c - regress_authenticated_user_sr
+-- Test ALTER DATABASE consequences
+-- The longstanding historical behavior is that session_authorization in
+-- setconfig has no effect. Hence, session_user remains
+-- regress_authenticated_user_ssa. See comment in InitializeSessionUserId().
+\c - regress_authenticated_user_db_ssa
+SELECT current_user, session_user;
+ current_user | session_user
+-----------------------------------+-----------------------------------
+ regress_authenticated_user_db_ssa | regress_authenticated_user_db_ssa
+(1 row)
+
+-- We document "The DEFAULT and RESET forms reset the session and current user
+-- identifiers to be the originally authenticated user name." If we let
+-- session_authorization in setconfig have an effect, we'll need to decide
+-- whether to make RESET differ from DEFAULT.
+RESET SESSION AUTHORIZATION;
+SELECT current_user, session_user;
+ current_user | session_user
+-----------------------------------+-----------------------------------
+ regress_authenticated_user_db_ssa | regress_authenticated_user_db_ssa
+(1 row)
+
+DO $$BEGIN
+ EXECUTE format(
+ 'ALTER DATABASE %I RESET session_authorization', current_catalog);
+ EXECUTE format(
+ 'ALTER DATABASE %I SET role = regress_current_user', current_catalog);
+END$$;
+\c - regress_authenticated_user_db_sr
+SELECT current_user, session_user;
+ current_user | session_user
+----------------------+----------------------------------
+ regress_current_user | regress_authenticated_user_db_sr
+(1 row)
+
+-- Back to superuser, to reverse ALTER DATABASE
+\c - regress_authenticated_user_db_ssa
+SELECT current_user, session_user;
+ current_user | session_user
+----------------------+-----------------------------------
+ regress_current_user | regress_authenticated_user_db_ssa
+(1 row)
+
+SET ROLE NONE;
+DO $$BEGIN EXECUTE format(
+ 'ALTER DATABASE %I RESET role', current_catalog); END$$;
+-- Test connection string options
+\c -reuse-previous=on "user=regress_authenticated_user_db_sr options=-crole=regress_current_user"
+SELECT current_user, session_user;
+ current_user | session_user
+----------------------+----------------------------------
+ regress_current_user | regress_authenticated_user_db_sr
+(1 row)
+
+-- As above, session_authorization has no effect.
+\c -reuse-previous=on "user=regress_authenticated_user_db_ssa options=-csession_authorization=regress_session_user"
+SELECT current_user, session_user;
+ current_user | session_user
+-----------------------------------+-----------------------------------
+ regress_authenticated_user_db_ssa | regress_authenticated_user_db_ssa
+(1 row)
+
+-- Test ALTER ROLE consequences
+\c -reuse-previous=on "user=regress_authenticated_user_sr options="
SELECT current_user, session_user;
current_user | session_user
----------------------+-------------------------------
regress_current_user | regress_authenticated_user_sr
(1 row)
--- The longstanding historical behavior is that session_authorization in
--- setconfig has no effect. Hence, session_user remains
--- regress_authenticated_user_ssa. See comment in InitializeSessionUserId().
+-- As above, session_authorization has no effect.
\c - regress_authenticated_user_ssa
SELECT current_user, session_user;
current_user | session_user
diff --git a/src/test/modules/unsafe_tests/meson.build b/src/test/modules/unsafe_tests/meson.build
index 3e174c7..cf1480a 100644
--- a/src/test/modules/unsafe_tests/meson.build
+++ b/src/test/modules/unsafe_tests/meson.build
@@ -11,7 +11,9 @@ tests += {
'alter_system_table',
'guc_privs',
],
- 'regress_args': ['--create-role=regress_authenticated_user_sr',
+ 'regress_args': ['--create-role=regress_authenticated_user_db_sr',
+ '--create-role=regress_authenticated_user_db_ssa',
+ '--create-role=regress_authenticated_user_sr',
'--create-role=regress_authenticated_user_ssa'],
'runningcheck': false,
},
diff --git a/src/test/modules/unsafe_tests/sql/setconfig.sql b/src/test/modules/unsafe_tests/sql/setconfig.sql
index 8817a7c..81296d1 100644
--- a/src/test/modules/unsafe_tests/sql/setconfig.sql
+++ b/src/test/modules/unsafe_tests/sql/setconfig.sql
@@ -2,21 +2,70 @@
-- during the test run. Under installcheck, a too-permissive pg_hba.conf
-- might allow unwanted logins as regress_authenticated_user_ssa.
+-- Setup catalog state.
+ALTER USER regress_authenticated_user_db_ssa superuser;
ALTER USER regress_authenticated_user_ssa superuser;
CREATE ROLE regress_session_user;
CREATE ROLE regress_current_user;
+GRANT regress_current_user TO regress_authenticated_user_db_sr;
GRANT regress_current_user TO regress_authenticated_user_sr;
+GRANT regress_session_user TO regress_authenticated_user_db_ssa;
GRANT regress_session_user TO regress_authenticated_user_ssa;
+DO $$BEGIN EXECUTE format(
+ 'ALTER DATABASE %I SET session_authorization = regress_session_user',
+ current_catalog); END$$;
ALTER ROLE regress_authenticated_user_ssa
SET session_authorization = regress_session_user;
ALTER ROLE regress_authenticated_user_sr SET ROLE = regress_current_user;
-\c - regress_authenticated_user_sr
-SELECT current_user, session_user;
+
+-- Test ALTER DATABASE consequences
-- The longstanding historical behavior is that session_authorization in
-- setconfig has no effect. Hence, session_user remains
-- regress_authenticated_user_ssa. See comment in InitializeSessionUserId().
+\c - regress_authenticated_user_db_ssa
+SELECT current_user, session_user;
+-- We document "The DEFAULT and RESET forms reset the session and current user
+-- identifiers to be the originally authenticated user name." If we let
+-- session_authorization in setconfig have an effect, we'll need to decide
+-- whether to make RESET differ from DEFAULT.
+RESET SESSION AUTHORIZATION;
+SELECT current_user, session_user;
+DO $$BEGIN
+ EXECUTE format(
+ 'ALTER DATABASE %I RESET session_authorization', current_catalog);
+ EXECUTE format(
+ 'ALTER DATABASE %I SET role = regress_current_user', current_catalog);
+END$$;
+
+\c - regress_authenticated_user_db_sr
+SELECT current_user, session_user;
+
+-- Back to superuser, to reverse ALTER DATABASE
+\c - regress_authenticated_user_db_ssa
+SELECT current_user, session_user;
+SET ROLE NONE;
+DO $$BEGIN EXECUTE format(
+ 'ALTER DATABASE %I RESET role', current_catalog); END$$;
+
+
+-- Test connection string options
+
+\c -reuse-previous=on "user=regress_authenticated_user_db_sr options=-crole=regress_current_user"
+SELECT current_user, session_user;
+
+-- As above, session_authorization has no effect.
+\c -reuse-previous=on "user=regress_authenticated_user_db_ssa options=-csession_authorization=regress_session_user"
+SELECT current_user, session_user;
+
+
+-- Test ALTER ROLE consequences
+
+\c -reuse-previous=on "user=regress_authenticated_user_sr options="
+SELECT current_user, session_user;
+
+-- As above, session_authorization has no effect.
\c - regress_authenticated_user_ssa
SELECT current_user, session_user;
RESET SESSION AUTHORIZATION;
* Re: Today's Postgres Releases break login roles
@ 2024-11-16 20:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Noah Misch <noah@leadboat.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2024-11-16 20:11 UTC (permalink / raw)
To: Noah Misch <noah@leadboat.com>; +Cc: Matthew Woodcraft <matthew@woodcraft.me.uk>; pgsql-bugs@lists.postgresql.org, Etienne LAFARGE <etienne.lafarge@gmail.com>
Noah Misch <noah@leadboat.com> writes:
> Agreed. I cut some corners by not directly testing that or ALTER DATABASE, so
> here are some more tests. The output is the same on master @ now, on master @
> 2024-11-10 (before the regression), and on v12. It reproduces the trouble on
> master @ 2024-11-12. That's sufficient confirmation for the moment, so I'll
> hold this until the releases wrap and make it master-only.
If you only intend to apply it to master, there's no need to wait.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Today's Postgres Releases break login roles
@ 2024-11-20 13:30 Etienne LAFARGE <etienne.lafarge@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Etienne LAFARGE @ 2024-11-20 13:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Noah Misch <noah@leadboat.com>; Matthew Woodcraft <matthew@woodcraft.me.uk>; pgsql-bugs@lists.postgresql.org
Thank you so much for acknowledging & fixing this so promptly Noah & Tom
!!!
Have a great week both.
Best regards,
-Étienne
On Sat, Nov 16, 2024 at 9:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Noah Misch <noah@leadboat.com> writes:
> > Agreed. I cut some corners by not directly testing that or ALTER
> DATABASE, so
> > here are some more tests. The output is the same on master @ now, on
> master @
> > 2024-11-10 (before the regression), and on v12. It reproduces the
> trouble on
> > master @ 2024-11-12. That's sufficient confirmation for the moment, so
> I'll
> > hold this until the releases wrap and make it master-only.
>
> If you only intend to apply it to master, there's no need to wait.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Today's Postgres Releases break login roles
@ 2024-11-25 03:42 Noah Misch <noah@leadboat.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Noah Misch @ 2024-11-25 03:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Woodcraft <matthew@woodcraft.me.uk>; pgsql-bugs@lists.postgresql.org, Etienne LAFARGE <etienne.lafarge@gmail.com>
On Sat, Nov 16, 2024 at 03:11:11PM -0500, Tom Lane wrote:
> Noah Misch <noah@leadboat.com> writes:
> > Agreed. I cut some corners by not directly testing that or ALTER DATABASE, so
> > here are some more tests. The output is the same on master @ now, on master @
> > 2024-11-10 (before the regression), and on v12. It reproduces the trouble on
> > master @ 2024-11-12. That's sufficient confirmation for the moment, so I'll
> > hold this until the releases wrap and make it master-only.
>
> If you only intend to apply it to master, there's no need to wait.
True, the delay wasn't obligatory. I pushed it today, for whatever reason.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-11-25 03:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-15 14:47 Today's Postgres Releases break login roles Etienne LAFARGE <etienne.lafarge@gmail.com>
2024-11-15 23:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-16 04:53 ` Noah Misch <noah@leadboat.com>
2024-11-16 10:45 ` Matthew Woodcraft <matthew@woodcraft.me.uk>
2024-11-16 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-16 18:34 ` Noah Misch <noah@leadboat.com>
2024-11-16 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-20 13:30 ` Etienne LAFARGE <etienne.lafarge@gmail.com>
2024-11-25 03:42 ` Noah Misch <noah@leadboat.com>
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