1<!--
2doc/src/sgml/ref/set_role.sgml
3PostgreSQL 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>
24SET [ SESSION | LOCAL ] ROLE <replaceable class="parameter">role_name</replaceable>
25SET [ SESSION | LOCAL ] ROLE NONE
26RESET 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.
41  </para>
42
43  <para>
44   The specified <replaceable class="parameter">role_name</replaceable>
45   must be a role that the current session user is a member of.
46   (If the session user is a superuser, any role can be selected.)
47  </para>
48
49  <para>
50   The <literal>SESSION</literal> and <literal>LOCAL</literal> modifiers act the same
51   as for the regular <xref linkend="sql-set"/>
52   command.
53  </para>
54
55  <para>
56   <literal>SET ROLE NONE</literal> sets the current user identifier to the
57   current session user identifier, as returned by
58   <function>session_user</function>.  <literal>RESET ROLE</literal> sets the
59   current user identifier to the connection-time setting specified by the
60   <link linkend="libpq-connect-options">command-line options</link>,
61   <link linkend="sql-alterrole"><command>ALTER ROLE</command></link>, or
62   <link linkend="sql-alterdatabase"><command>ALTER DATABASE</command></link>,
63   if any such settings exist.  Otherwise, <literal>RESET ROLE</literal> sets
64   the current user identifier to the current session user identifier.  These
65   forms can be executed by any user.
66  </para>
67 </refsect1>
68
69 <refsect1>
70  <title>Notes</title>
71
72  <para>
73   Using this command, it is possible to either add privileges or restrict
74   one's privileges.  If the session user role has the <literal>INHERIT</literal>
75   attribute, then it automatically has all the privileges of every role that
76   it could <command>SET ROLE</command> to; in this case <command>SET ROLE</command>
77   effectively drops all the privileges assigned directly to the session user
78   and to the other roles it is a member of, leaving only the privileges
79   available to the named role.  On the other hand, if the session user role
80   has the <literal>NOINHERIT</literal> attribute, <command>SET ROLE</command> drops the
81   privileges assigned directly to the session user and instead acquires the
82   privileges available to the named role.
83  </para>
84
85  <para>
86   In particular, when a superuser chooses to <command>SET ROLE</command> to a
87   non-superuser role, they lose their superuser privileges.
88  </para>
89
90  <para>
91   <command>SET ROLE</command> has effects comparable to
92   <xref linkend="sql-set-session-authorization"/>, but the privilege
93   checks involved are quite different.  Also,
94   <command>SET SESSION AUTHORIZATION</command> determines which roles are
95   allowable for later <command>SET ROLE</command> commands, whereas changing
96   roles with <command>SET ROLE</command> does not change the set of roles
97   allowed to a later <command>SET ROLE</command>.
98  </para>
99
100  <para>
101   <command>SET ROLE</command> does not process session variables as specified by
102   the role's <xref linkend="sql-alterrole"/> settings;  this only happens during
103   login.
104  </para>
105
106  <para>
107   <command>SET ROLE</command> cannot be used within a
108   <literal>SECURITY DEFINER</literal> function.
109  </para>
110 </refsect1>
111
112 <refsect1>
113  <title>Examples</title>
114
115<programlisting>
116SELECT SESSION_USER, CURRENT_USER;
117
118 session_user | current_user
119--------------+--------------
120 peter        | peter
121
122SET ROLE 'paul';
123
124SELECT SESSION_USER, CURRENT_USER;
125
126 session_user | current_user
127--------------+--------------
128 peter        | paul
129</programlisting>
130 </refsect1>
131
132 <refsect1>
133  <title>Compatibility</title>
134
135  <para>
136   <productname>PostgreSQL</productname>
137   allows identifier syntax (<literal>"<replaceable>rolename</replaceable>"</literal>), while
138   the SQL standard requires the role name to be written as a string
139   literal.  SQL does not allow this command during a transaction;
140   <productname>PostgreSQL</productname> does not make this
141   restriction because there is no reason to.
142   The <literal>SESSION</literal> and <literal>LOCAL</literal> modifiers are a
143   <productname>PostgreSQL</productname> extension, as is the
144   <literal>RESET</literal> syntax.
145  </para>
146 </refsect1>
147
148 <refsect1>
149  <title>See Also</title>
150
151  <simplelist type="inline">
152   <member><xref linkend="sql-set-session-authorization"/></member>
153  </simplelist>
154 </refsect1>
155</refentry>
156