From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ronljohnsonjr@gmail.com>
cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: search_path and SET ROLE
In-reply-to: <CANzqJaBoAkdXMkPvdXqhkBduLqpTbU7BdjyZePcuWkFnKi1SDQ@mail.gmail.com>
References: <CANzqJaBoAkdXMkPvdXqhkBduLqpTbU7BdjyZePcuWkFnKi1SDQ@mail.gmail.com>
Comments: In-reply-to Ron Johnson <ronljohnsonjr@gmail.com>
	message dated "Wed, 22 May 2024 10:27:41 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4165840.1716397819.1@sss.pgh.pa.us>
Date: Wed, 22 May 2024 13:10:19 -0400
Message-ID: <4165841.1716397819@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4165841.1716397819%40sss.pgh.pa.us>
Precedence: bulk

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