Thread: Viewing another role's search path?
Is there some simple way of viewing the search path (or other role-specific setting) for a role different to the current role? Apart from querying 'pg_db_role_setting' directly? Just wondering if I'm missing something obvious. Regards Ian Barwick
On Tue, Jul 23, 2013 at 9:15 PM, Ian Lawrence Barwick <barwick@gmail.com> wrote:
-- Is there some simple way of viewing the search path (or other
role-specific setting) for a role different to the current role? Apart from
querying 'pg_db_role_setting' directly?
This one perhaps?
select rolname, rolconfig from pg_roles;
rolconfig contains all the user-specific settings.
Michael
2013/7/23 Michael Paquier <michael.paquier@gmail.com>: > > On Tue, Jul 23, 2013 at 9:15 PM, Ian Lawrence Barwick <barwick@gmail.com> > wrote: >> >> Is there some simple way of viewing the search path (or other >> role-specific setting) for a role different to the current role? Apart >> from >> querying 'pg_db_role_setting' directly? > > This one perhaps? > select rolname, rolconfig from pg_roles; > rolconfig contains all the user-specific settings. Thanks, but what I'm looking for is a more elegant (built-in?) way of extracting the contents of the configuration array which is useable for people who don't want to poke around in the system catalogues. I've created a view which does this, but I want to confirm if there's anything along the lines of "SHOW search_path FOR ROLE foo" which I've missed and would save the trouble of maintaining the view. Regards Ian Barwick
On 07/23/2013 05:15 AM, Ian Lawrence Barwick wrote: > Is there some simple way of viewing the search path (or other > role-specific setting) for a role different to the current role? Apart from > querying 'pg_db_role_setting' directly? > > Just wondering if I'm missing something obvious. http://www.postgresql.org/docs/9.2/interactive/app-psql.html \drds [ role-pattern [ database-pattern ] ] Lists defined configuration settings. These settings can be role-specific, database-specific, or both. role-pattern and database-pattern are used to select specific roles and databases to list, respectively. If omitted, or if * is specified, all settings are listed, including those not role-specific or database-specific, respectively. The ALTER ROLE and ALTER DATABASE commands are used to define per-role and per-database configuration settings. > > Regards > > Ian Barwick > > -- Adrian Klaver adrian.klaver@gmail.com
2013/7/23 Adrian Klaver <adrian.klaver@gmail.com>: > On 07/23/2013 05:15 AM, Ian Lawrence Barwick wrote: >> >> Is there some simple way of viewing the search path (or other >> role-specific setting) for a role different to the current role? Apart >> from >> querying 'pg_db_role_setting' directly? >> >> Just wondering if I'm missing something obvious. > > > http://www.postgresql.org/docs/9.2/interactive/app-psql.html > > \drds [ role-pattern [ database-pattern ] ] > Lists defined configuration settings. These settings can be role-specific, > database-specific, or both. role-pattern and database-pattern are used to > select specific roles and databases to list, respectively. If omitted, or if > * is specified, all settings are listed, including those not role-specific > or database-specific, respectively. > > The ALTER ROLE and ALTER DATABASE commands are used to define per-role and > per-database configuration settings. Thanks, I missed that one. Unfortunately it produces a false negative if a user hasn't had their search path explicitly set, e.g.: postgres=# CREATE ROLE sp_test LOGIN; CREATE ROLE postgres=# \drds sp_test No matching settings found. postgres=# \c - sp_test You are now connected to database "postgres" as user "sp_test". postgres=> SHOW search_path ; search_path ---------------- "$user",public (1 row) postgres=> \c - postgres You are now connected to database "postgres" as user "postgres". postgres=# ALTER ROLE sp_test SET search_path ="$user",public, foo; ALTER ROLE postgres=# \drds sp_test List of settings Role | Database | Settings ---------+----------+----------------------------- sp_test | | search_path="$user", public, foo Also it's psql-specific, so doesn't really lend itself as a global solution. The custom view will have to do for now. Regards Ian Barwick