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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
'\" t
.\" Title: SET ROLE
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2024
.\" Manual: PostgreSQL 16.2 Documentation
.\" Source: PostgreSQL 16.2
.\" Language: English
.\"
.TH "SET ROLE" "7" "2024" "PostgreSQL 16.2" "PostgreSQL 16.2 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
SET_ROLE \- set the current user identifier of the current session
.SH "SYNOPSIS"
.sp
.nf
SET [ SESSION | LOCAL ] ROLE \fIrole_name\fR
SET [ SESSION | LOCAL ] ROLE NONE
RESET ROLE
.fi
.SH "DESCRIPTION"
.PP
This command sets the current user identifier of the current SQL session to be
\fIrole_name\fR\&. The role name can be written as either an identifier or a string literal\&. After
\fBSET ROLE\fR, permissions checking for SQL commands is carried out as though the named role were the one that had logged in originally\&.
.PP
The specified
\fIrole_name\fR
must be a role that the current session user is a member of\&. (If the session user is a superuser, any role can be selected\&.)
.PP
The
SESSION
and
LOCAL
modifiers act the same as for the regular
\fBSET\fR
command\&.
.PP
SET ROLE NONE
sets the current user identifier to the current session user identifier, as returned by
\fBsession_user\fR\&.
RESET ROLE
sets the current user identifier to the connection\-time setting specified by the
command\-line options,
\fBALTER ROLE\fR, or
\fBALTER DATABASE\fR, if any such settings exist\&. Otherwise,
RESET ROLE
sets the current user identifier to the current session user identifier\&. These forms can be executed by any user\&.
.SH "NOTES"
.PP
Using this command, it is possible to either add privileges or restrict one\*(Aqs privileges\&. If the session user role has been granted memberships
WITH INHERIT TRUE, it automatically has all the privileges of every such role\&. In this case,
\fBSET ROLE\fR
effectively drops all the privileges except for those which the target role directly possesses or inherits\&. On the other hand, if the session user role has been granted memberships
WITH INHERIT FALSE, the privileges of the granted roles can\*(Aqt be accessed by default\&. However, if the role was granted
WITH SET TRUE, the session user can use
\fBSET ROLE\fR
to drop the privileges assigned directly to the session user and instead acquire the privileges available to the named role\&. If the role was granted
WITH INHERIT FALSE, SET FALSE
then the privileges of that role cannot be exercised either with or without
SET ROLE\&.
.PP
Note that when a superuser chooses to
\fBSET ROLE\fR
to a non\-superuser role, they lose their superuser privileges\&.
.PP
\fBSET ROLE\fR
has effects comparable to
\fBSET SESSION AUTHORIZATION\fR, but the privilege checks involved are quite different\&. Also,
\fBSET SESSION AUTHORIZATION\fR
determines which roles are allowable for later
\fBSET ROLE\fR
commands, whereas changing roles with
\fBSET ROLE\fR
does not change the set of roles allowed to a later
\fBSET ROLE\fR\&.
.PP
\fBSET ROLE\fR
does not process session variables as specified by the role\*(Aqs
\fBALTER ROLE\fR
settings; this only happens during login\&.
.PP
\fBSET ROLE\fR
cannot be used within a
SECURITY DEFINER
function\&.
.SH "EXAMPLES"
.sp
.if n \{\
.RS 4
.\}
.nf
SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
\-\-\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-\-\-
peter | peter
SET ROLE \*(Aqpaul\*(Aq;
SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
\-\-\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-\-\-
peter | paul
.fi
.if n \{\
.RE
.\}
.SH "COMPATIBILITY"
.PP
PostgreSQL
allows identifier syntax ("\fIrolename\fR"), while the SQL standard requires the role name to be written as a string literal\&. SQL does not allow this command during a transaction;
PostgreSQL
does not make this restriction because there is no reason to\&. The
SESSION
and
LOCAL
modifiers are a
PostgreSQL
extension, as is the
RESET
syntax\&.
.SH "SEE ALSO"
SET SESSION AUTHORIZATION (\fBSET_SESSION_AUTHORIZATION\fR(7))
|
