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