Re: Viewing another role's search path?

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