Thread: And I thought I had this solved.
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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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? If the above is the entire function I am not seeing that you need to SET search_path as there is no object that needs schema qualification. I would comment it out and run it. If that fails then set the function volatile. -- Adrian Klaver adrian.klaver@aklaver.com
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.klaver@aklaver.com
On Fri, Nov 22, 2019 at 04:06:14PM -0800, Adrian Klaver wrote: > 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. OH, that seems the cleanest way to do this. Thanks. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin