1SET ROLE(7) PostgreSQL 16.1 Documentation SET ROLE(7)
2
6 SET_ROLE - set the current user identifier of the current session
7
9 SET [ SESSION | LOCAL ] ROLE role_name
10 SET [ SESSION | LOCAL ] ROLE NONE
11 RESET ROLE
12
14 This command sets the current user identifier of the current SQL
15 session to be role_name. The role name can be written as either an
16 identifier or a string literal. After SET ROLE, permissions checking
17 for SQL commands is carried out as though the named role were the one
18 that had logged in originally.
19 The specified role_name must be a role that the current session user is
21 a member of. (If the session user is a superuser, any role can be
22 selected.)
23 The SESSION and LOCAL modifiers act the same as for the regular SET
25 command.
26 SET ROLE NONE sets the current user identifier to the current session
28 user identifier, as returned by session_user. RESET ROLE sets the
29 current user identifier to the connection-time setting specified by the
30 command-line options, ALTER ROLE, or ALTER DATABASE, if any such
31 settings exist. Otherwise, RESET ROLE sets the current user identifier
32 to the current session user identifier. These forms can be executed by
33 any user.
34
36 Using this command, it is possible to either add privileges or restrict
37 one's privileges. If the session user role has been granted memberships
38 WITH INHERIT TRUE, it automatically has all the privileges of every
39 such role. In this case, SET ROLE effectively drops all the privileges
40 except for those which the target role directly possesses or inherits.
41 On the other hand, if the session user role has been granted
42 memberships WITH INHERIT FALSE, the privileges of the granted roles
43 can't be accessed by default. However, if the role was granted WITH SET
44 TRUE, the session user can use SET ROLE to drop the privileges assigned
45 directly to the session user and instead acquire the privileges
46 available to the named role. If the role was granted WITH INHERIT
47 FALSE, SET FALSE then the privileges of that role cannot be exercised
48 either with or without SET ROLE.
49 Note that when a superuser chooses to SET ROLE to a non-superuser role,
51 they lose their superuser privileges.
52 SET ROLE has effects comparable to SET SESSION AUTHORIZATION, but the
54 privilege checks involved are quite different. Also, SET SESSION
55 AUTHORIZATION determines which roles are allowable for later SET ROLE
56 commands, whereas changing roles with SET ROLE does not change the set
57 of roles allowed to a later SET ROLE.
58 SET ROLE does not process session variables as specified by the role's
60 ALTER ROLE settings; this only happens during login.
61 SET ROLE cannot be used within a SECURITY DEFINER function.
63
65 SELECT SESSION_USER, CURRENT_USER;
66 session_user | current_user
68 --------------+--------------
69 peter | peter
70 SET ROLE 'paul';
72 SELECT SESSION_USER, CURRENT_USER;
74 session_user | current_user
76 --------------+--------------
77 peter | paul
78
80 PostgreSQL allows identifier syntax ("rolename"), while the SQL
81 standard requires the role name to be written as a string literal. SQL
82 does not allow this command during a transaction; PostgreSQL does not
83 make this restriction because there is no reason to. The SESSION and
84 LOCAL modifiers are a PostgreSQL extension, as is the RESET syntax.
85
87 SET SESSION AUTHORIZATION (SET_SESSION_AUTHORIZATION(7))
88PostgreSQL 16.1 2023 SET ROLE(7)