public inbox for [email protected]
help / color / mirror / Atom feedRe: search_path and SET ROLE
2+ messages / 2 participants
[nested] [flat]
* Re: search_path and SET ROLE
@ 2024-05-22 17:10 Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Tom Lane @ 2024-05-22 17:10 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
Ron Johnson <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: search_path and SET ROLE
@ 2024-05-22 17:47 Ron Johnson <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Ron Johnson @ 2024-05-22 17:47 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-general
On Wed, May 22, 2024 at 1:10 PM Tom Lane <[email protected]> wrote:
> Ron Johnson <[email protected]> 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-05-22 17:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-22 17:10 Re: search_path and SET ROLE Tom Lane <[email protected]>
2024-05-22 17:47 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox