flashman
← All posts

PostgreSQL search_path security debugging

Debug PostgreSQL search_path surprises by tracing roles, schemas, functions, extensions, migrations, and which object a live statement actually resolves.

2026-09-29 · 6 min read · Rahul Chitturi

  • sql
  • postgresql
  • security

PostgreSQL resolves an unqualified table, function, or operator by walking the session search_path. A statement that passed in a migration role can call a different object when the application role, a SECURITY DEFINER function, or a restored database uses another path.

That mismatch is a correctness bug and, when an untrusted schema appears earlier in the path, a privilege bug. Temporary objects and a writable public schema are the usual places an unexpected function gets selected.

Capture who is connected and what they see

Record the database, role, current_schemas output, the exact SQL text, and whether the statement ran inside a function. Include the function owner and whether it is SECURITY DEFINER or SECURITY INVOKER.

  • Compare the migration role with the application role.
  • Check database, role, and function-level search_path settings.
  • List schemas the role can create objects in.
  • Note extensions and where their objects were installed.

Find the object that actually ran

Qualify the name in a catalog query or use EXPLAIN to see the resolved relation. For functions, signature and schema both matter: two functions can share a name and differ by argument types. A cast or operator from an earlier schema can change a predicate without changing the SQL text.

SECURITY DEFINER functions should set a safe search_path at definition time, typically a locked schema list that does not begin with a caller-writable schema. Relying on the caller's path lets the caller influence which objects the privileged function uses.

Make the failing session reproducible

Use Flashman's SQL formatter to compare the unqualified statement with its schema-qualified form, the timestamp converter to align session and migration logs, JSON formatter for catalog snapshots, and diff for role settings.

Test default paths, empty paths, temporary schemas, SECURITY DEFINER and INVOKER functions, extension installs, restore jobs, poolers that reset session state, and a role that can create objects in public.

Try these tools