From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Boris Migo <migo(at)intrak(dot)sk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: is it possible to get current_user inside security definer function ? |
Date: | 2006-03-07 05:46:50 |
Message-ID: | 20060307054650.GA18942@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-03-07 05:58:29 | Re: is it possible to get current_user inside security definer function ? |
Previous Message | Phill Edwards | 2006-03-07 05:42:36 | JSP pages don't work with database after postgres downgrade |