What is the postgres set 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?
the postgres set search path for a given database and user is -
- The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.
- Only settings passed to ALTER USER and ALTER DATABASE are present in this table. To get at the values that are configured aside from these commands:
- The value of the setting prior to any change, including at the cluster level (through the global configuration postgresql.conf) can be queried from the database with:
SELECT boot_val FROM pg_settings WHERE name='search_path';
The value of the setting prior to any change within the session (through the SET command) can be queried from the database with:
SELECT reset_val FROM pg_settings WHERE name='search_path';When a non-default value is set in postgresql.conf, it's not straightforward to obtain that value in SQL independently of the current session. pg_settings.boot_val won't do because it ignores changes in the configuration file, and pg_settings.reset_val won't either, because it's influenced by the database/user settings potentially set through ALTER USER/ALTER DATABASE. The simplest way for a DBA to get the value is to just look it up in postgresql.conf. Otherwise, see Reset search_path to the global cluster default which covers this topic in detail.