Hi Gary,
p_runasid is a variable, not a role name, so you will need to run this as dynamic sql. That can be accomplished with the next line of code below. You may want to take a look at the manual for setting functions as “SECURITY DEFINER” (https://www.postgresql.org/docs/current/static/sql-createfunction.html ) and read up on SQL injection in general. The syntax here can be found on this page https://www.postgresql.org/docs/current/static/plpgsql-statements.html
execute format('set role= %I;', p_runas_id);
Dan
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Garry Chen
Sent: Tuesday, November 07, 2017 1:43 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Using set role inside a function question
Hi All,
I have a function pass in a user login id (p_runas_id) and the it will insert a row into a table after that set role to that pass in id. This function complied fine but getting ERROR: role "p_runas_id" does not exist CONTEXT: SQL statement "SET ROLE p_runas_id" when execute. Any suggestion?
Thank you very much,
Garry
CREATE OR REPLACE FUNCTION set_netid_context(p_runas_id varchar(30))
RETURNS void
AS $$
Declare
v_dm_name varchar(30);
v_runid varchar(30);
v_ora_id numeric;
BEGIN
INSERT INTO obiee_context_audit(context_date, ora_sid, db_user, runas_id)
VALUES (now(), pg_backend_pid(), SESSION_USER, p_runas_id);
SET ROLE p_runas_id;
END; $$
LANGUAGE plpgsql;
The contents of this e-mail message and
any attachments are intended solely for the
addressee(s) and may contain confidential
and/or legally privileged information. If you
are not the intended recipient of this message
or if this message has been addressed to you
in error, please immediately alert the sender
by reply e-mail and then delete this message
and any attachments. If you are not the
intended recipient, you are notified that
any use, dissemination, distribution, copying,
or storage of this message or any attachment
is strictly prohibited.