PostgreSQL 10.4 requires repairs to installed function metadata

A function's implementation is only part of its behavior inside PostgreSQL. Its permissions decide who can invoke it, while its catalog labels tell the planner which execution strategies are valid. PostgreSQL 10.4 fixes mistakes in both kinds of metadata. Because an installed database retains that metadata, the upgrade has steps that a package replacement cannot complete by itself.

The May 10 announcement covers PostgreSQL 10.4 and updates to all other supported branches. Its security issue is CVE-2018-1115, a too-permissive access-control list on an adminpack function. The same announcement supplies explicit post-installation instructions for incorrect volatility and parallel-safety markings. Those instructions make the distinction between updated code and installed catalog state unusually visible.

I value PostgreSQL's ability to explain a query plan, but that explanation depends on the planner receiving correct information. A function label is part of that information. This release is interesting because it corrects the declarations under which existing code runs, including declarations stored inside databases that otherwise continue through a normal minor update.

A wrapper retained the wrong privilege

The 10.4 release notes explain that adminpack's deprecated pg_logfile_rotate() wraps the core pg_rotate_logfile() function. When the core function moved from an internal superuser check to SQL privilege checks, the wrapper needed a corresponding change. That change was missed, leaving ordinary users able to request log rotation when adminpack was installed.

This is a useful example of a security change affecting callers. Moving an access check changes where enforcement occurs. A wrapper that was safe under the old rule may retain a grant that is inappropriate under the new rule. The wrapper's name and purpose need not change for its permission behavior to become incorrect.

The documented repair for existing adminpack installations is an extension update. On affected PostgreSQL 9.6 or 10 installations, an administrator runs this in each database containing the extension:

ALTER EXTENSION adminpack UPDATE;

That scope matters. Updating one database does not establish that another database's installed extension metadata has changed. Likewise, having the corrected extension files available on disk is different from applying their update to an installed extension. The announcement calls for both the software update and the database operation.

The issue's stated consequence is unauthorized requests for log rotation. It should be described at that scope. There is no need to turn a limited permission error into a claim of arbitrary data access to explain why its grant is wrong. Administrative operations require intentional grants even when their immediate effect is narrower than reading application tables.

Volatility is an optimizer promise

The function-volatility documentation defines VOLATILE, STABLE and IMMUTABLE as promises about a function's behavior. A stable function returns the same result for the same arguments within a statement. An immutable function permits stronger assumptions, including evaluation of constant inputs during planning. A volatile function cannot make those promises and may have side effects.

These declarations let the planner avoid work, but avoiding a function call can change the result when the declaration is wrong. A function that performs a side effect is especially sensitive to an optimization that eliminates or combines evaluations. Correctness depends on describing that possibility, even if the function's usual inputs happen to behave more simply.

The May notes identify XML functions that execute user-supplied queries and therefore need volatile markings. A supplied query can contain operations that invalidate a stronger promise. The outer function's classification has to accommodate those operations. Classifying only the ordinary formatting behavior would leave out the behavior of the query it executes.

For an existing database, the announcement provides exact signatures for the corrections. One of those statements is:

ALTER FUNCTION pg_catalog.query_to_xml(text, boolean, boolean, text)
  VOLATILE;

This is one correction from the published list, not a complete script for every affected function. The distinction is important because overloads and signatures identify the object being changed. An administrator needs the full applicable list when carrying out the catalog repair. The example shows what kind of change the release requires.

Parallel restricted and parallel unsafe differ

The PostgreSQL 10 parallel-safety documentation distinguishes operations allowed in workers from operations allowed only in the leader. A restricted operation can execute in the leader during a parallel query. An unsafe operation prevents parallel execution of the query altogether. Marking something restricted is therefore a weaker constraint than marking it unsafe.

That distinction explains why merely keeping a function out of a worker is not always enough. Some operations are incompatible with the surrounding parallel execution itself. The documentation includes database writes and sequence access among reasons for an unsafe classification. The relevant question is what the operation can do, including operations it invokes indirectly.

These labels also explain why a query's performance characteristics can change after a correctness repair. Removing permission to use a parallel plan restricts the planner's choices. That restriction is appropriate when the previous choice could execute under invalid assumptions. A comparison of plans needs to account for the corrected behavior before interpreting the change as a lost optimization. The repair changes which plans are legal for the operation concerned.

The 10.4 notes correct several built-in functions that were marked restricted but need to be unsafe. They also explain that corrected initial catalog data handles new installations, while existing databases retain their previous markings. The release's repair instructions therefore address persistent declarations, not just a different planner implementation.

I prefer this explicit treatment of the upgrade consequences. It gives an operator a precise explanation for why the server can report the new version while still needing database work. The version identifies the available software. The catalogs identify the rules the running database supplies to that software.

For this update, a maintenance record should therefore distinguish binary installation from extension and catalog updates. An affected adminpack installation needs its extension update in every relevant database. Existing databases using the incorrectly marked functions need the applicable corrections from the published instructions. Those operations complete changes that a normal restart with new executables does not apply to already stored function metadata.