Switches the current user identifier of the SQL session to a specified role.
Syntax
SET ROLE { rolename | NONE }Prerequisites
The role specified in rolename must already be granted to the current session user.
Description
SET ROLE sets the current user identifier of the SQL session context to rolename. After the command runs, privilege checks on SQL commands are performed as if the specified role is the active user.
Use SET ROLE NONE to revert to the original session user identity.
Behavior
The effect of SET ROLE on privileges depends on the session user's role attributes:
| Session user attribute | Effect of SET ROLE |
|---|---|
INHERITS | Drops all privileges of the session user and any other roles they belong to. Only the privileges of the specified role are retained. |
NOINHERITS | Drops the privileges of the session user. Retains the privileges of the specified role. |
If a superuser runs SET ROLE to assume a non-superuser role, the superuser no longer has superuser privileges.Examples
Switch to a different role
User mary takes on the identity of role admins:
SET ROLE admins;Revert to the original identity
User mary reverts to their own identity:
SET ROLE NONE;