Invoking user of the function with SECURITY DEFINER

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Invoking user of the function with SECURITY DEFINER

Madan Kumar
Hi,

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?
 
Warm Regards,
 
"There is no Elevator to Success. You have to take the Stairs"
Reply | Threaded
Open this post in threaded view
|

Re: Invoking user of the function with SECURITY DEFINER

Laurenz Albe
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


Reply | Threaded
Open this post in threaded view
|

Re: Invoking user of the function with SECURITY DEFINER

raf-6
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


Reply | Threaded
Open this post in threaded view
|

Re: Invoking user of the function with SECURITY DEFINER

Madan Kumar
Got it..
In my case i was getting session_user in declare section and trying to validate later which always resulted in the function owner.
 DECLARE
user text := SESSION_USER;

So using it within the BEGIN; ...; END; clause worked for me.
Thanks.


Warm Regards,
 
"There is no Elevator to Success. You have to take the Stairs"


On Sun, Nov 25, 2018 at 2:24 AM raf <[hidden email]> wrote:
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


Reply | Threaded
Open this post in threaded view
|

Re: Invoking user of the function with SECURITY DEFINER

Олег Самойлов
Looked like a bug.

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