Обсуждение: Invoking user of the function with SECURITY DEFINER

Madan Kumar wrote:
> How to get the user who is invoking the function with SECURITY DEFINER? 
> When we define the function to be SECURITY DEFINER, it will execute in the
> context of the user who created it. Let's say I've given execute permission
> for this function to other users and wish to know who is executing it.
> Is there a way to find that out?
> I tried CURRENT_USER and SESSION_USER but they return the function owner
> since they execute in that context. So is there any way to figure out the
> user who is invoking the function?

It works for me:

As user "postgres":

CREATE OR REPLACE FUNCTION tellme() RETURNS text LANGUAGE plpgsql
   SECURITY DEFINER AS 'BEGIN RETURN session_user; END;';

As user "laurenz":

SELECT tellme();
 tellme  
---------
 laurenz
(1 row)

Yours,
Laurenz Albe
-- 
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com

Laurenz Albe wrote:

> Madan Kumar wrote:
> > How to get the user who is invoking the function with SECURITY DEFINER? 
> > When we define the function to be SECURITY DEFINER, it will execute in the
> > context of the user who created it. Let's say I've given execute permission
> > for this function to other users and wish to know who is executing it.
> > Is there a way to find that out?
> > I tried CURRENT_USER and SESSION_USER but they return the function owner
> > since they execute in that context. So is there any way to figure out the
> > user who is invoking the function?
> 
> It works for me:
> 
> As user "postgres":
> 
> CREATE OR REPLACE FUNCTION tellme() RETURNS text LANGUAGE plpgsql
>    SECURITY DEFINER AS 'BEGIN RETURN session_user; END;';
> 
> As user "laurenz":
> 
> SELECT tellme();
>  tellme  
> ---------
>  laurenz
> (1 row)
> 
> Yours,
> Laurenz Albe

session_user has always worked for me.

cheers,
raf

Looked like a bug.

> 25 нояб. 2018 г., в 14:50, Madan Kumar <madankumar1993@gmail.com> написал(а):
>
> Got it..
> In my case i was getting session_user in declare section and trying to validate later which always resulted in the
functionowner. 
>  DECLARE
>     user text := SESSION_USER;
>
> So using it within the BEGIN; ...; END; clause worked for me.
> Thanks.
>
>
> Warm Regards,
> Madan Kumar K
>