SET ROLE

Synopsis

Use the SET ROLE statement to set the current user of the current session to be the specified user.

set_role ::= SET [ SESSION | LOCAL ] ROLE { role_name | NONE }

reset_role ::= RESET ROLE

The specified role_name must be a role that the current session user is a member of. Superusers can set to any role.

After the role is set to role_name, any further SQL commands will use the privileges available to that role.

To reset the role back to current user, RESET ROLE or SET ROLE NONE can be used.

Change to new role John.

SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 yugabyte     | yugabyte
(1 row)

SET ROLE john;

SET

SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 yugabyte     | john
(1 row)

Changing to new role assumes the privileges available to that role.

SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 yugabyte     | yugabyte
(1 row)

CREATE DATABASE db1;

CREATE DATABASE

SET ROLE john;

SET

SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 yugabyte     | john
(1 row)

CREATE DATABASE db2;

ERROR:  permission denied to create database