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.