Re: localhost ssl

Hi Rob,

> On 22. Jan, 2021, at 23:48, Rob Sargent <robjsargent(at)gmail(dot)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 can avoid 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 you can set a default search_path for a role. But keep in mind that it is a *default* search_path for that role, which means a) 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