Debian Codegraph @

doc/src/sgml/ref/set_role.sgml

raw
1 <!--
2 doc/src/sgml/ref/set_role.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-set-role">
7 <indexterm zone="sql-set-role">
8 <primary>SET ROLE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>SET ROLE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>SET ROLE</refname>
19 <refpurpose>set the current user identifier of the current session</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 SET [ SESSION | LOCAL ] ROLE <replaceable class="parameter">role_name</replaceable>
25 SET [ SESSION | LOCAL ] ROLE NONE
26 RESET ROLE
27 </synopsis>
28 </refsynopsisdiv>
29
30 <refsect1>
31 <title>Description</title>
32
33 <para>
34 This command sets the current user
35 identifier of the current SQL session to be <replaceable
36 class="parameter">role_name</replaceable>. The role name can be
37 written as either an identifier or a string literal.
38 After <command>SET ROLE</command>, permissions checking for SQL commands
39 is carried out as though the named role were the one that had logged
40 in originally. Note that <command>SET ROLE</command> and
41 <command>SET SESSION AUTHORIZATION</command> are exceptions; permissions
42 checks for those continue to use the current session user and the initial
43 session user (the <firstterm>authenticated user</firstterm>), respectively.
44 </para>
45
46 <para>
47 The current session user must have the <literal>SET</literal> option for the
48 specified <replaceable class="parameter">role_name</replaceable>, either
49 directly or indirectly via a chain of memberships with the
50 <literal>SET</literal> option.
51 (If the session user is a superuser, any role can be selected.)
52 </para>
53
54 <para>
55 The <literal>SESSION</literal> and <literal>LOCAL</literal> modifiers act the same
56 as for the regular <link linkend="sql-set"><command>SET</command></link>
57 command.
58 </para>
59
60 <para>
61 <literal>SET ROLE NONE</literal> sets the current user identifier to the
62 current session user identifier, as returned by
63 <function>session_user</function>. <literal>RESET ROLE</literal> sets the
64 current user identifier to the connection-time setting specified by the
65 <link linkend="libpq-connect-options">command-line options</link>,
66 <link linkend="sql-alterrole"><command>ALTER ROLE</command></link>, or
67 <link linkend="sql-alterdatabase"><command>ALTER DATABASE</command></link>,
68 if any such settings exist. Otherwise, <literal>RESET ROLE</literal> sets
69 the current user identifier to the current session user identifier. These
70 forms can be executed by any user.
71 </para>
72 </refsect1>
73
74 <refsect1>
75 <title>Notes</title>
76
77 <para>
78 Using this command, it is possible to either add privileges or restrict
79 one's privileges. If the session user role has been granted memberships
80 <literal>WITH INHERIT TRUE</literal>, it automatically has all the
81 privileges of every such role. In this case, <command>SET ROLE</command>
82 effectively drops all the privileges except for those which the target role
83 directly possesses or inherits. On the other hand, if the session user role
84 has been granted memberships <literal>WITH INHERIT FALSE</literal>, the
85 privileges of the granted roles can't be accessed by default. However, if
86 the role was granted <literal>WITH SET TRUE</literal>, the
87 session user can use <command>SET ROLE</command> to drop the privileges
88 assigned directly to the session user and instead acquire the privileges
89 available to the named role. If the role was granted <literal>WITH INHERIT
90 FALSE, SET FALSE</literal> then the privileges of that role cannot be
91 exercised either with or without <literal>SET ROLE</literal>.
92 </para>
93
94 <para>
95 <command>SET ROLE</command> has effects comparable to
96 <link linkend="sql-set-session-authorization"><command>SET SESSION AUTHORIZATION</command></link>, but the privilege
97 checks involved are quite different. Also,
98 <command>SET SESSION AUTHORIZATION</command> determines which roles are
99 allowable for later <command>SET ROLE</command> commands, whereas changing
100 roles with <command>SET ROLE</command> does not change the set of roles
101 allowed to a later <command>SET ROLE</command>.
102 </para>
103
104 <para>
105 <command>SET ROLE</command> does not process session variables as specified by
106 the role's <link linkend="sql-alterrole"><command>ALTER ROLE</command></link> settings; this only happens during
107 login.
108 </para>
109
110 <para>
111 <command>SET ROLE</command> cannot be used within a
112 <literal>SECURITY DEFINER</literal> function.
113 </para>
114 </refsect1>
115
116 <refsect1>
117 <title>Examples</title>
118
119 <programlisting>
120 SELECT SESSION_USER, CURRENT_USER;
121
122 session_user | current_user
123 --------------+--------------
124 peter | peter
125
126 SET ROLE 'paul';
127
128 SELECT SESSION_USER, CURRENT_USER;
129
130 session_user | current_user
131 --------------+--------------
132 peter | paul
133 </programlisting>
134 </refsect1>
135
136 <refsect1>
137 <title>Compatibility</title>
138
139 <para>
140 <productname>PostgreSQL</productname>
141 allows identifier syntax (<literal>"<replaceable>rolename</replaceable>"</literal>), while
142 the SQL standard requires the role name to be written as a string
143 literal. SQL does not allow this command during a transaction;
144 <productname>PostgreSQL</productname> does not make this
145 restriction because there is no reason to.
146 The <literal>SESSION</literal> and <literal>LOCAL</literal> modifiers are a
147 <productname>PostgreSQL</productname> extension, as is the
148 <literal>RESET</literal> syntax.
149 </para>
150 </refsect1>
151
152 <refsect1>
153 <title>See Also</title>
154
155 <simplelist type="inline">
156 <member><xref linkend="sql-set-session-authorization"/></member>
157 </simplelist>
158 </refsect1>
159 </refentry>