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