SET ROLE

Sets the current role identifier for the current session.

Summary

SET [SESSION | LOCAL] ROLE rolename

SET [SESSION | LOCAL] ROLE NONE

RESET ROLE

describe

This command sets the current role identifier for the current SQL session context to rolename. Role names can be written as identifiers or string literals. After SET ROLE, permission checks for SQL commands will be performed as if the new role was the initial logged in.

The specified rolename must be the role to which the current session user belongs. If the session user is a superuser, you can choose any role.

The NONE and RESET forms reset the current role identifier to the current session role identifier. These forms can be executed by any user.

Parameters

SESSION

Specify that this command takes effect on the current session. This is the default value.

LOCAL

Specifies that this command is only effective for the current transaction. After COMMIT or ROLLBACK, the session-level settings will take effect again. Note that if SET LOCAL is executed outside the transaction, it seems to be invalid.

rolename

The name of the role used for permission checking in this session.

NONE

RESET

Reset the current role identifier to the current session role identifier (the identifier of the role used for login).

Note

Use this command to add or restrict privileges. If the session user role has an INHERITS attribute, it will automatically have all privileges for each role that can SET ROLE; in this case, SET ROLE effectively abandons all privileges assigned directly to other roles that are assigned to the session user and its members, only the privileges available to the named roles. On the other hand, if the session user role has the NOINHERITS attribute, SET ROLE deletes the privileges assigned directly to the session user, but instead obtains the privileges that can be used to naming the role.

In particular, when the superuser chooses to set SET ROLE as a non-superuser role, she loses her superuser privileges.

SET ROLE has the same effect as SET SESSION AUTHORIZATION, but the privilege checks involved are very different. Similarly, SET SESSION AUTHORIZATION determines which roles are allowed by the subsequent SET ROLE command, and using SET ROLE does not change the set of roles that can be changed by subsequent SET ROLE.

SET ROLE does not handle session variables specified by the role's ALTER ROLE settings. Session variables are processed only during login.

Example

SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
----------------------------------------------------------------------------------------------------------------------------------
peter | peter

SET ROLE 'paul';

SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
----------------------------------------------------------------------------------------------------------------------------------
peter | paul

compatibility

YMatrix databases allow the use of identifier syntax (rolename), while SQL standards require role names to be written as string literals. SQL does not allow this command during a transaction. The YMatrix database does not have this restriction. SESSION and LOCAL modifiers are extensions to YMatrix databases, as are the RESET syntax.