| <!-- |
| $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> |