Re: [NOVICE] 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

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;