On this page 
The SHOW DEFAULT SESSION VARIABLES FOR ROLE statement lists the values for updated session variables that are applied to a given user or role.
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 user, group, or role whose updated session variables should be displayed. |
FOR ROLE ALL |
Denotes that changes to default session variables across all roles should be displayed. |
FOR USER ALL |
Alias for FOR ROLE ALL. |
Response
| Column | Description |
|---|---|
session_variables |
The name of the session variable that has had its default value changed. |
default_values |
The updated value of the session variable. |
database |
The database 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 ALTER ROLE ALL. |
Required Privileges
The user issuing this statement must have at least one of the following privileges:
CREATEROLEMODIFYCLUSTERSETTINGMODIFYSQLCLUSTERSETTING
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
Was this helpful?
