doc/src/sgml/ref/set_role.sgml

<!--
$PostgreSQL: pgsql/doc/src/sgml/ref/set_role.sgml,v 1.3 2006/09/16 00:30:20 momjian Exp $
PostgreSQL documentation
-->

<refentry id="SQL-SET-ROLE">
 <refmeta>
  <refentrytitle id="sql-set-role-title">SET ROLE</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>SET ROLE</refname>
  <refpurpose>set the current user identifier of the current session</refpurpose>
 </refnamediv>

 <indexterm zone="sql-set-role">
  <primary>SET ROLE</primary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
SET [ SESSION | LOCAL ] ROLE <replaceable class="parameter">rolename</replaceable>
SET [ SESSION | LOCAL ] ROLE NONE
RESET ROLE
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   This command sets the current user
   identifier of the current SQL session to be <replaceable
   class="parameter">rolename</replaceable>.  The role name may be
   written as either an identifier or a string literal.
   After <command>SET ROLE</>, permissions checking for SQL commands
   is carried out as though the named role were the one that had logged
   in originally.
  </para>

  <para>
   The specified <replaceable class="parameter">rolename</replaceable>
   must be a role that the current session user is a member of.
   (If the session user is a superuser, any role can be selected.)
  </para>

  <para>
   The <literal>SESSION</> and <literal>LOCAL</> modifiers act the same
   as for the regular <xref linkend="SQL-SET" endterm="SQL-SET-title">
   command.
  </para>

  <para>
   The <literal>NONE</> and <literal>RESET</> forms reset the current
   user identifier to be the current session user identifier.
   These forms may be executed by any user.
  </para>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   Using this command, it is possible to either add privileges or restrict
   one's privileges.  If the session user role has the <literal>INHERITS</>
   attribute, then it automatically has all the privileges of every role that
   it could <command>SET ROLE</> to; in this case <command>SET ROLE</>
   effectively drops all the privileges assigned directly to the session user
   and to the other roles it is a member of, leaving only the privileges
   available to the named role.  On the other hand, if the session user role
   has the <literal>NOINHERITS</> attribute, <command>SET ROLE</> drops the
   privileges assigned directly to the session user and instead acquires the
   privileges available to the named role.
  </para>

  <para>
   In particular, when a superuser chooses to <command>SET ROLE</> to a
   non-superuser role, she loses her superuser privileges.
  </para>

  <para>
   <command>SET ROLE</> has effects comparable to
   <xref linkend="sql-set-session-authorization"
   endterm="sql-set-session-authorization-title">, but the privilege
   checks involved are quite different.  Also,
   <command>SET SESSION AUTHORIZATION</> determines which roles are
   allowable for later <command>SET ROLE</> commands, whereas changing
   roles with <command>SET ROLE</> does not change the set of roles
   allowed to a later <command>SET ROLE</>.
  </para>

  <para>
   <command>SET ROLE</> cannot be used within a
   <literal>SECURITY DEFINER</> function.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

<programlisting>
SELECT SESSION_USER, CURRENT_USER;

 session_user | current_user 
--------------+--------------
 peter        | peter

SET ROLE 'paul';

SELECT SESSION_USER, CURRENT_USER;

 session_user | current_user 
--------------+--------------
 peter        | paul
</programlisting>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <productname>PostgreSQL</productname>
   allows identifier syntax (<literal>"rolename"</literal>), while
   the SQL standard requires the role name to be written as a string
   literal.  SQL does not allow this command during a transaction;
   <productname>PostgreSQL</productname> does not make this
   restriction because there is no reason to.
   The <literal>SESSION</> and <literal>LOCAL</> modifiers are a
   <productname>PostgreSQL</productname> extension, as is the
   <literal>RESET</> syntax.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-set-session-authorization" endterm="sql-set-session-authorization-title"></member>
  </simplelist>
 </refsect1>
</refentry>