[GENERAL] 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
Re: [GENERAL] 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.shah1...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions regarding SET option.
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions regarding SET option.
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 On Mon, Feb 22, 2010 at 8:05 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: 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
Re: [GENERAL] Questions regarding SET option.
2010/2/22 Jignesh Shah jignesh.shah1...@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.a...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions regarding SET option.
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 On Mon, Feb 22, 2010 at 11:41 PM, Pavel Stehule pavel.steh...@gmail.comwrote: 2010/2/22 Jignesh Shah jignesh.shah1...@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.a...@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
Re: [GENERAL] Questions regarding SET option.
2010/2/22 Jignesh Shah jignesh.shah1...@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.steh...@gmail.com wrote: 2010/2/22 Jignesh Shah jignesh.shah1...@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.a...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
