A PostgreSQL flaw

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. Unfortunately, the 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 here. The only alternative is the laborious and error-prone task of schema-qualifying function and operator references in SECURITY DEFINER functions.

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.