On 11/22/19 3:52 PM, stan wrote:
A while back I ran into problems caused by security fix related to the search path. I wound up adding a line to. for instance, this function:REATE FUNCTION work_hours ( start_date date, end_date date ) RETURNS decimal(10,4) stable language sql as $$ /* workaround for secuirty "feature" */ SET search_path TO ica, "user" , public; SELECT sum(case when extract(isodow from d) between 1 and 5 then 8.0 else +0.0 end) from generate_series($1, $2, interval '24 hours') d; $$; And walked away happy, or so I thought. Now I just got this error: [local] stan@stan=# select * from ttl_avail_hours_by_project_and_employee ; ERROR: SET is not allowed in a non-volatile function CONTEXT: SQL function "work_hours" during startup How can I solve this issue?
I thought I was missing something. Third option. As example: https://www.postgresql.org/docs/11/sql-createfunction.html Writing SECURITY DEFINER Functions Safely ... $$ LANGUAGE plpgsql SECURITY DEFINER -- Set a secure search_path: trusted schema(s), then 'pg_temp'. SET search_path = admin, pg_temp; Put the SET outside the function body. -- Adrian Klaver adrian.kla...@aklaver.com
