2026-09-29 · 8 min read
- sql
- postgresql
- security
PostgreSQL names are resolved through search_path unless they are schema-qualified. That convenience hides a session-dependent decision: the same SQL text can target a different table, function, or operator when the role, database default, or function definition changes.
Treat search_path as part of the security boundary. A caller who can create an object in an earlier schema can influence unqualified names used by a privileged function.
Choose an explicit schema layout
Put application objects in a dedicated schema and decide whether public remains usable. Extensions should be installed into a known schema rather than whichever schema the installing role happens to use.
- Qualify names in migrations and security-sensitive functions.
- Set database and role defaults deliberately.
- Limit which roles can create objects in shared schemas.
- Document the path each application connection should see.
Lock privileged functions
A SECURITY DEFINER function runs with its owner's privileges, so an unqualified call inside it must not follow the caller's search_path. Set a minimal path on the function, schema-qualify every reference, and avoid a leading schema that ordinary users can write to.
SECURITY INVOKER functions follow the caller, which is appropriate for some utilities and dangerous for others. Record the choice next to the function so a later edit does not flip it silently.
Control temporary objects and extensions
Temporary schemas can appear early in name resolution. If a workflow creates temporary functions or operators, make sure they cannot shadow the objects a privileged code path expects.
Extension upgrades may add overloads or move objects. After an upgrade, compare the resolved signatures used by the application with the signatures you tested, especially for functions that share a short name.
Keep pools and restores from drifting
Connection poolers can reset or preserve session settings depending on configuration. A path set interactively will not save you if the pooled connection presents a different one. Put the required setting in the role, database, or connection configuration that every checkout uses.
Restores and migration roles often have broader paths than the application. Run a post-deploy check that resolves representative unqualified names as the application role, not only as the owner.
Verify resolution under the real role
Use Flashman's SQL formatter to compare qualified and unqualified statements, the timestamp converter to align migration and session logs, JSON formatter for catalog snapshots, diff for role settings, and the regex tester when a log scrubber extracts schema-qualified names.
Test role defaults, empty paths, temporary schemas, SECURITY DEFINER and INVOKER functions, extension install schemas, pooler resets, restores, and a low-privilege role that can create objects in public.