Viewing another role's search path?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general