Making PostGIS extension persist in the search_path of a PostgreSQL database?

Question

Context

I have a script to set up a PostgreSQL database along with the postgis extension which I decided to install in an other schema than public, namely, the postgis schema. In my script, this schema is then set in the search path as explained here;

SET search_path TO "$user", postgis, topology, public;

After having successfully executed my creation script, I was totally surprised by this error while connected with the user role I defined (different than the postgres role) and using a PostGIS function to test it was correctly installed:

$ psql -U user -d my_database
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

my_database=# SELECT st_astext(ST_SetSRID(ST_MakePoint(12.0, 42.0),4326));
ERROR:  function st_makepoint(numeric, numeric) does not exist
LINE 1: SELECT st_astext(ST_SetSRID(ST_MakePoint(12.0, 42.0),4326));
                                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Then I checked for the search_path;

my_database=# show search_path;
        search_path        
---------------------------
 "$user", public, topology
(1 row)

Astonishing; PostGIS is no more here!

I have to enter the SET command again, manually, to make it come back:

$ psql -U user -d my_database
my_database=# SET search_path TO "$user", postgis, topology, public;
SET
my_database=# show search_path;
                 search_path                 
---------------------------------------------
 "$user", postgis, topology, public
(1 row)

But this doesn't persist across re-connections.

Question

How to make it persistent?

Related questions and links

Use PostGIS functions from an other schema (than public)

PostGIS functions do not exist even when public is in the search path

https://intellij-support.jetbrains.com/hc/en-us/community/posts/208393425-type-geometry-does-not-exist

Answer 1

General rule

The PostGIS extension must be in the user search_path as explained here

Detailed explanation

Using SET search_path TO "$user", public, postgis, topology; makes it immediately available while ALTER ROLE or ALTER DATABASE makes it available at the next session. But the latter commands make it persist through future connections.

Therefore, you would need e.g. to ALTER the user role with the following in addition to the immediate SET statement in your script if you want to use PostGIS in future sessions;

SET search_path TO "$user", postgis, topology, public;
ALTER ROLE user SET search_path TO "$user", postgis, topology, public;

A little warning

The order the schemas are set in the search_path is important because tables will be searched according to this order!

Credits, for having put me on the right track

With schema, function postgis_full_version() does not exist

https://stackoverflow.com/questions/2875610/permanently-set-postgresql-schema-path