public inbox for [email protected]  

help / color / mirror / Atom feed

Default session role broken in PostgreSQL 14.14?
2+ messages / 2 participants
[nested] [flat]

* Default session role broken in PostgreSQL 14.14?
@ 2024-11-18 21:24  Maxime Legault-Venne <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Maxime Legault-Venne @ 2024-11-18 21:24 UTC (permalink / raw)
  To: [email protected] <[email protected]>

Hello,

I noticed a change of behaviour since PostgreSQL 14.14 that is breaking the permissions in my application: it looks like the “ALTER ROLE <role> IN DATABASE <db> SET ROLE <session-role>” is no longer applied when <role> is connecting to the database. For example, I have this setup to create my database and roles:

-- Create my admin group for the new db
CREATE ROLE db1_admin_group;
GRANT db1_admin_group TO postgres;

-- Create the new db
CREATE DATABASE db1 OWNER db1_admin_group;

-- Grant proper permissions to the admin group
GRANT pg_write_all_data, pg_read_all_data TO db1_admin_group;
GRANT CONNECT ON DATABASE db1 TO db1_admin_group;

-- Create a new user in the admin group
CREATE USER admin1 PASSWORD 'admin1pass';
GRANT db1_admin_group TO admin1;
ALTER ROLE admin1 IN DATABASE db1 SET ROLE db1_admin_group;

-- Then connecting on db1, revoke all default permissions
REVOKE ALL ON DATABASE db1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;

This setup ensures me that the database objects are properly created as that db’s admin group without the users needing to remember to run a “SET ROLE db1_admin_group” after establishing a connection to the database.

In versions 14.13 and earlier, admin1 connecting to db1 would get the following:

db1=> select current_user, session_user;
  current_user   | session_user
-----------------+--------------
db1_admin_group | admin1

But with 14.14, I get this result:

db1=> select current_user, session_user;
current_user | session_user
--------------+--------------
admin1       | admin1

I know there was this CVE fixed in the latest release https://www.postgresql.org/support/security/CVE-2024-10978/, could it be related? Is the new behaviour I’m observing expected or is it an issue? If it is expected, is there any other recommended way I could achieve what I’m trying to do?

Thank you!
Max


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Default session role broken in PostgreSQL 14.14?
@ 2024-11-18 21:27  David G. Johnston <[email protected]>
  parent: Maxime Legault-Venne <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: David G. Johnston @ 2024-11-18 21:27 UTC (permalink / raw)
  To: Maxime Legault-Venne <[email protected]>; +Cc: [email protected] <[email protected]>

On Mon, Nov 18, 2024 at 2:24 PM Maxime Legault-Venne <
[email protected]> wrote:

>
> I noticed a change of behaviour since PostgreSQL 14.14 that is breaking
> the permissions in my application: it looks like the “ALTER ROLE <role> IN
> DATABASE <db> SET ROLE <session-role>” is no longer applied when <role> is
> connecting to the database.
>

Reported as a bug previously here and fixed in this week's out-of-band
update.

https://www.postgresql.org/message-id/[email protected]...

David J.


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-11-18 21:27 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-18 21:24 Default session role broken in PostgreSQL 14.14? Maxime Legault-Venne <[email protected]>
2024-11-18 21:27 ` David G. Johnston <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox