Re: And I thought I had this solved.

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(at)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