Hi Rob,
> On 22. Jan, 2021, at 23:48, Rob Sargent <robjsargent@gmail.com> wrote:
>
> Yes, I'm confused. As I said in reply to Jeff, I would rather not need to remember to set the search_path, which I
canavoid if I login as "role".
I didn't follow the whole discussion, so sorry, to just jump in here.
You don't need to remember the search_path once your role is set up correctly. The following example demonstrates that
youcan set a default search_path for a role. But keep in mind that it is a *default* search_path for that role, which
meansa) it's for that role only and b) since it's a default, it is in effect only after the next login to that role.
db01=# \conninfo
You are connected to database "db01" as user "paul" on host ...
db01=# show search_path;
search_path
-----------------
"$user", public
(1 row)
db01=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
db01=# create schema s1;
CREATE SCHEMA
db01=# create schema s2;
CREATE SCHEMA
db01=# create schema s3;
CREATE SCHEMA
db01=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
s1 | paul
s2 | paul
s3 | paul
(4 rows)
Now comes the crucial part. Notice that the search path still shows the old value until I reconnect:
db01=# alter role paul set search_path to s2, s3;
ALTER ROLE
db01=# show search_path;
search_path
-----------------
"$user", public
(1 row)
db01=# \c db01
psql (13.1, server 12.5)
You are now connected to database "db01" as user "paul".
db01=# show search_path;
search_path
-------------
s2, s3
(1 row)
See? No $user, public or s1 after connecting to the database, only s2 and s3 as specified by me.
The same applies to resetting it to its default values "$user", public:
db01=# alter role paul reset search_path;
ALTER ROLE
db01=# show search_path;
search_path
-------------
s2, s3
(1 row)
db01=# \c db01
psql (13.1, server 12.5)
You are now connected to database "db01" as user "paul".
db01=# show search_path;
search_path
-----------------
"$user", public
(1 row)
There's a lot of descriptions on how this works. Just google for something like "postgres set default search path".
Also, see chapter 5.9.3 of the docs: The Schema Search Path
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
Hope this helps.
Cheers,
Paul