Qual é o search_path para um determinado banco de dados e usuário?

Você pode encontrar as definições de configuração para funções e bancos de dados na tabela de catálogo pg_db_role_setting.

Esta consulta recupera quaisquer configurações para uma determinada função ou banco de dados:

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';

Se nada for definido, a próxima instância inferior determinará o estado padrão do search_path, que é postgresql.confneste caso ou opções de linha de comando na inicialização do servidor. Relacionado:

• Como o search_path influencia a resolução do identificador e o “esquema atual”

Para anular quaisquer configurações de uma função ou banco de dados - search_pathneste exemplo específico:

ALTER ROLE myrole RESET search_path;

Ou:

ALTER DATABASE mydb RESET search_path;

Ou:

ALTER ROLE myrole in DATABASE mydb RESET search_path;

Nunca manipule dados no catálogo do sistema ( pg_catalog.*) manualmente. Use os comandos DDL conforme instruído no manual para ALTER ROLEe ALTER DATABASE.
Essencialmente, o RESETcomando exclui uma linha de pg_db_role_settingpermitir que a configuração básica entre em vigor novamente. Eu não chamaria isso de complicado.

As configurações permanentes para bancos de dados e funções são armazenadas na tabela de todo o cluster do sistema pg_db_role_settings .

Somente as configurações passadas para ALTER USERe ALTER DATABASEestão presentes nesta tabela. Para obter os valores configurados além desses comandos:

• O valor da configuração antes de qualquer alteração, inclusive no nível do cluster (por meio da configuração global postgresql.conf) pode ser consultado no banco de dados com:

   SELECT boot_val FROM pg_settings WHERE name='search_path';
  

• O valor da configuração anterior a qualquer alteração na sessão (através do SETcomando) pode ser consultado no banco de dados com:

   SELECT reset_val FROM pg_settings WHERE name='search_path';
  

• Quando é definido um valor não padrão em postgresql.conf, não é simples obter esse valor em SQL independentemente da sessão atual . pg_settings.boot_valnão funcionará porque ignora as alterações no arquivo de configuração e pg_settings.reset_valtambém não, porque é influenciado pelas configurações do banco de dados/usuário potencialmente definidas por meio do ALTER USER/ALTER DATABASE. A maneira mais simples para um DBA obter o valor é apenas procurá-lo no postgresql.conf. Caso contrário, consulte Redefinir search_path para o padrão global do cluster, que aborda esse tópico em detalhes.

select * from pg_user;

Verdadeiro para postgres e Redshift. Isso parece muito simples em comparação com as respostas anteriores que dependem de pg_db_role_setting, mas a useconfigcoluna terá uma lista de configurações de usuário incluindo search_path, formatada como uma lista.

A documentação do pg_user Postgres está aqui

Para ser mais seletivo:

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"} │
└────────────┴─────────────────────────────────────────────────────┘

Acho que essa tabela de usuários contém todos os usuários no cluster, não apenas o banco de dados específico - mas não verifiquei isso.