Обсуждение: Questions regarding SET option.

Hello

you can overwrite standard settings only for function

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = }
default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT } <<<===
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

Regards
Pavel Stehule


2010/2/22 Jignesh Shah <jignesh.shah1980@gmail.com>:
> Hello All,
>
> I have been writing a function with SECURITY DEFINER enabled. Basically, I
> am looking for ways to override the users SET option settings while
> executing my function to prevent the permissions breach. For example, to
> override "SET search_path", I am setting search path in my function before
> executing anything. Could any one please tell me what could be other SET
> options that I should take care?
>
> Moreover, how to revert back those settings just before returning from my
> function?
>
> Thanks, Jack

Jignesh Shah wrote:
> I have been writing a function with SECURITY DEFINER enabled.
> Basically, I am looking for ways to override the users SET
> option settings while executing my function to prevent the
> permissions breach. For example, to override "SET
> search_path", I am setting search path in my function before
> executing anything. Could any one please tell me what could
> be other SET options that I should take care?
>
> Moreover, how to revert back those settings just before
> returning from my function?

You can use the SET clause of CREATE FUNCTION which does exactly
what you want.

Yours,
Laurenz Albe

2010/2/22 Jignesh Shah <jignesh.shah1980@gmail.com>:
> Thanks a ton Laurenz and Pavel for your responses but I really didn't follow
> you. I am not master in PostGreSQL yet. Could you please give me some
> example?
>
> Basically, I want to know how many such SET options I should reset before
> executing my function and at the end it should also be restored to original
> settings.
>

create or replace function foop()
 returns int as $$
select 10
$$ language sql
set work_mem to '1MB'
set search_path = 'public';
CREATE FUNCTION
postgres=#

regards
Pavel Stehule

> It would be really helpful if you could elaborate your response.
>
> Thanks guys.
> Jack
>
> On Mon, Feb 22, 2010 at 8:05 PM, Albe Laurenz <laurenz.albe@wien.gv.at>
> wrote:
>>
>> Jignesh Shah wrote:
>> > I have been writing a function with SECURITY DEFINER enabled.
>> > Basically, I am looking for ways to override the users SET
>> > option settings while executing my function to prevent the
>> > permissions breach. For example, to override "SET
>> > search_path", I am setting search path in my function before
>> > executing anything. Could any one please tell me what could
>> > be other SET options that I should take care?
>> >
>> > Moreover, how to revert back those settings just before
>> > returning from my function?
>>
>> You can use the SET clause of CREATE FUNCTION which does exactly
>> what you want.
>>
>> Yours,
>> Laurenz Albe
>
>

2010/2/22 Jignesh Shah <jignesh.shah1980@gmail.com>:
>>> set work_mem to '1MB'
>>> set search_path = 'public';
>
> Thanks for the example Pavel. I understood it. Are there any other SET
> options except above that I need to set to prevent security breach?
>

I am not sure - I know only search_path

Pavel

> Thanks,
> Jack
>
> On Mon, Feb 22, 2010 at 11:41 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> 2010/2/22 Jignesh Shah <jignesh.shah1980@gmail.com>:
>> > Thanks a ton Laurenz and Pavel for your responses but I really didn't
>> > follow
>> > you. I am not master in PostGreSQL yet. Could you please give me some
>> > example?
>> >
>> > Basically, I want to know how many such SET options I should reset
>> > before
>> > executing my function and at the end it should also be restored to
>> > original
>> > settings.
>> >
>>
>> create or replace function foop()
>>  returns int as $$
>> select 10
>> $$ language sql
>> set work_mem to '1MB'
>> set search_path = 'public';
>> CREATE FUNCTION
>> postgres=#
>>
>> regards
>> Pavel Stehule
>>
>> > It would be really helpful if you could elaborate your response.
>> >
>> > Thanks guys.
>> > Jack
>> >
>> > On Mon, Feb 22, 2010 at 8:05 PM, Albe Laurenz <laurenz.albe@wien.gv.at>
>> > wrote:
>> >>
>> >> Jignesh Shah wrote:
>> >> > I have been writing a function with SECURITY DEFINER enabled.
>> >> > Basically, I am looking for ways to override the users SET
>> >> > option settings while executing my function to prevent the
>> >> > permissions breach. For example, to override "SET
>> >> > search_path", I am setting search path in my function before
>> >> > executing anything. Could any one please tell me what could
>> >> > be other SET options that I should take care?
>> >> >
>> >> > Moreover, how to revert back those settings just before
>> >> > returning from my function?
>> >>
>> >> You can use the SET clause of CREATE FUNCTION which does exactly
>> >> what you want.
>> >>
>> >> Yours,
>> >> Laurenz Albe
>> >
>> >
>
>