how to fix the Supabase pgvector Function Search Path Mutable security warning

TL;DR

Set a fixed search_path on every SECURITY DEFINER function the linter flags, typically SET search_path = public (plus any schemas the function needs), then re-run the Supabase linter to confirm the warning is gone. A mutable search path lets an attacker who can create objects in a searched schema hijack what your privileged function resolves; pinning the path closes it.

The query

how to fix the Supabase pgvector Function Search Path Mutable security warning

Use this when

• The Supabase database linter or advisor flags "Function Search Path Mutable"
• You have SECURITY DEFINER functions (common with pgvector helpers, RPC wrappers)
• You are hardening Postgres functions before a security review
• You want the advisor dashboard green without suppressing real warnings

Not for

• pgvector index or query performance tuning
• General Postgres DBA work
• Supabase Auth or Row Level Security setup

Steps

1. List the flagged functions. In the Supabase dashboard open Database, then Advisors, and note each function name and schema. Or query pg_proc for SECURITY DEFINER functions with a mutable search path.

Expected output: the exact list of functions to fix, no guessing.

1. For each function, decide the minimal schemas it needs. Most need just public; pgvector helper functions may need public and extensions (wherever pgvector lives in your project).

Expected output: a per-function schema list you can defend.

1. Alter each function to pin the search path. Example:

   ALTER FUNCTION public.match_documents SET search_path = public, extensions;

Use CREATE OR REPLACE with the SET search_path clause if you prefer to keep it in your migration files. Expected output: the command succeeds; the function's definition now shows the fixed path.

1. Put the fix in a migration, not just the dashboard SQL editor, so it survives redeploys and is reviewed like code. One migration per batch of functions is fine.

Expected output: a migration file committed and applied to staging.

1. Re-run the Supabase linter (Advisors, refresh) and confirm the warning is gone for every function you touched.

Expected output: zero "Function Search Path Mutable" findings.

1. Regression-check the functions still work: call each one with representative inputs, especially the pgvector similarity searches, and confirm results match the pre-change behavior.

Expected output: identical results, no new errors in the logs.

Variant phrasings

"Supabase advisor security warning functions"

Same fix. The advisor groups several function warnings; search-path mutability is the most common and the fix above clears it.

"Postgres SECURITY DEFINER search_path best practice"

The general rule behind this warning: every SECURITY DEFINER function should pin search_path. Apply it to all privileged functions, not just the flagged ones.

"pgvector RPC function security hardening"

pgvector apps often wrap similarity search in RPC functions marked SECURITY DEFINER so anon roles can call them. Those are exactly the functions this warning targets.

Why this happens

Postgres resolves unqualified object names using search_path, and the default is mutable per session. A SECURITY DEFINER function runs with the owner's privileges, so if an attacker can plant a table or function in an earlier-searched schema, the privileged function may resolve to the attacker's object. Pinning the path removes the ambiguity.

Edge cases and pitfalls

• Do not set search_path = pg_temp tricks or empty paths unless you fully qualify every reference; missing schemas break the function at runtime.
• Extensions installed in a custom schema must be in the path or the function fails after the change; test, do not assume.
• ALTER FUNCTION needs the exact signature (argument types); check pg_proc or the dashboard if the alter says the function does not exist.
• Re-apply the fix after restoring from a backup taken before the migration; advisors will tell you if it regressed.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstEb3QycvjDHwQhurBrLvnQ