An announcement of possibly
insecure practices in user-defined PostgreSQL functions seems at first
blush to be
a fairly straightforward advisory; a deeper look reveals some serious
implications. It is a problem that echoes a textbook security hole in
UNIX setuid programs; it would appear that the developers did
not consider that history when adding a setuid-like capability to PostgreSQL.
Unfortunately, it also appears that the fix that the advisory recommends
is not up to the task of resolving the issue. Anyone using SECURITY DEFINER
functions in PostgreSQL probably has quite a large job ahead of them to
clear up this particular mess.
PostgreSQL functions can be be declared as "SECURITY DEFINER" functions, which
causes them to run with the privileges of the owner rather than those of
the invoker. PostgreSQL binds the operators and functions called at
runtime and searches each element in the schema path to find them.
user invoking the function can control the schema search
path and, by defining operators or other functions that are used by
the SECURITY DEFINER function, the invoker can run any code with the
permissions of the owner.
The once common, now hopefully largely eradicated, UNIX parallel was a
vulnerability in setuid programs that invoked other programs via
exec(). If the
program did not either sanitize its PATH environment variable or fully
specify the path to the executable, it was vulnerable to attackers who
would put their own code in the path, with the same name as the executable,
ahead of the standard program. When the setuid program executed, it would
grab the wrong binary and the attacker could run arbitrary code with
the permissions of the owner of the setuid program. Another important
requirement is that all elements of the sanitized PATH and the directory
of the binary are not writable by non-privileged users.
So, much like the solution to the UNIX issue, the advisory suggests that
SECURITY DEFINER functions specify a sanitized schema path. The
equivalent to a fully specified path is not recommended as it is
"likely to induce mistakes and will furthermore
make the source code harder to read and maintain." Unfortunately, it
turns out that because of the way PostgreSQL processes the function
definitions, the only solution is to schema-qualify each and every function
and operator reference in the function. In addition, setting a schema
search path in a function is not local to the function, it changes the global
search path for the whole program; functions that do this should restore
the original search path on exit.
It turns out that the references in a function are resolved as PostgreSQL
creates an execution plan for the function. This is prior to actually
executing the "set search path" operation in the function and so it will bind to
functions and operators in the user controlled schema path as described
The only alternative is the laborious and error-prone task of
schema-qualifying function and operator references in SECURITY DEFINER
This is a very unfortunate outcome for a feature that was meant to promote
more secure database usage. The idea is to separate the database privileges
into different users but to still allow users with few privileges to
perform a restricted set of privileged operations. It is surprising that
the UNIX setuid issues from the dawn of time_t were not more
closely studied when this feature was implemented. It would also seem that
the PostgreSQL developers will need to rework how the execution plan and
search path interact to fix this design flaw.
to post comments)