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:
|
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)