How to find invoking user in PL/vSQL procedure with definer security

Sergey_Cherepan_1

Hi,
It appears, if I am creating PL/vSQL procedure with definer security, call to current_user() return definer user.
How I can find what user is invoking procedure with definer security?
Thank you
Sergey

Bryan_H

According to documentation "A procedure with SECURITY DEFINER effectively executes the procedure as that user, so changes to the database appear to be performed by the procedure's definer rather than its caller."

You can pass the current user as an argument:

CREATE PROCEDURE who_are_you(caller VARCHAR(128)) SECURITY DEFINER AS $$
DECLARE
results VARCHAR(255);
BEGIN
results := EXECUTE 'SELECT CURRENT_USER;';
RAISE INFO 'current_user: %', results;
results := EXECUTE 'SELECT USER_NAME FROM CURRENT_SESSION;';
RAISE INFO 'current_user: %', results;
RAISE INFO 'actual user: %', caller;
END;
$$;

bryan=> call who_are_you(current_user);
INFO 2005: current_user: dbadmin
INFO 2005: current_user: dbadmin
INFO 2005: actual user: bryan

Sergey_Cherepan_1

This is a creative solution to problem, but it does work.
Thanks!