SET ROLE

Enables a role for the user's current session. The user can access privileges that have been granted to the role. Enabling a role has no effect on roles that are currently enabled.

Use SHOW AVAILABLE ROLES to list granted roles.

Syntax

SET ROLE roles‑expression

Parameters

roles‑expression

Specifies what roles are the default roles for this user, with one of the following expressions:

  • NONE (default): Disables all roles.
  • roles‑list: A comma-delimited list of roles to enable. You can only set roles that are currently granted to you.
  • ALL [EXCEPT roles‑list]: Enables all roles currently granted to this user, excluding any comma-delimited roles specified in the optional EXCEPT clause.
  • DEFAULT: Enables all default roles of the current user, as set by ALTER USER...DEFAULT ROLE.

Privileges

None

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)