Re: Using set role inside a function question

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(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Garry Chen
Sent: Tuesday, November 07, 2017 1:43 PM
To: pgsql-novice(at)postgresql(dot)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;

E-MAIL CONFIDENTIALITY NOTICE:
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.