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

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