Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tD9EE-00GInQ-0D for pgsql-general@arkaria.postgresql.org; Mon, 18 Nov 2024 21:24:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tD9EB-007rzD-G4 for pgsql-general@arkaria.postgresql.org; Mon, 18 Nov 2024 21:24:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tD9EA-007rz5-SX for pgsql-general@lists.postgresql.org; Mon, 18 Nov 2024 21:24:11 +0000 Received: from mail-qv1-xf2f.google.com ([2607:f8b0:4864:20::f2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tD9E7-002aFs-UM for pgsql-general@lists.postgresql.org; Mon, 18 Nov 2024 21:24:09 +0000 Received: by mail-qv1-xf2f.google.com with SMTP id 6a1803df08f44-6cc250bbc9eso28015686d6.2 for ; Mon, 18 Nov 2024 13:24:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=metrio.net; s=google; t=1731965046; x=1732569846; darn=lists.postgresql.org; h=mime-version:content-language:accept-language:message-id:date :thread-index:thread-topic:subject:to:from:from:to:cc:subject:date :message-id:reply-to; bh=+ZzdXsQQc3K+KTUi3ENDv636y3oQfLaMl5D5KHX/NnY=; b=fy3e44jPFb8/TkrSbNqS65bxmljjo8ITELFxzZCdf+Ocq2sLoTvajPxozH6QODBMS7 78DqB4PRZNaUFjAfl3PNTtlN9h4n+0FcetP7bewYicQBmWBcboynbwNbyKLCmLCgfx/z OJzEqLFyOP+pPxp71UtibmyUjSd5S02/WyN7noh4BkEDTLlDjrOE6tZGk8c30PLhLLH/ mTmAcf+nQciUNvufuaf0wvnsHaoCDoLpxyrQ6xpi/BiV5nsgxtzl1zfkBB24uzcxcriW hDF33a4JzGAlgZPkcJAPynxAYb7XBpDQuQq0oII6OK49C84DZFlepziwYsFbfp30k/Cl X/Zg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731965046; x=1732569846; h=mime-version:content-language:accept-language:message-id:date :thread-index:thread-topic:subject:to:from:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=+ZzdXsQQc3K+KTUi3ENDv636y3oQfLaMl5D5KHX/NnY=; b=OOkVyMYC38b/Sr0nAcu4rdP8tB5G1QUL7zBFI5UyrIeFzYnuJYobc13y3StKvoR7zn 86TiZctpKtiD9LPtw1FHzC7AhmnomV0BvnxCxikMTHGkjkp6IrrxgWLdarmcB18mfWNd yB/74sMewoaWChMZc1FYWUY+459xoqthU6cKy61W4HtclJG96jp67KIyCCz6YgXl1eX9 mlNTWBORdpQqaFxrJy0PJPm2E/7vNRPKG56cneWU40BfN3AUpBu+v3EbjtN0hnIgha4T /0n0JxqYdAXe/Ql8WL1lyBsQH9t3UZgQd7hxsac2+7JRu6O4lLcxj3Eciuv+/iIKQCcz b5Og== X-Gm-Message-State: AOJu0Yw3kY1QacfNLPQhx5EeGm7plDtDKP0RGFbYcXGGgAQIrLHdso+Z kUdEPX7FH9/QpUBuPHHPWI/ePiyJ8db+S4DV6sBQ3LX+BoKbFeXBueSBlbVl3E2tWPzmhXcr8hO T+LiGw6cWPJwZkDmYA7ppy32E6ADSGR016VR5Crwa+aj2OckDq4z+WIjL5fJVNt+LJQfQ334tLq LCgf2BoTsJmN8mysC6RQMDkc/6ahu+BR+rCa8cUUQjYj7AW0z9lRHoH6knWZcOckCZM1MtsDxUb qmcNtuQajyB+CynwqAL1XfCB/PfZaqcHf3qoJF5j6RT X-Google-Smtp-Source: AGHT+IErG6gKLKO2TloIi5UBv4CEhGX0ese9c9l5uNbG8+RuRs+QBFtA7Bv49zskOkcIPqLtckgr7Q== X-Received: by 2002:a05:6214:5411:b0:6cd:f6de:8ad2 with SMTP id 6a1803df08f44-6d3fb8acb9emr231714896d6.45.1731965046491; Mon, 18 Nov 2024 13:24:06 -0800 (PST) Received: from BN7PR08MB4195.namprd08.prod.outlook.com ([2603:1036:406:6f::5]) by smtp.gmail.com with ESMTPSA id 6a1803df08f44-6d40dd89290sm40425006d6.109.2024.11.18.13.24.05 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 18 Nov 2024 13:24:06 -0800 (PST) From: Maxime Legault-Venne To: "pgsql-general@lists.postgresql.org" Subject: Default session role broken in PostgreSQL 14.14? Thread-Topic: Default session role broken in PostgreSQL 14.14? Thread-Index: AQHbOfr6uL+Y7SUxfk+vWfcIj2TYEQ== X-MS-Exchange-MessageSentRepresentingType: 1 Date: Mon, 18 Nov 2024 21:24:04 +0000 Message-ID: Accept-Language: en-US Content-Language: en-CA X-MS-Has-Attach: X-MS-Exchange-Organization-SCL: -1 X-MS-TNEF-Correlator: X-MS-Exchange-Organization-RecordReviewCfmType: 0 x-ms-reactions: allow Content-Type: multipart/alternative; boundary="_000_DM6PR08MB4203384F44E9680BFDCC34A0FE272DM6PR08MB4203namp_" MIME-Version: 1.0 X-CLOUD-SEC-AV-Sent: true X-CLOUD-SEC-AV-Info: metrio,google_mail,monitor X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DM6PR08MB4203384F44E9680BFDCC34A0FE272DM6PR08MB4203namp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hello, I noticed a change of behaviour since PostgreSQL 14.14 that is breaking the= permissions in my application: it looks like the =93ALTER ROLE IN D= ATABASE SET ROLE =94 is no longer applied when is= connecting to the database. For example, I have this setup to create my da= tabase 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 tha= t db=92s admin group without the users needing to remember to run a =93SET = ROLE db1_admin_group=94 after establishing a connection to the database. In versions 14.13 and earlier, admin1 connecting to db1 would get the follo= wing: db1=3D> select current_user, session_user; current_user | session_user -----------------+-------------- db1_admin_group | admin1 But with 14.14, I get this result: db1=3D> select current_user, session_user; current_user | session_user --------------+-------------- admin1 | admin1 I know there was this CVE fixed in the latest release https://www.postgresq= l.org/support/security/CVE-2024-10978/, could it be related? Is the new beh= aviour I=92m observing expected or is it an issue? If it is expected, is th= ere any other recommended way I could achieve what I=92m trying to do? Thank you! Max --_000_DM6PR08MB4203384F44E9680BFDCC34A0FE272DM6PR08MB4203namp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Hello,

 

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

 

-- 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<= /o:p>

GRANT pg_write_all_data, pg_read_all_data TO db1_adm= in_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 permis= sions

REVOKE ALL ON DATABASE db1 FROM public;

REVOKE ALL ON SCHEMA public FROM public;<= /p>

 

This setup ensures me that the database objects are = properly created as that db=92s admin group without the users needing to re= member to run a =93SET ROLE db1_admin_group=94 after establishing a connect= ion to the database.

 

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

 

db1=3D> select current_user, session_user;

  current_user   | session_user<= /o:p>

-----------------+--------------

db1_admin_group | admin1

 

But with 14.14, I get this result:

 

db1=3D> select current_user, session_user;

current_user | session_user

--------------+--------------

admin1       | admin1<= o:p>

 

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

 

Thank you!

Max

--_000_DM6PR08MB4203384F44E9680BFDCC34A0FE272DM6PR08MB4203namp_--