Subject: Re: search_path and SET ROLE

From: Tom Lane <[email-masked]>
Date: Wed, 22 May 2024 13:10:19 -0400

Ron Johnson <[email-masked]> 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

---

From: Ron Johnson <[email-masked]>
Date: Wed, 22 May 2024 13:47:59 -0400

>    current_schemas   | current_user

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.