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

This problem affects all existing PostgreSQL releases since version 7.3.
Because this situation is a case of poor programming practice in
combination with a design mistake and inadequate documentation, no
security releases of PostgreSQL will be made to address this problem at
this time. Instead, all users are urged to hastily correct their code
as described above. Appropriate technological fixes for this problem
are being investigated for inclusion with PostgreSQL 8.3.

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

pgsql-announce-unsubscribe(at)postgresql(dot)org

> 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?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

On Sat, Feb 17, 2007 at 01:26:34PM +0900, Tatsuo Ishii wrote:

> 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?

Schema-qualifying object accesses would be tedious,
omission-prone but not liable to the above problem.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Tatsuo Ishii <ishii(at)postgresql(dot)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

On Sat, Feb 17, 2007 at 03:15:25PM +0100, Karsten Hilbert wrote:
> On Sat, Feb 17, 2007 at 01:26:34PM +0900, Tatsuo Ishii wrote:
> > 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?
>
> Schema-qualifying object accesses would be tedious,
> omission-prone but not liable to the above problem.

If you schema-qualify objects instead of setting search_path then
don't forget about operators. A query like

SELECT col
FROM schemaname.tablename
WHERE othercol = schemaname.funcname(someval)

is vulnerable because the caller might have defined an = operator
for the appropriate data types and set search_path to find it before
the one in pg_catalog. To be safe you'd need to use

SELECT col
FROM schemaname.tablename
WHERE othercol operator(pg_catalog.=) schemaname.funcname(someval)

which is harder to read and, as Karsten mentioned, prone to omission.
Also, this query might still be vulnerable if funcname() isn't
carefully written.

A PL/pgSQL function could save and restore the caller's search_path
with something like

oldpath := pg_catalog.current_setting('search_path');
PERFORM pg_catalog.set_config('search_path', oldpath, false);

If the function raises an exception then search_path wouldn't be
reset unless you catch exceptions and reset the path in the
exception-handling code.

--
Michael Fuhr

> Tatsuo Ishii <ishii(at)postgresql(dot)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

On Sat, Feb 17, 2007 at 11:31:19AM -0700, Michael Fuhr wrote:

> If you schema-qualify objects instead of setting search_path then
> don't forget about operators.
I knew I had missed something.

> SELECT col
> FROM schemaname.tablename
> WHERE othercol operator(pg_catalog.=) schemaname.funcname(someval)

Good to know what.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346