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

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 <https://about.me/madankumark&gt;

*"There is no Elevator to Success. You have to take the Stairs"*

On Sun, Nov 25, 2018 at 2:24 AM raf <raf@raf.org> wrote:

Looked like a bug.