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

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>

See also