Ron Johnson <ronljohnsonjr@xxxxxxxxx> 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
