public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: search_path and SET ROLE
Date: Wed, 22 May 2024 07:48:04 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaBoAkdXMkPvdXqhkBduLqpTbU7BdjyZePcuWkFnKi1SDQ@mail.gmail.com>
References: <CANzqJaBoAkdXMkPvdXqhkBduLqpTbU7BdjyZePcuWkFnKi1SDQ@mail.gmail.com>

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
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: search_path and SET ROLE
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox