Re: is it possible to get current_user inside security definer function ?

On Mon, Mar 06, 2006 at 06:27:06PM +0100, Boris Migo wrote:
> Is there any way to get the name of the current user inside a
> plpgsql function that is defined with security definer?
>
> I know that this question was discused earlier, and session_user
> should be the answer, but user's curren_user doesn't have to be the
> same as session_user before function call, because of 'set role'.

Is this what you're after?  I don't know if there's another way.

\c test user1

CREATE FUNCTION whoami(OUT curr_user text, OUT sess_user text, OUT role_user text)
AS $$
BEGIN
    curr_user := current_user;
    sess_user := session_user;
    role_user := current_setting('role');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

\c test user2

SELECT current_user, session_user, current_setting('role');
 current_user | session_user | current_setting
--------------+--------------+-----------------
 user2        | user2        | none
(1 row)

SET ROLE user3;

SELECT current_user, session_user, current_setting('role');
 current_user | session_user | current_setting
--------------+--------------+-----------------
 user3        | user2        | user3
(1 row)

SELECT * FROM whoami();
 curr_user | sess_user | role_user
-----------+-----------+-----------
 user1     | user2     | user3
(1 row)

--
Michael Fuhr