Thread: security definer default for some PL languages (SQL/PSM)?

Hello,

SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), but 
PostgreSQL default is SECURITY CALLLER. Is acceptable to define security 
flag in dependency to used language?

Regards

Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

Pavel Stehule wrote:
> SQL/PSM default for SQL procedures are SECURITY DEFINER (like views),

I can't find this in the standard.  Where did you get this information?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), but 
> PostgreSQL default is SECURITY CALLLER. Is acceptable to define security 
> flag in dependency to used language?

I'd vote no, even if Peter is wrong and you're right about what the spec
says.  A PL gets to set the rules within its function body, not outside.
Next you'll be telling us that the standard requires that the CREATE
FUNCTION not use a dollar-quoted function body ... to which the answer
will be "too bad".  I think the principle of least surprise dictates
that security properties shouldn't be inconsistent across PLs.
        regards, tom lane

>
>Pavel Stehule wrote:
> > SQL/PSM default for SQL procedures are SECURITY DEFINER (like views),
>
>I can't find this in the standard.  Where did you get this information?
>
>--

Jim Melton, SQL's stored procedures, page 43,

next:
blom98sqlpsm:

3.3.1 Access rights to data
By default, the stored routine can access the same data as the creator of 
the routine.
Hence all tables that the creator of a routine can access and/or change can 
also be
accessed and changed by the routine

but I can't find any better info now, so it's can be propably 
implementation-defined.

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/

>
>"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> > SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), 
>but
> > PostgreSQL default is SECURITY CALLLER. Is acceptable to define security
> > flag in dependency to used language?
>
>I'd vote no, even if Peter is wrong and you're right about what the spec
>says.  A PL gets to set the rules within its function body, not outside.

I prefare security invoker too. It's secure. This question is again here 
over some years. Spec knows both, but doesn't speak clearly which is 
default. From my view SECURITY DEFINER is more natural and consistent in SQL 
framework (like views) and maybe a little bit simpler for some beginers. My 
view isn't too important. I checked this topic again and I thing so default 
depends on implementation now.

>Next you'll be telling us that the standard requires that the CREATE
>FUNCTION not use a dollar-quoted function body ... to which the answer
>will be "too bad".  I think the principle of least surprise dictates
>that security properties shouldn't be inconsistent across PLs.

I am finding solution and concensus. Propably CREATE FUNCTION without 
dollar-quoted function body is  more elegant, but it means big enhancing of 
main parser. It hasn't sence with external parser like plpgsql or plpgpsm, 
and I don't wont to do it now. Our syntax simply allow more languages and I 
really haven't problem with it.

last note:

SQL/PSM doesn't specify syntax for CREATE FUNCTION or CREATE PROCEDURE 
statement. So it is possible be compatible with SQL/PSM with dollar-quoted 
function body. With any IDEs this topic isnt too important.

Regards
Pavel Stehule

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. 
http://www.msn.cz/

Peter Eisentraut wrote:
>> SQL/PSM default for SQL procedures are SECURITY DEFINER (like views),
>
> I can't find this in the standard.  Where did you get this
> information?

I only have a draft version of SQL:2003, which says in the 'Foundation'
book, chapter 11.50 ("<SQL-invoked routine>"), about the "<rights
clause>"
(which can be "SQL SECURITY INVOKER" or "SQL SECURITY DEFINER") in
Syntax Rule
19) b):

"If R is an SQL routine, then if <rights clause> is not specified,
then SQL SECURITY DEFINER is implicit."

It adds, however, in chapter 4.27.2, that
"An SQL routine is an SQL-invoked routine whose <language clause>
specifies SQL."

Rule 20) b) of chapter 11.50 says that

"If R is an external routine, then if <external security clause> is not
specified, then EXTERNAL SECURITY IMPLEMENTATION DEFINED is implicit."

Here, "An external routine is one whose <language clause> does not
specify SQL."

"Implementation defined" here means the obvious thing.


So one can make a case that SQL functions should be SECURITY DEFINER by
default, but for all other procedural languages the standard explicitly
sets no rules.

For me, who comes from a UNIX background, SECURITY INVOKER is the
natural
default value, and the standard's decision surprises me. I guess that it
is counter-intuitive to most people, and moreover it would break
compatibility with current behaviour.
I think that it is wise to break with the standard in this case,
SECURITY INVOKER being the safer option.

It should be noted, however, that Oracle's PL/SQL functions have
AUTHID DEFINER by default, which corresponds to our SECURITY DEFINER.

Yours,
Laurenz Albe