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 1s9mwf-0059aU-Bw for pgsql-general@arkaria.postgresql.org; Wed, 22 May 2024 14:27:58 +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 1s9mwf-00HVrk-FB for pgsql-general@arkaria.postgresql.org; Wed, 22 May 2024 14:27:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s9mwf-00HVrc-4M for pgsql-general@lists.postgresql.org; Wed, 22 May 2024 14:27:57 +0000 Received: from mail-ot1-x332.google.com ([2607:f8b0:4864:20::332]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s9mwc-000DJd-F5 for pgsql-general@postgresql.org; Wed, 22 May 2024 14:27:56 +0000 Received: by mail-ot1-x332.google.com with SMTP id 46e09a7af769-6f0f7ec684cso2888996a34.0 for ; Wed, 22 May 2024 07:27:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1716388072; x=1716992872; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=PLaxLIL5wmcdlSwA6TDe5Sy+zqCI+ATkGrR9wZEessA=; b=jKf8XrK9fQ1FGKGKO+8mwNpXsQCB8HPRjmqAarL+YJyy7ylbEcDQ8a6X5lcrO6cxfj V6ai6F6YhEaUHgj+CwRMHm4WWvGUJ+TPF+TyBUnIri/Q5Lof57yJPmwgHStZqjTy8Lir O04wyAAsI2gk2i7qzM3S2vH4a7mPxYnA+pWKpRZ4M0ZZDYV8ncUZnmT7WENkRyg3TYRY ScSOPgJ7LY5v8jSJC3tVTFApOnOIi3y4zkJbrqCf79UKmf+lbtsXwZNZTYkKXd5xwM0Z GpRmp1l3+dMnsaA+AOIc1r4Pnm5NA1T1jN7RqbTrjM2OHnp14lZG0HhFXzLY98iy2mnC xCTQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716388072; x=1716992872; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=PLaxLIL5wmcdlSwA6TDe5Sy+zqCI+ATkGrR9wZEessA=; b=UkCr0wXKHW8IZJ/Njb5uKVL/kdKB/iHpxRSDo5V1flaHINaHt/He/wf6FV7Z274BXz tmiaP1Kuo9s+WvcFeivQyKXWsct0C2TYAerru+n4Yya6Vy/35SjiGwU6hks+13Zc1TVV rdJM5G98NHw4tOPTnokDfCD8QfhK56c6NUttEomB5EcNebwwI8GU8AgLGOGHwP6kNaWq Qabha+UR/xhftIemjUAct/PCAmxr93RCTPUD0UBuEl3tRyyFh7yNN9PKcZGIAOQ48Y+8 CF7gi/nKhUPEalA5Ka1Be0xsuyQ3PDMMkHmZcm7MYzcm/9jQzoJklWNltWukiNJKf/ej NRnw== X-Gm-Message-State: AOJu0YzW5Evw62cCjcmCj1CZteSMetg5vV0WjQvD1NWI1+QQqSFXkefq bWK5kgIyF/KIMIChQK9BPBG6zf5129JnxrPT5ve+QAvk1Y8uDhKChjNv/F83zJ/Y921KqZzT5t+ fHpR7Pzlx6qG3kqIiLjtQg/E5X9wyIlpW X-Google-Smtp-Source: AGHT+IEIpQ6H7tHdoJlHTW3P9mYXGu6Bs/TOP3+pEFzstVnt7SXB0YrfBwfvrxiAfCj6ppm/0/v7We4YWXO0t3UBa/c= X-Received: by 2002:a05:6871:580e:b0:24c:50a8:a154 with SMTP id 586e51a60fabf-24c68aec035mr2496955fac.1.1716388072310; Wed, 22 May 2024 07:27:52 -0700 (PDT) MIME-Version: 1.0 From: Ron Johnson Date: Wed, 22 May 2024 10:27:41 -0400 Message-ID: Subject: search_path and SET ROLE To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000058cdd306190bbd2b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000058cdd306190bbd2b Content-Type: text/plain; charset="UTF-8" PG 9.6.24 (Soon, I swear!) It seems that the search_path of the role that you SET ROLE to does not become the new search_path. Am I missing something, or is that PG's behavior? AS USER postgres ================ $ psql -h 10.143.170.52 -Xac "CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN;" CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN; CREATE ROLE $ psql -h 10.143.170.52 -Xac "CREATE USER rjohnson IN GROUP dbagrp INHERIT;" CREATE USER rjohnson IN GROUP dbagrp INHERIT; CREATE ROLE [postgres@FISPMONDB001 ~]$ psql -h 10.143.170.52 -Xac "CREATE USER \"11026270\" IN GROUP dbagrp INHERIT PASSWORD '${NewPass}' VALID UNTIL '2024-06-30 23:59:59';" CREATE USER "11026270" IN GROUP dbagrp INHERIT PASSWORD 'linenoise' VALID UNTIL '2024-06-30 23:59:59'; CREATE ROLE $ psql -h 10.143.170.52 -Xac "ALTER ROLE dbagrp set search_path = dbagrp, public, dba, cds, tms;" ALTER ROLE dbagrp set search_path = dbagrp, public, dba, cds, tms; ALTER ROLE AS USER rjohnson ================ [rjohnson@fpslbxcdsdbppg1 ~]$ psql -dCDSLBXW psql (9.6.24) Type "help" for help. CDSLBXW=> SET ROLE dbagrp; SET CDSLBXW=# CDSLBXW=# SHOW SEARCH_PATH; search_path ----------------- "$user", public (1 row) Back to user postgres ================= $ psql -h 10.143.170.52 -Xac "ALTER ROLE rjohnson set search_path = dbagrp, public, dba, cds, tms;" ALTER ROLE rjohnson set search_path = dbagrp, public, dba, cds, tms; ALTER ROLE Back to user rjohnson ================= [rjohnson@fpslbxcdsdbppg1 ~]$ psql -dCDSLBXW psql (9.6.24) Type "help" for help. CDSLBXW=> CDSLBXW=> SET ROLE dbagrp; SET CDSLBXW=# SHOW SEARCH_PATH; search_path ------------------------------- dbagrp, public, dba, cds, tms (1 row) --00000000000058cdd306190bbd2b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
PG 9.6.24 (Soon, I swear!)

It see= ms that the search_path of the role that you SET ROLE to does not become th= e new search_path.

Am I missing=C2=A0something, or is th= at PG's behavior?

AS USER postgres
=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

= $ psql -h 10.143.170.52 -Xac "CREATE ROLE dbagrp SUPERUSER INHERIT NOL= OGIN;"
CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN;
CREATE ROLE=

$ psql -h 10.143.170.52 -Xac "CREATE USER rjohnson IN GROUP db= agrp INHERIT;"
CREATE USER rjohnson IN GROUP dbagrp INHERIT;
CRE= ATE ROLE

[postgres@FISPMONDB001 ~]$ psql -h 10.143.170.52 -Xac "= ;CREATE USER \"11026270\" IN GROUP dbagrp INHERIT PASSWORD '$= {NewPass}' VALID UNTIL '2024-06-30 23:59:59';"
CREATE U= SER "11026270" IN GROUP dbagrp INHERIT PASSWORD 'linenoise= 9; VALID UNTIL '2024-06-30 23:59:59';
CREATE ROLE

$ psql = -h 10.143.170.52 -Xac "ALTER ROLE dbagrp set search_path =3D dbagrp, p= ublic, dba, cds, tms;"
ALTER ROLE dbagrp set search_path =3D dbagrp= , public, dba, cds, tms;
ALTER ROLE

AS USER rjohnson
= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

[rjohnson@fpslbxcdsdbppg1 ~]$ psql -dCDSLBXW
psql (9.6.24)
Type= "help" for help.

CDSLBXW=3D> SET ROLE dbagrp;
SETCDSLBXW=3D#
CDSLBXW=3D# SHOW SEARCH_PATH;
=C2=A0 =C2=A0search_path= =C2=A0
-----------------
=C2=A0"$user", public
(1 row)=


Back to user postgres
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D

$ psql -h 10.143.170.= 52 -Xac "ALTER ROLE rjohnson set search_path =3D dbagrp, public, dba, = cds, tms;"
ALTER ROLE rjohnson set search_path =3D dbagrp, public, = dba, cds, tms;
ALTER ROLE

Back to user rjohnson
= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

[rjohnson@fpslbxcdsdbppg1 ~]$ psql -dCDSLBXW
psql (9.6.24)
T= ype "help" for help.

CDSLBXW=3D>
CDSLBXW=3D> SET= ROLE dbagrp;
SET

CDSLBXW=3D# SHOW SEARCH_PATH;
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 search_path =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
----= ---------------------------
=C2=A0dbagrp, public, dba, cds, tms
(1 ro= w)
--00000000000058cdd306190bbd2b--