Thread: Re: [GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions

> > It has come to the attention of the core team of the PostgreSQL project 
> > that insecure programming practice is widespread in SECURITY DEFINER 
> > functions.  Many of these functions are exploitable in that they allow 
> > users that have the privilege to execute such a function to execute 
> > arbitrary code with the privileges of the owner of the function.
> > 
> > The SECURITY DEFINER property of functions is a special non-default 
> > property that causes such functions to be executed with the privileges 
> > of their owner rather than with the privileges of the user invoking the 
> > function (the default mode, SECURITY INVOKER).  Thus, this mechanism is 
> > very similar to the "setuid" mechanism in Unix operating systems.
> > 
> > Because SQL object references in function code are resolved at run time, 
> > any references to SQL objects that are not schema qualified are 
> > resolved using the schema search path of the session at run time, which 
> > is under the control of the calling user.  By installing functions or 
> > operators with appropriate signatures in other schemas, users can then 
> > redirect any function or operator call in the function code to 
> > implementations of their choice, which, in case of SECURITY DEFINER 
> > functions, will still be executed with the function owner privileges.  
> > Note that even seemingly innocent invocations of arithmetic operators 
> > are affected by this issue, so it is likely that a large fraction of 
> > all existing functions are exploitable.
> > 
> > The proper fix for this problem is to insert explicit SET search_path 
> > commands into each affected function to produce a known safe schema 
> > search path.  Note that using the default search path, which includes a 
> > reference to the "$user" schema, is not safe when unqualified 
> > references are intended to be found in the "public" schema and "$user" 
> > schemas exist or can be created by other users.  It is also not 
> > recommended to rely on rigorously schema-qualifying all function and 
> > operator invocations in function source texts, as such measures are 
> > likely to induce mistakes and will furthermore make the source code 
> > harder to read and maintain.
> 
> But if we insert a set schema search_path command in an SQL function,
> the caller will be affected by it. Doing reset search_path before
> returning to caller might solve some of problems, but it will not
> recover caller's special search_path. How do you solve the problem?

I looked into this more and I think I'm afraid the proposed solution 

> The proper fix for this problem is to insert explicit SET search_path 
> commands into each affected function to produce a known safe schema 
> search path.

actually does not work for SQL functions. For example,

CREATE OR REPLACE FUNCTION foo(INTEGER, INTEGER) RETURNS INTEGER AS $$
SET search_path To pg_catalog,public;
SELECT mod($1,$2);
$$ LANGUAGE sql SECURITY DEFINER;

If an attacker creates public.mod() to do something bad and override
his search_path to public,pg_catalog before executing foo(), his
attack will succeed since calling to mod() is resolved in the plan
time thus it will be resolved to public.mod, rather than
pg_catalog.mod.

Am I missing something?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Tatsuo Ishii <ishii@postgresql.org> writes:
> I looked into this more and I think I'm afraid the proposed solution 
> actually does not work for SQL functions. For example,

> CREATE OR REPLACE FUNCTION foo(INTEGER, INTEGER) RETURNS INTEGER AS $$
> SET search_path To pg_catalog,public;
> SELECT mod($1,$2);
> $$ LANGUAGE sql SECURITY DEFINER;

> If an attacker creates public.mod() to do something bad and override
> his search_path to public,pg_catalog before executing foo(), his
> attack will succeed since calling to mod() is resolved in the plan
> time thus it will be resolved to public.mod, rather than
> pg_catalog.mod.

True, because the SQL-function code runs parse analysis for the whole
function body before executing any of it.  We could fix it by doing
parse-analyze/plan/execute one statement at a time, which would make
SQL functions work more like multi-statement strings submitted by a
client application.  Just a day or two ago there was someone complaining
that they couldn't create and use a temp table in the same SQL function,
due to this same behavior; and I recall similar gripes in the past.
Maybe it's time to change it.
        regards, tom lane

> Tatsuo Ishii <ishii@postgresql.org> writes:
> > I looked into this more and I think I'm afraid the proposed solution 
> > actually does not work for SQL functions. For example,
> 
> > CREATE OR REPLACE FUNCTION foo(INTEGER, INTEGER) RETURNS INTEGER AS $$
> > SET search_path To pg_catalog,public;
> > SELECT mod($1,$2);
> > $$ LANGUAGE sql SECURITY DEFINER;
> 
> > If an attacker creates public.mod() to do something bad and override
> > his search_path to public,pg_catalog before executing foo(), his
> > attack will succeed since calling to mod() is resolved in the plan
> > time thus it will be resolved to public.mod, rather than
> > pg_catalog.mod.
> 
> True, because the SQL-function code runs parse analysis for the whole
> function body before executing any of it.  We could fix it by doing
> parse-analyze/plan/execute one statement at a time, which would make
> SQL functions work more like multi-statement strings submitted by a
> client application.  Just a day or two ago there was someone complaining
> that they couldn't create and use a temp table in the same SQL function,
> due to this same behavior; and I recall similar gripes in the past.
> Maybe it's time to change it.
> 
>             regards, tom lane

Ok. So bottom line would be "do not use SECURITY DEFINER SQL functions
unless you make every object including functions and operators into
schema-qualified one".
--
Tatsuo Ishii
SRA OSS, Inc. Japan