Jaka jest ścieżka wyszukiwania dla danej bazy danych i użytkownika?

Widzę bieżący search_pathz:

show search_path ;

I mogę ustawić search_pathdla bieżącej sesji za pomocą:

set search_path = "$user", public, postgis;

Ponadto mogę na stałe ustawić search_pathdla danej bazy danych za pomocą:

alter database mydb set search_path = "$user", public, postgis ;

I mogę na stałe ustawić search_pathdla danej roli (użytkownika) za pomocą:

alter role johnny set search_path = "$user", public, postgis ;

Chciałbym jednak wiedzieć, jak określić (w odniesieniu do ) ustawienia bazy danych i rolisearch_path przed ich zmianą?

Ustawienia konfiguracji ról i baz danych można znaleźć w tabeli katalogu pg_db_role_setting.

To zapytanie pobiera wszelkie ustawienia dla danej roli lub bazy danych:

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

Jeśli nic nie jest ustawione, kolejna niższa instancja określa domyślny stan search_path, czyli postgresql.confw tym przypadku lub opcje wiersza polecenia na starcie serwera. Związane z:

• W jaki sposób ścieżka szukania wpływa na rozdzielczość identyfikatora i „bieżący schemat”

Aby usunąć wszelkie ustawienia roli lub bazy danych - search_pathw tym konkretnym przykładzie:

ALTER ROLE myrole RESET search_path;

Lub:

ALTER DATABASE mydb RESET search_path;

Lub:

ALTER ROLE myrole in DATABASE mydb RESET search_path;

Nigdy nie manipuluj danymi w katalogu systemowym ( pg_catalog.*) ręcznie. Użyj poleceń DDL zgodnie z instrukcją dla ALTER ROLEi ALTER DATABASE.
Zasadniczo RESETpolecenie usuwa wiersz, pg_db_role_settingpozwalając, aby ustawienie podstawowe zaczęło obowiązywać ponownie. Nie nazwałbym tego tak skomplikowanym.

Stałe ustawienia zarówno baz danych, jak i ról są przechowywane w tabeli dla całego klastra systemu pg_db_role_settings .

Obecne są tylko zmienione ustawienia. Jeśli ścieżka wyszukiwania nigdy nie była modyfikowana dla bazy danych lub roli, to chyba można to założyć "$user",public.

• Wartość ustawienia przed każdą zmianą, w tym na poziomie klastra (poprzez konfigurację globalną postgresql.conf), można uzyskać w bazie danych za pomocą:

   SELECT boot_val FROM pg_settings WHERE name='search_path';

• Wartość ustawienia przed każdą zmianą w sesji (za pomocą SETpolecenia) można uzyskać z bazy danych za pomocą:

   SELECT reset_val FROM pg_settings WHERE name='search_path';

• Gdy zostanie ustawiona wartość inna niż domyślna postgresql.conf, uzyskanie tej wartości w SQL niezależnie od bieżącej sesji nie jest proste . pg_settings.boot_valnie zrobi tego, ponieważ ignoruje zmiany w pliku konfiguracyjnym, i też pg_settings.reset_valtego nie zrobi, ponieważ wpływają na to ustawienia bazy danych / użytkownika, które mogą zostać wprowadzone ALTER USER/ALTER DATABASE. Najprostszym sposobem uzyskania wartości przez DBA jest po prostu jej sprawdzenie postgresql.conf. W przeciwnym razie zobacz Resetowanie ścieżki wyszukiwania do globalnej wartości domyślnej klastra, która szczegółowo omawia ten temat.

select * from pg_user;

Prawda dla postgres i Redshift. To wydaje się zbyt proste w porównaniu z poprzednimi odpowiedziami, które zależą pg_db_role_setting, ale useconfigkolumna będzie miała listę konfiguracji użytkownika, w tym search_pathsformatowanych jako lista.

pg_user Dokumentacja Postgres jest tutaj

Aby być bardziej selektywnym:

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

Myślę, że ta tabela użytkowników zawiera wszystkich użytkowników w klastrze, a nie tylko określoną bazę danych - ale tego nie zweryfikowałem.