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?
Best Answer
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:
If nothing is set, the next lower instance determines the default state of the
search_path
, which ispostgresql.conf
in this case or command-line options at server start. Related:To unset any settings of a role or database - the
search_path
in this particular example:Or:
Or:
Never manipulate data in the system catalog (
pg_catalog.*
) manually. Use DDL commands as instructed in the manual forALTER ROLE
andALTER DATABASE
.Essentially, the
RESET
command deletes a row frompg_db_role_setting
allowing the base setting to take effect again. I wouldn't call that convoluted.