Thread: 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

Michael Fuhr <mike@fuhr.org> writes:
> Is this what you're after?  I don't know if there's another way.

>     role_user := current_setting('role');

We currently define the spec's CURRENT_ROLE as equivalent to
CURRENT_USER, but I wonder if it shouldn't do what Boris is after.

            regards, tom lane

On Tue, Mar 07, 2006 at 12:58:29AM -0500, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Is this what you're after?  I don't know if there's another way.
>
> >     role_user := current_setting('role');
>
> We currently define the spec's CURRENT_ROLE as equivalent to
> CURRENT_USER, but I wonder if it shouldn't do what Boris is after.

I wondered why there wasn't a SOME_THING that showed that.  Does
the spec say anything about it?  I just did a quick search and
didn't see anything but I might easily have overlooked it.

--
Michael Fuhr