SET SESSION AUTHORIZATION

Description

Sets the session user identifier and the current user identifier of the current session to a specified user.

Precautions

The session identifier can be changed only when the initial session user has the system administrator permissions. Otherwise, the system supports the statement only when the authenticated username is specified.

Syntax

• Set the session user identifier and current user identifier for the current session.

  1	SET [ SESSION | LOCAL ] SESSION AUTHORIZATION role_name PASSWORD 'password';

• Reset the identifiers of the session and current users to the initially authenticated usernames.

  1 2	{SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT | RESET SESSION AUTHORIZATION};

Parameters

• SESSION

  Specifies that the specified parameters take effect for the current session.

• LOCAL

  Specifies that the specified statement takes effect only for the current transaction.

• role_name

  Specifies the username.

  Value range: a string. It must be an existing username in the database.

• password

  Specifies the password of a role. It must comply with the password convention.

  

  The restrictions on using a ciphertext password are as follows:

  • An administrator cannot use a ciphertext password to switch to another administrator but to a user with lower permissions.

  Ciphertext passwords are usually used in gs_dump and gs_dumpall export scenarios. In other scenarios, you are advised not to use ciphertext passwords directly.

• DEFAULT

  Resets the identifiers of the session and current users to the initially authenticated usernames.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- Query the session user and the current user.
gaussdb=# SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user 
--------------+--------------
 omm          | omm
(1 row)

-- Create a role paul.
gaussdb=# CREATE ROLE paul PASSWORD '********';

-- Set the current user and session user to paul, and query the session user and current user.
gaussdb=# SET SESSION AUTHORIZATION paul PASSWORD '********';
gaussdb=> SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user 
--------------+--------------
 paul         | paul
(1 row)

-- Reset the session user and the current user.
gaussdb=> RESET SESSION AUTHORIZATION;
gaussdb=# SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user 
--------------+--------------
 omm          | omm
(1 row)

-- Delete the user.
gaussdb=# DROP USER paul;

Helpful Links

SET ROLE