Questions regarding SET option.

Started by Jignesh Shahabout 16 years ago7 messagesgeneral

jignesh.shah1980@gmail.com

about 16 years ago

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

Pavel Stehule

pavel.stehule@gmail.com

about 16 years ago

In reply to: Jignesh Shah (#1)

Re: 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>:

Show quoted text

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

Laurenz Albe

laurenz.albe@cybertec.at

about 16 years ago

In reply to: Jignesh Shah (#1)

Re: 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

Jignesh Shah

jignesh.shah1980@gmail.com

about 16 years ago

In reply to: Laurenz Albe (#3)

Re: 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.albe@wien.gv.at>wrote:

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

about 16 years ago

In reply to: Jignesh Shah (#4)

Re: Questions regarding SET option.

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

Show quoted text

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

Jignesh Shah

jignesh.shah1980@gmail.com

about 16 years ago

In reply to: Pavel Stehule (#5)

Re: 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.stehule@gmail.com>wrote:

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

about 16 years ago

In reply to: Jignesh Shah (#6)

Re: Questions regarding SET option.

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

Show quoted text

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