SET ROLE

Enables a role for the user's current session. The user can access privileges that have been granted to the role.

Note: If you set EnableAllRolesOnLogin=1, this eliminates the need for the user to run SET ROLE <rolenames> to enable any roles that have been granted to any user. For more information see Security Parameters.

Syntax

SET ROLE { role [,…] | NONE | ALL | ALL EXCEPT [,…] | DEFAULT }

Parameters

role [,…] | NONE | ALL | ALL EXCEPT [,…] | DEFAULT

The name of one or more roles to set as the current role, or one of the following keywords:

NONE disables all roles for the current user session.
ALL enables all of the roles to which the user has been granted access. Use GRANT (Role) to assign a role to a user.
ALL EXCEPT enable all the roles to which the user has access, with the exception of the role or roles indicated with this command.
DEFAULT sets the roles assigned to the user as the default roles.

Privileges

As a user, you can only set a role that has been granted to you. Use the SHOW AVAILABLE ROLES command to retrieve a list of the roles available to you.

Notes

The DBADMIN user creates default roles that the DBADMIN then grants to a user. The DBADMIN can also grant a default role to a user using ALTER USER.
Enabling a role does not affect any other roles that are currently enabled. A user session can have more than one role enabled at a time. The user's permissions are the union of all the roles that are currently active, plus any permissions granted directly to the user.

Examples

This example shows the following:

SHOW AVAILABLE_ROLES; lists the roles available to the user, but not enabled.
SET ROLE applogs; enables the applogs role for the user.
SHOW ENABLED_ROLES; lists the applogs role as enabled (SET) for the user.
SET ROLE appuser; enables the appuser role for the user.
SHOW ENABLED_ROLES now lists both applogs and appuser as enabled roles for the user.
SET ROLE NONE disables all the users' enabled roles .
SHOW ENABLED_ROLES shows that no roles are enabled for the user.

=> SHOW AVAILABLE_ROLES;
      name       |          setting
-----------------+----------------------------
 available roles | applogs, appadmin, appuser
(1 row)

=> SET ROLE applogs;
SET

=> SHOW ENABLED_ROLES;
     name      | setting
---------------+---------
 enabled roles | applogs
(1 row)

=> SET ROLE appuser;
SET

=> SHOW ENABLED_ROLES;
     name      |     setting
---------------+------------------
 enabled roles | applogs, appuser
(1 row)

=> SET ROLE NONE;SET


=> SHOW ENABLED_ROLES;
     name      | setting
---------------+---------
 enabled roles |
(1 row)

Set User Default Roles

Though the DBADMIN user is normally responsible for setting a user's default roles, as a user you can set your own role. For example, if you run SET ROLE NONE all of your enabled roles are disabled. Then it was determined you need access to role1 as a default role. The DBADMIN uses ALTER USER to assign you a default role:

=> ALTER USER user1 default role role1;

This example sets role1 as user1's default role because the DBADMIN assigned this default role using ALTER USER.

user1 => SET ROLE default;
user1 => SHOW ENABLED_ROLES;
    name      | setting
-----------------------
enabled roles |   role1
(1 row)

Set All Roles as Default

This example makes all roles granted to user1 default roles:

user1 => SET ROLE all; 
user1 => show enabled roles;
    name      |   setting
----------------------------------
enabled roles | role1, role2, role3
(1 row)

Set All Roles as Default With EXCEPT

This example makes all the roles granted to the user default roles with the exception of role1.

user1 => set role all except role1;
user1 => SHOW ENABLED_ROLES
     name     | setting
----------------------------
enabled roles | role2, role3
(1 row)