doc/src/sgml/ref/set_role.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-SET-ROLE">
   7  <refmeta>
   8   <refentrytitle id="sql-set-role-title">SET ROLE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>SET ROLE</refname>
  15   <refpurpose>set the current user identifier of the current session</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-set-role">
  19   <primary>SET ROLE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 SET [ SESSION | LOCAL ] ROLE <replaceable class="parameter">rolename</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">rolename</replaceable>.  The role name can be
  37    written as either an identifier or a string literal.
  38    After <command>SET ROLE</>, 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">rolename</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</> and <literal>LOCAL</> modifiers act the same
  51    as for the regular <xref linkend="SQL-SET" endterm="SQL-SET-title">
  52    command.
  53   </para>
  54
  55   <para>
  56    The <literal>NONE</> and <literal>RESET</> forms reset the current
  57    user identifier to be the current session user identifier.
  58    These forms can be executed by any user.
  59   </para>
  60  </refsect1>
  61
  62  <refsect1>
  63   <title>Notes</title>
  64
  65   <para>
  66    Using this command, it is possible to either add privileges or restrict
  67    one's privileges.  If the session user role has the <literal>INHERITS</>
  68    attribute, then it automatically has all the privileges of every role that
  69    it could <command>SET ROLE</> to; in this case <command>SET ROLE</>
  70    effectively drops all the privileges assigned directly to the session user
  71    and to the other roles it is a member of, leaving only the privileges
  72    available to the named role.  On the other hand, if the session user role
  73    has the <literal>NOINHERITS</> attribute, <command>SET ROLE</> drops the
  74    privileges assigned directly to the session user and instead acquires the
  75    privileges available to the named role.
  76   </para>
  77
  78   <para>
  79    In particular, when a superuser chooses to <command>SET ROLE</> to a
  80    non-superuser role, she loses her superuser privileges.
  81   </para>
  82
  83   <para>
  84    <command>SET ROLE</> has effects comparable to
  85    <xref linkend="sql-set-session-authorization"
  86    endterm="sql-set-session-authorization-title">, but the privilege
  87    checks involved are quite different.  Also,
  88    <command>SET SESSION AUTHORIZATION</> determines which roles are
  89    allowable for later <command>SET ROLE</> commands, whereas changing
  90    roles with <command>SET ROLE</> does not change the set of roles
  91    allowed to a later <command>SET ROLE</>.
  92   </para>
  93
  94   <para>
  95    <command>SET ROLE</> does not process session variables as specified by
  96    the role's <xref linkend="sql-alterrole"
  97    endterm="sql-alterrole-title"> settings;  this only happens during
  98    login.
  99   </para>
 100
 101   <para>
 102    <command>SET ROLE</> cannot be used within a
 103    <literal>SECURITY DEFINER</> function.
 104   </para>
 105  </refsect1>
 106
 107  <refsect1>
 108   <title>Examples</title>
 109
 110 <programlisting>
 111 SELECT SESSION_USER, CURRENT_USER;
 112
 113  session_user | current_user
 114 --------------+--------------
 115  peter        | peter
 116
 117 SET ROLE 'paul';
 118
 119 SELECT SESSION_USER, CURRENT_USER;
 120
 121  session_user | current_user
 122 --------------+--------------
 123  peter        | paul
 124 </programlisting>
 125  </refsect1>
 126
 127  <refsect1>
 128   <title>Compatibility</title>
 129
 130   <para>
 131    <productname>PostgreSQL</productname>
 132    allows identifier syntax (<literal>"rolename"</literal>), while
 133    the SQL standard requires the role name to be written as a string
 134    literal.  SQL does not allow this command during a transaction;
 135    <productname>PostgreSQL</productname> does not make this
 136    restriction because there is no reason to.
 137    The <literal>SESSION</> and <literal>LOCAL</> modifiers are a
 138    <productname>PostgreSQL</productname> extension, as is the
 139    <literal>RESET</> syntax.
 140   </para>
 141  </refsect1>
 142
 143  <refsect1>
 144   <title>See Also</title>
 145
 146   <simplelist type="inline">
 147    <member><xref linkend="sql-set-session-authorization" endterm="sql-set-session-authorization-title"></member>
 148   </simplelist>
 149  </refsect1>
 150 </refentry>