What is the search_path for a given database and user?

I can see the current search_path with:

show search_path ;

And I can set the search_path for the current session with:

set search_path = "$user", public, postgis;

As well, I can permanently set the search_path for a given database with:

alter database mydb set search_path = "$user", public, postgis ;

And I can permanently set the search_path for a given role (user) with:

alter role johnny set search_path = "$user", public, postgis ;

But I would like to know how to determine what the database and role settings are (with respect to search_path) prior to altering them?

You can find configuration settings for roles and databases in the catalog table pg_db_role_setting.

This query retrieves any settings for a given role or database:

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
WHERE  r.rolname = 'myrole' OR d.datname = 'mydb';

If nothing is set, the next lower instance determines the default state of the search_path, which is postgresql.conf in this case or command-line options at server start. Related:

• How does the search_path influence identifier resolution and the “current schema”

To unset any settings of a role or database - the search_path in this particular example:

ALTER ROLE myrole RESET search_path;

Or:

ALTER DATABASE mydb RESET search_path;

Or:

ALTER ROLE myrole in DATABASE mydb RESET search_path;

Never manipulate data in the system catalog (pg_catalog.*) manually. Use DDL commands as instructed in the manual for ALTER ROLE and ALTER DATABASE.

Essentially, the RESET command deletes a row from pg_db_role_setting allowing the base setting to take effect again. I wouldn't call that convoluted.

StackExchange Database Administrators Q#56023, answer score: 82