What is the search_path for a given database and user?

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.

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 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.

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.