Horton

Thread

Commits

  1. Test "options=-crole=" and "ALTER DATABASE SET role".

  2. Fix per-session activation of ALTER {ROLE|DATABASE} SET role.

  1. Today's Postgres Releases break login roles

    Etienne LAFARGE <etienne.lafarge@gmail.com> — 2024-11-15T14:47:26Z

    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

  2. Re: Today's Postgres Releases break login roles

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-15T23:54:00Z

    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

  3. Re: Today's Postgres Releases break login roles

    Noah Misch <noah@leadboat.com> — 2024-11-16T04:53:58Z

    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.

  4. Re: Today's Postgres Releases break login roles

    Matthew Woodcraft <matthew@woodcraft.me.uk> — 2024-11-16T10:45:30Z

    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-

  5. Re: Today's Postgres Releases break login roles

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-16T15:45:46Z

    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

  6. Re: Today's Postgres Releases break login roles

    Noah Misch <noah@leadboat.com> — 2024-11-16T18:34:59Z

    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.

  7. Re: Today's Postgres Releases break login roles

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-16T20:11:11Z

    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

  8. Re: Today's Postgres Releases break login roles

    Etienne LAFARGE <etienne.lafarge@gmail.com> — 2024-11-20T13:30:09Z

    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
>

  9. Re: Today's Postgres Releases break login roles

    Noah Misch <noah@leadboat.com> — 2024-11-25T03:42:51Z

    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.