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