SET ROLE

Description

Sets the current user identifier for the current session.

Precautions

• Users of the current session must be the role members specified by rolename, but system administrators can choose any roles.
• This statement may add permissions to or restrict permissions of a user. If the role of a session user has the INHERITS attribute, then the user is automatically granted all permissions of the roles that SET ROLE enables the user to be. In this case, SET ROLE deletes all permissions directly granted to the session user, as well as the permissions of the role it belongs to, that is, the user is left with the permissions of only the specified role. On the other hand, if the role of a session user has the NOINHERITS attribute, SET ROLE deletes permissions directly granted to the session user and obtains permissions of the specified role.

Syntax

• Set the current user identifier of the current session.

  SET [ SESSION | LOCAL ] ROLE role_name PASSWORD 'password';

• Reset the current user identifier to that of the current session.

  SET ROLE = DEFAULT;

Parameters

• SESSION

  Specifies that the command takes effect only for the current session. This parameter is used by default.

• LOCAL

  Specifies that the command takes effect only for the current transaction.

• role_name

  Value range: an existing role name. For details, see •role_name.

• password

  Specifies the password for the role. It must comply with the password convention. Encrypted passwords are not supported.

Examples

-- Create a role named paul.
m_db=# CREATE ROLE paul IDENTIFIED BY '********';

-- Set the current user to paul.
m_db=# SET ROLE paul PASSWORD '********';

Query the current user.
m_db=# SELECT CURRENT_USER;

-- Reset the current user.
m_db=# SET role = DEFAULT;

-- Drop the user.
m_db=# DROP USER paul;