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
