SHOW DEFAULT SESSION VARIABLES FOR ROLE

The SHOW DEFAULT SESSION VARIABLES FOR ROLE lists the values for updated that are applied to a given . The results returned only include the values of session variables that are changed from the defaults. When no session variables have been changed from the defaults for a given role, the statement returns no values.

Synopsis

show_default_session_variables_for_role syntax diagram

Parameters

Parameter	Description
`FOR {role\_or\_group\_or\_user}`	The whose updated should be displayed.
`FOR ROLE ALL`	Denotes that changes to default across all should be displayed.
`FOR USER ALL`	Alias for `FOR ROLE ALL`.

Response

Column	Description
`session\_variables`	The name of the that has had its default value changed.
`default\_values`	The updated value of the .
`database`	The where the change to the session variable will be applied.
`inherited\_globally`	Whether the change to the variable’s value is applied to all users. For more information, see .

Required Privileges

The issuing this statement must have at least one of the following :

CREATEROLE
MODIFYCLUSTERSETTING
MODIFYSQLCLUSTERSETTING

Examples

Output when no session variables have been changed

When no session variables have been changed from the defaults for a given role, the statement returns no values:

SHOW DEFAULT SESSION VARIABLES FOR ROLE public;

SHOW DEFAULT SESSION VARIABLES FOR ROLE 0

Another way of confirming zero rows of output:

SELECT * FROM [SHOW DEFAULT SESSION VARIABLES FOR ROLE public];

  session_variables | default_values | database | inherited_globally
--------------------+----------------+----------+---------------------
(0 rows)

Show changed session variables that apply to a user

CREATE USER movr_auditor;

ALTER ROLE ALL SET application_name = 'movr';

SHOW DEFAULT SESSION VARIABLES FOR ROLE movr_auditor;

  session_variables | default_values | database | inherited_globally
--------------------+----------------+----------+---------------------
  application_name  | movr           | NULL     |         t
(1 row)

Show changed session variables that apply to a user in different databases

CREATE DATABASE movr_audit;

ALTER ROLE ALL IN DATABASE movr_audit SET application_name = 'movr_audit';

SHOW DEFAULT SESSION VARIABLES FOR ROLE movr_auditor;

  session_variables | default_values |  database  | inherited_globally
--------------------+----------------+------------+---------------------
  application_name  | movr_audit     | movr_audit |         t
  application_name  | movr           | NULL       |         t
(2 rows)

Show updated default session variables that apply to all users

SHOW DEFAULT SESSION VARIABLES FOR ROLE ALL;

  session_variables | default_values |  database
--------------------+----------------+-------------
  application_name  | movr_audit     | movr_audit
  application_name  | movr           | NULL
(2 rows)

Get inline help in the SQL shell

\h SHOW DEFAULT SESSION VARIABLES FOR ROLE

Command:     SHOW DEFAULT SESSION VARIABLES FOR ROLE
Description: list default session variables for role
Category:    privileges and security
Syntax:
SHOW DEFAULT SESSION VARIABLES FOR ROLE <name>

​Synopsis

​Parameters

​Response

​Required Privileges

​Examples

​Output when no session variables have been changed

​Show changed session variables that apply to a user

​Show changed session variables that apply to a user in different databases

​Show updated default session variables that apply to all users

​Get inline help in the SQL shell

​See also