search_path and SET ROLE

On Wednesday, May 22, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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?

Yes, attaching a setting to a non-login role is basically pointless as
those settings are only applied during the login process.

David J.

On 5/22/24 07:27, Ron Johnson wrote:

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)

https://www.postgresql.org/docs/current/sql-alterrole.html

Whenever the role subsequently starts a new session, the specified value
becomes the session default, overriding whatever setting is present in
postgresql.conf or has been received from the postgres command line.
This only happens at login time; executing SET ROLE or SET SESSION
AUTHORIZATION does not cause new configuration values to be set.
Settings set for all databases are overridden by database-specific
settings attached to a role. Settings for specific databases or specific
roles override settings for all roles.

--
Adrian Klaver
adrian.klaver@aklaver.com

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=# create role r1;
CREATE ROLE
regression=# create schema r1 authorization r1;
CREATE SCHEMA
regression=# select current_schemas(true), current_user;
current_schemas | current_user
---------------------+--------------
{pg_catalog,public} | postgres
(1 row)

regression=# set role r1;
SET
regression=> select current_schemas(true), current_user;
current_schemas | current_user
------------------------+--------------
{pg_catalog,r1,public} | r1
(1 row)

regression=> 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.

regards, tom lane

On Wed, May 22, 2024 at 1:10 PM 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=# create role r1;
CREATE ROLE
regression=# create schema r1 authorization r1;
CREATE SCHEMA
regression=# select current_schemas(true), current_user;
current_schemas | current_user
---------------------+--------------
{pg_catalog,public} | postgres
(1 row)

regression=# set role r1;
SET
regression=> select current_schemas(true), current_user;
current_schemas | current_user
------------------------+--------------
{pg_catalog,r1,public} | r1
(1 row)

regression=> 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 = public, dba, sch1, sch2, sch3, sch4;
CREATE USER joe IN GROUP dbagrp INHERIT PASSWORD = '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.

On Wed, 22 May 2024 at 13:48, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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.

\dt *.*

But I am skeptical how often you really want this in a real database with
more than a few tables. Surely \dn+ followed by \dt [schemaname].* for a
few strategically chosen [schemaname] would be more useful?

On Wed, May 22, 2024 at 2:02 PM Isaac Morland <isaac.morland@gmail.com>
wrote:

On Wed, 22 May 2024 at 13:48, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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.

\dt *.*

Also shows information_schema, pg_catalog, and pg_toast. I can adjust to
that, though.

But I am skeptical how often you really want this in a real database with
more than a few tables. Surely \dn+ followed by \dt [schemaname].* for a
few strategically chosen [schemaname] would be more useful?

More than you'd think. I'm always looking up the definition of this table
or that table (mostly for indices and keys), and I never remember which
schema they're in.

st 22. 5. 2024 v 21:38 odesílatel Ron Johnson <ronljohnsonjr@gmail.com>
napsal:

On Wed, May 22, 2024 at 2:02 PM Isaac Morland <isaac.morland@gmail.com>
wrote:

On Wed, 22 May 2024 at 13:48, Ron Johnson <ronljohnsonjr@gmail.com>
wrote:

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.

\dt *.*

Also shows information_schema, pg_catalog, and pg_toast. I can adjust to
that, though.

But I am skeptical how often you really want this in a real database with
more than a few tables. Surely \dn+ followed by \dt [schemaname].* for a
few strategically chosen [schemaname] would be more useful?

More than you'd think. I'm always looking up the definition of this table
or that table (mostly for indices and keys), and I never remember which
schema they're in.

\d *.pg_class

Unfortunately in this case, tab complete doesn't work