MIME-Version: 1.0
References: <CANzqJaBoAkdXMkPvdXqhkBduLqpTbU7BdjyZePcuWkFnKi1SDQ@mail.gmail.com>
 <4165841.1716397819@sss.pgh.pa.us>
In-Reply-To: <4165841.1716397819@sss.pgh.pa.us>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 22 May 2024 13:47:59 -0400
Message-ID: <CANzqJaDfSaB=EvGCABT8VA_2FwYM4MYUmQYT5e-bw-Tf_EH13Q@mail.gmail.com>
Subject: Re: search_path and SET ROLE
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bf95e806190e8908"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDfSaB%3DEvGCABT8VA_2FwYM4MYUmQYT5e-bw-Tf_EH13Q%40mail.gmail.com>
Precedence: bulk

--000000000000bf95e806190e8908
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, May 22, 2024 at 1:10=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> > It seems that the search_path of the role that you SET ROLE to does not
> > become the new search_path.
>
> It does for me:
>
> regression=3D# create role r1;
> CREATE ROLE
> regression=3D# create schema r1 authorization r1;
> CREATE SCHEMA
> regression=3D# select current_schemas(true), current_user;
>    current_schemas   | current_user
> ---------------------+--------------
>  {pg_catalog,public} | postgres
> (1 row)
>
> regression=3D# set role r1;
> SET
> regression=3D> select current_schemas(true), current_user;
>     current_schemas     | current_user
> ------------------------+--------------
>  {pg_catalog,r1,public} | r1
> (1 row)
>
> regression=3D> show search_path ;
>    search_path
> -----------------
>  "$user", public
> (1 row)
>
> The fine manual says that $user tracks the result of
> CURRENT_USER, and at least in this example it's doing that.
> (I hasten to add that I would not swear there are no
> bugs in this area.)
>
> > Am I missing something, or is that PG's behavior?
>
> I bet what you missed is granting (at least) USAGE on the
> schema to that role.  PG will silently ignore unreadable
> schemas when computing the effective search path.
>

There are multiple schemata in (sometimes) multiple databases on (many)
multiple servers.

As a superuser administrator, I need to be able to see ALL tables in ALL
schemas when running "\dt", not just the ones in "$user" and public.  And I
need it to act consistently across all the systems.

(Heck, none of our schemas are named the same as roles.)

This would be useful for account maintenance:

CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN;
ALTER ROLE dbagrp SET search_path =3D public, dba, sch1, sch2, sch3, sch4;
CREATE USER joe IN GROUP dbagrp INHERIT PASSWORD =3D 'linenoise';

Then, as user joe:
SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)
SET ROLE dbagrp RELOAD SESSION; -- note the new clause
SHOW search_path;
   search_path
-----------------------------------
public , dba, sch1, sch2, sch3, sch4
(1 row)

When a new DBA comes on board, add him/her to dbagrp, and they
automagically have everything  that dbagrp has.
Now, each dba must individually be given a search_path.  If you forget, or
forget to add some schemas, etc, mistakes ger made and time is wasted.

--000000000000bf95e806190e8908
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, May 22, 2024 at 1:10=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com=
" target=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; writes:<br>
&gt; It seems that the search_path of the role that you SET ROLE to does no=
t<br>
&gt; become the new search_path.<br>
<br>
It does for me:<br>
<br>
regression=3D# create role r1;<br>
CREATE ROLE<br>
regression=3D# create schema r1 authorization r1;<br>
CREATE SCHEMA<br>
regression=3D# select current_schemas(true), current_user;<br>
=C2=A0 =C2=A0current_schemas=C2=A0 =C2=A0| current_user <br>
---------------------+--------------<br>
=C2=A0{pg_catalog,public} | postgres<br>
(1 row)<br>
<br>
regression=3D# set role r1;<br>
SET<br>
regression=3D&gt; select current_schemas(true), current_user;<br>
=C2=A0 =C2=A0 current_schemas=C2=A0 =C2=A0 =C2=A0| current_user <br>
------------------------+--------------<br>
=C2=A0{pg_catalog,r1,public} | r1<br>
(1 row)<br>
<br>
regression=3D&gt; show search_path ;<br>
=C2=A0 =C2=A0search_path=C2=A0 =C2=A0<br>
-----------------<br>
=C2=A0&quot;$user&quot;, public<br>
(1 row)<br>
<br>
The fine manual says that $user tracks the result of<br>
CURRENT_USER, and at least in this example it&#39;s doing that.<br>
(I hasten to add that I would not swear there are no<br>
bugs in this area.)<br>
<br>
&gt; Am I missing something, or is that PG&#39;s behavior?<br>
<br>
I bet what you missed is granting (at least) USAGE on the<br>
schema to that role.=C2=A0 PG will silently ignore unreadable<br>
schemas when computing the effective search path.<br></blockquote><div><br>=
</div><div>There are multiple schemata in (sometimes) multiple databases on=
 (many) multiple servers.</div><div><br></div><div>As a superuser administr=
ator, I need to be able to see ALL tables in ALL schemas when running &quot=
;\dt&quot;, not just the ones in &quot;$user&quot; and public.=C2=A0 And I =
need it to act consistently across all the systems.<br></div><div><br></div=
><div>(Heck, none of our schemas are named the same as roles.)</div><div><b=
r></div><div>This would be useful for account maintenance:</div><div><br></=
div><div><font face=3D"monospace">CREATE ROLE dbagrp SUPERUSER INHERIT NOLO=
GIN;</font></div><div><font face=3D"monospace">ALTER ROLE dbagrp SET search=
_path =3D public, dba, sch1, sch2, sch3, sch4;</font></div><div><font face=
=3D"monospace">CREATE USER joe IN GROUP dbagrp=C2=A0INHERIT PASSWORD =3D &#=
39;linenoise&#39;;<br></font></div><div><br></div><div>Then, as user joe:</=
div><div><font face=3D"monospace">SHOW search_path;</font></div><div><font =
face=3D"monospace">=C2=A0 =C2=A0search_path=C2=A0 =C2=A0<br>---------------=
--<br>=C2=A0&quot;$user&quot;, public<br>(1 row)</font><br></div><div><div>=
<font face=3D"monospace">SET ROLE dbagrp RELOAD SESSION; -- note the new cl=
ause</font></div><div></div></div><div><div><font face=3D"monospace">SHOW s=
earch_path;</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0search_p=
ath=C2=A0 =C2=A0<br>-----------------------------------<br>public</font>

<span style=3D"font-family:monospace">, dba, sch1, sch2, sch3, sch4</span><=
font face=3D"monospace"><br>(1 row)</font><br></div><div><br class=3D"gmail=
-Apple-interchange-newline"></div></div><div>When a new DBA comes on board,=
 add him/her to dbagrp, and they automagically have everything=C2=A0 that d=
bagrp=C2=A0has.<br></div><div>Now, each dba must individually be given a se=
arch_path.=C2=A0 If you forget, or forget to add some schemas, etc, mistake=
s ger made and time is wasted.</div><div><br></div></div></div>

--000000000000bf95e806190e8908--