PostgreSQL search paths define who can influence a query

Calling a familiar SQL function does not necessarily select the function its author intended. PostgreSQL permits functions with the same name and different argument types, and it resolves a call using both namespace visibility and type matching. If another user can add candidates to that resolution process, an ordinary query can execute that user's code. The security problem begins before the chosen function starts running.

The March 1 release announcement addresses CVE-2018-1058 with PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17 and 9.3.22. Much of the response is documentation explaining how to secure installations against objects that change other users' queries. The release also changes supplied programs. Those two parts have different responsibilities, so installing the update is only part of the response for an affected application.

I default to PostgreSQL for relational work, and its extensibility is a substantial part of its appeal. This issue concerns the authority that comes with that extensibility. Defining a function is useful application development. Letting an untrusted account define a function that another account might resolve implicitly makes that permission relevant to the other account's security.

An overload can be a better match

The project's guide to CVE-2018-1058 uses a column of type varchar and an unqualified call to lower. The built-in function accepts text. A user who can create public.lower(varchar) supplies an exact argument match, so the new function can take the call that a developer expected to reach the built-in function.

The important detail is the argument type. This is not adequately explained as a table lookup that simply stops at the first matching spelling. Functions have signatures, and the parser has to choose among them. A name can remain identical in application source while the set of available signatures changes underneath it.

The function-resolution documentation distinguishes functions with identical argument types from functions with different argument types. Search-path position resolves visibility among otherwise identical signatures. Different signatures remain candidates for type matching, and an exact match wins when one exists. This explains why the implicit priority of the system catalog is not a sufficient defence against the guide's example.

Explicit qualification narrows the candidate schema. In this particular built-in call, qualification and an explicit input type document the intended dependency. The expression requests the catalog function with a text argument.

SELECT pg_catalog.lower(CAST('Example' AS text));

That example illustrates one call, not a complete security policy for arbitrary SQL. The resolution documentation also discusses hazards involving default parameters and variadic functions in schemas writable by untrusted users. Qualification does not make an untrusted schema trustworthy. The permission to create another callable object remains relevant inside the schema that a query explicitly names.

This is why a review that searches only for unqualified table names will miss part of the problem. Functions and operators participate in expressions whose spelling can look routine. The security question is whether an account outside the caller's trust relationship can affect which executable object the expression selects. The answer depends on object creation privileges as well as the statement text.

A search path carries a trust decision

The PostgreSQL 10 schema documentation describes the default path as "$user", public. It also documents the default grant of CREATE and USAGE on public to everyone. Adding a writable schema to the path therefore has consequences beyond organizing table names. Every account allowed to create objects there can influence the namespace available to queries using that path.

Two users sharing a database may be allowed to read the same application tables without being trusted to provide code for each other. Those permissions express different relationships. Treating schema creation as a harmless convenience erases that distinction. A secure layout needs to preserve it, particularly when a maintenance account has greater privileges than ordinary application accounts.

The administrative example removes the general creation grant on the shared schema. It is an access-control change that an administrator applies within the database concerned.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Here public names a schema and PUBLIC denotes every role. The command removes the grant made to everyone. It should not be described as proof that no account can create objects there, since ownership and other grants still matter. The relevant policy is to exclude untrusted creators from schemas that trusted queries search.

The practical consequence is that an application's migration identity needs a deliberate place to create objects. Removing a broad grant may expose a workflow that relied on it. That failure is useful information about the previous permission model, but it does not by itself specify the replacement. The administrator still needs to decide which identities own application schemas and which identities execute ordinary queries.

Existing objects deserve a separate examination. Preventing future creation does not tell an administrator which functions were created while the old grant existed. The guide recommends examining public functions, and psql can list them with \df public.*. That inventory supplies evidence about the current namespace. A permissions change and an object inventory answer different questions.

For an application owner, the resulting design question is who should be able to change a query's dependencies. A runtime identity that only needs to read and update application data has a different purpose from an identity that defines executable functions. Keeping those responsibilities explicit makes the trust relationship easier to assess. It also makes a migration failure after revocation understandable as a missing, specific privilege instead of a reason to restore the original grant to everyone.

Supplied tools stop inheriting the risky path

The 10.3 release notes describe changes to pg_dump, pg_upgrade, vacuumdb and other supplied programs. They restrict their search paths to pg_catalog, and autovacuum workers adopt the same restriction. These programs are attractive targets because they commonly run with elevated database privileges. Their work can also invoke user-provided functions indirectly.

The release notes identify index expressions as one such indirect route. A tool does not need to contain an obvious call to an application function for application code to execute during its work. Consequently, securing a command's own SQL is not the whole task. The environment under which an indirectly invoked function resolves its dependencies also matters.

A function that expects a particular ambient search path may now fail during auto-analyze or a reload. The release explicitly warns about this compatibility effect. The appropriate repair is to make the function's dependencies intentional. Restoring an insecure global path to conceal the error would restore the assumption that the update is trying to remove.

I consider that compatibility cost justified. A maintenance operation should not depend on an unrelated role having refrained from creating a competing function. The resulting errors identify dependencies that were previously implicit, which gives maintainers something concrete to repair in their own function definitions.

Privileged functions need a controlled environment

The CREATE FUNCTION documentation distinguishes the default invoker permissions from SECURITY DEFINER, which runs with the function owner's permissions. Its guidance for definer functions requires excluding schemas writable by untrusted users from the search path. It also places pg_temp last when a trusted schema is named first, so temporary objects do not silently take priority.

That distinction changes the impact of an unintended dependency. An invoker function already runs with the caller's authority. A definer function deliberately crosses a privilege boundary. Its owner therefore needs to control which supporting objects execute inside that boundary, as well as the commands visible in the function body.

The same documentation notes that new functions grant execution to PUBLIC by default. It recommends creating a restricted definer function and changing its execution grants within one transaction. This prevents a temporary exposure between creation and revocation. Search-path policy and execution grants solve related but separate problems, since controlling who may enter a function does not determine what that function calls.

My preference is to make those relationships visible in the database definition. A trusted schema identifies where dependencies belong. A function-specific configuration identifies the environment in which they resolve. Grants identify the permitted callers. These decisions can then be reviewed alongside the function's SQL instead of being reconstructed from whichever session happened to invoke it.

The March update makes that work explicit for administrators, application authors and extension maintainers. Its documentation does not claim that every database has the same risk. It identifies the trust condition that matters, whether another user can supply objects to a namespace that a more privileged operation searches. Securing that condition requires examining the actual grants and the actual function dependencies in each affected database.