Postgres : Set search_path for a specific user and a specific database

By default, the user is connected on the 'public' schema. If you have specifics schemas, you must set search_path setting.

To do this for a specific database you can use the following statement :

ALTER USER test_user IN DATABASE test_database SET search_path TO schema1, schema2;

To check the correct setting, you can use the following command :

SELECT r.rolname, d.datname, rs.setconfig 
  FROM   pg_db_role_setting rs 
    LEFT JOIN pg_roles r ON r.oid = rs.setrole 
    LEFT JOIN pg_database d ON d.oid = rs.setdatabase; 

With this command, you must have this result :

rolname |    datname      |                setconfig
----------+-----------------+-----------------------------------
test_user | test_database   | {"search_path=schema1, schema2"}  

Next Post Previous Post