Paweł Domański
23 lines
788 B
SQL
23 lines
788 B
SQL
-- Find search_path of users in a particular database ( replace your db_name(EDB))
|
|
|
|
SELECT r.rolname, d.datname, drs.setconfig
|
|
FROM pg_db_role_setting drs
|
|
LEFT JOIN pg_roles r ON r.oid = drs.setrole
|
|
LEFT JOIN pg_database d ON d.oid = drs.setdatabase
|
|
WHERE d.datname = 'EDB';
|
|
|
|
-- Find search_path of users in postgres db cluster( all database)
|
|
|
|
SELECT r.rolname, d.datname, drs.setconfig
|
|
FROM pg_db_role_setting drs
|
|
LEFT JOIN pg_roles r ON r.oid = drs.setrole
|
|
LEFT JOIN pg_database d ON d.oid = drs.setdatabase;
|
|
|
|
|
|
-- set search_path for a user in particular db:
|
|
|
|
postgres# alter user prod_user in database "EDB" set search_path="$user", public, prim_db;
|
|
|
|
-- set search_path for a user in postgres cluster( all dbs)
|
|
|
|
postgres=# alter user prod_user set search_path="$user", public, prim_db; |