Обсуждение: Questions regarding SET option.
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
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
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.
It would be really helpful if you could elaborate your response.
Thanks guys.
Jack
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.
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:
You can use the SET clause of CREATE FUNCTION which does exactlyJignesh 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?
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 > >
>> 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?
Thanks,
Jack
>> 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?
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 followcreate or replace function foop()
> 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.
>
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 >> > >> > > >