set_role(5)

NAME

       SET  ROLE - set the current user identifier of the current
       session

SYNOPSIS

       SET [ SESSION | LOCAL ] ROLE rolename
       SET [ SESSION | LOCAL ] ROLE NONE
       RESET ROLE

DESCRIPTION

       This command sets the current user identifier of the  cur-
       rent  SQL  session  to  be  rolename. The role name may be
       written as either  an  identifier  or  a  string  literal.
       After  SET  ROLE, permissions checking for SQL commands is
       carried out as though the named role were the one that had
       logged in originally.

       The  specified  rolename  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.)

       The  SESSION  and  LOCAL modifiers act the same as for the
       regular SET [set(5)] command.

       The NONE and RESET forms reset the current user identifier
       to  be  the  current session user identifier.  These forms
       may be executed by any user.

NOTES

       Using this command, it is possible to  either  add  privi-
       leges  or  restrict  one's privileges. If the session user
       role has the INHERITS attribute, then it automatically has
       all  the  privileges  of every role that it could SET ROLE
       to; in this case SET ROLE effectively drops all the privi-
       leges  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 NOINHERITS attribute,  SET  ROLE
       drops the privileges assigned directly to the session user
       and instead acquires the privileges available to the named
       role.

       In  particular,  when a superuser chooses to SET ROLE to a
       non-superuser role, she loses her superuser privileges.

       SET ROLE has effects comparable to SET SESSION  AUTHORIZA-
       TION  [set_session_authorization(5)],  but  the  privilege
       checks involved are quite  different.  Also,  SET  SESSION
       AUTHORIZATION  determines  which  roles  are allowable for
       later SET ROLE commands, whereas changing roles  with  SET
       ROLE  does  not change the set of roles allowed to a later
       SET ROLE.

       SET ROLE cannot be used within a  SECURITY  DEFINER  func-
       tion.

EXAMPLES

       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

COMPATIBILITY

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

SEE ALSO

       SET SESSION AUTHORIZATION [set_session_authorization(5)]

SQL - Language Statements   2008-01-03                 SET ROLE()