Обсуждение: And I thought I had this solved.

Поиск
Список
Период
Сортировка

And I thought I had this solved.

От
stan
Дата:
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

Re: And I thought I had this solved.

От
Adrian Klaver
Дата:
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

Re: And I thought I had this solved.

От
Adrian Klaver
Дата:
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

Re: And I thought I had this solved.

От
stan
Дата:
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