44

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?

36

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:

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.

  • Wow. I did not imagine it would be this convoluted. How would you unset a given database and role setting? After executing alter role myrole set search_path = "$user", public, postgis ; I noticed that pg_roles.rolconfig (corresponding to my role) got the value {"search_path=\"$user\", public, postgis"}. Also, select * from pg_db_role_setting ; now shows an additional row. And after executing alter database mydb set search_path = "$user", public, postgis ; I see a corresponding row in select * from pg_db_role_setting ; -- in the end, I am unsure of how to "undo" these changes. – user664833 Jan 3 '14 at 17:26
  • @user664833: I added instructions to unset. – Erwin Brandstetter Jan 3 '14 at 17:35
6

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

  • 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 it's set a non-default value 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.

  • Isn't boot_val actually the compiled-in factory default, not the setting in postgresql.conf? – Erwin Brandstetter Jul 30 '16 at 0:23
  • @Erwin: yes. One might want to look at reset_val instead of boot_val. – Daniel Vérité Aug 1 '16 at 11:15
  • Hmm, database or role settings overwrite the value in reset_val. I stumbled across this old question researching this recent one: dba.stackexchange.com/questions/145280/… – Erwin Brandstetter Aug 1 '16 at 12:35
  • @Erwin: ISTM that getting the value from postgresql.conf is likely to be an XY problem in most cases. Anyway, I edited the answer to link to the newer question and expand a bit. – Daniel Vérité Aug 1 '16 at 13:47
3
select * from pg_user;

True for postgres and Redshift. This seems too simple compared to the previous answers that depend on pg_db_role_setting, but the useconfig column will have a list of user configs including search_path, formatted as a list.

pg_user Postgres documentation is here

To be more selective:

rs.db.batarang.com cooldb:cooldude =#> select usename
                                              , useconfig 
                                       from   pg_user
                                       where  usename = 'cooldude';
┌────────────┬─────────────────────────────────────────────────────┐
│  usename   │                      useconfig                      │
├────────────┼─────────────────────────────────────────────────────┤
│ cooldude   │ {"search_path=dirt, test, \"$user\", public, prod"} │
└────────────┴─────────────────────────────────────────────────────┘

I think this user table contains all users in the cluster, not just specific db -- but I didn't verify that.

  • Role isn't exactly the same as user. ;) – Vic Aug 12 '18 at 19:56
  • Vic could you elaborate? The Postgres doc are succinct and seem to say that user and role are no longer distinct concepts, but I’m not a DBA and would love more input. postgresql.org/docs/current/static/user-manag.html – Merlin Aug 14 '18 at 17:18
  • 2
    Any 'role' can act as a user, a group, or both. But a user' is implied to have one other distinction. Reviewing the catalog we see that the views pg_role and pg_user both reference table pg_authid, but with the predicate rolcanlogin for users. 'Users' can login to your database and generally 'roles' define sets of authorization that users inherit. – Vic Aug 14 '18 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.