PostgreSQL ユーザ指定でサーチパスを変更する

-- 以下の意味は、ログインユーザと同じ名前のスキーマが存在しない限り、
-- publicスキーマへのアクセスになります。
myposdb=> show search_path;
   search_path
-----------------
 "$user", public
(1 行)

-- 以下でも確認可能です
--
myposdb=# select current_setting('search_path');
 current_setting
-----------------
 "$user", public
(1 行)

myposdb=#Copy

select * from ( select usename,unnest(useconfig) search_path from pg_user ) t 
where search_path like '%search%';Copy

postgres=# select * from ( select usename,unnest(useconfig) search_path from pg_user ) t
postgres-# where search_path like '%search%';
 usename |              search_path
---------+----------------------------------------
 udonman | search_path="$user", public, prjschema
 sooni   | search_path="$user", public, prjschema
(2 行)

postgres=#Copy

alter user udonman set search_path = "$user",relapi,public
;Copy

-- 現状の確認
postgres=# select * from ( select usename,unnest(useconfig) unnest from pg_user ) t where unnest like '%search%';
 usename |                unnest
---------+--------------------------------------
 sooni   | search_path=sooni, public, prjschema
 udonman | search_path="$user", public
(2 rows)


postgres=#
-- udonmanユーザのサーチパスを変更
postgres=# alter user udonman set search_path = "$user",relapi,public
postgres-# ;
ALTER ROLE

-- 変更された事を確認する
postgres=# select * from ( select usename,unnest(useconfig) unnest from pg_user ) t where unnest like '%search%';
 usename |                unnest
---------+--------------------------------------
 sooni   | search_path=sooni, public, prjschema
 udonman | search_path="$user", relapi, public
(2 rows)


postgres=#Copy