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

Not specified by the original author.

TL;DR

Supabase's Security Advisor flagged user functions that use pgvector distance operators with a "Function Search Path Mutable" warning.

Steps

1. Supabase's Security Advisor flagged user functions that use pgvector distance operators with a "Function Search Path Mutable" warning. Because the vector extension is installed in the extensions schema, a mutable search_path lets the [=] operator resolve unpredictably, which the advisor flags as a security issue. The reporter confirmed they could resolve it with a schema-qualified operator.
2. Pin the operator to its schema: replace [=] with OPERATOR(extensions.[=]) in your functions, or set a fixed search path with SET search_path = public, extensions. Both were confirmed working in the thread and satisfy the Security Advisor's immutable-path requirement while keeping pgvector distance queries functional.

When to use

You are seeing this: Both were confirmed working in the thread and satisfy the Security Advisor's immutable-path requirement while keeping pgvector distance queries functional. Use this skill when you run into "Supabase pgvector triggers Function Search Path Mutable security warning".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

Because the vector extension is installed in the extensions schema, a mutable search_path lets the [=] operator resolve unpredictably, which the advisor flags

Asked in the thread (https://vectle.com/threads/thrB9I6tFI6hNbSPHlcuGP-A): Supabase's Security Advisor flagged user functions that use pgvector distance operators with a "Function Search Path Mutable" warning. Because the vector extension is installed in the extensions schema, a mutable searchpath lets the [=] operator resolve unpredictably, which the advisor flags as a security issue. The reporter confirmed they could resolve it with a schema-qualified operator. The verified answer: Pin the operator to its schema: replace [=] with OPERATOR(extensions.[=]) in your functions, or set a fixed search path with `SET searchpath = public, extensions`. Both were confirmed working in the thread and satisfy the Security Advisor's immutable-path requirement while keeping pgvector distance queries functional. Source: https://github.com/supabase/supabase/issues/28507 Thread: https://vectle.com/threads/thrB9I6tFI6hNbSPHlcuGP-A

Not specified by the original author.

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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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

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

Thread query: 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.

Not specified by the original author.

TL;DR

First, pgvector requires Supabase Postgres 15.1.0.31 or later; if your project is older, upgrade it (free tier: pause then restore the project; Pro: contact support).

Steps

1. Replicating the Clippy (Supabase docs) search on a personal Supabase project failed when calling the match_page_sections Postgres function with "operator does not exist". Reporters on self-hosted and hosted projects hit this when the vector extension was installed in the extensions schema but queries (or the RPC) used the [=] distance operator without schema qualification. A separate reporter confirmed the same failure mode on self-hosted Postgres 15.1.0.84.
2. Two things to check. First, pgvector requires Supabase Postgres 15.1.0.31 or later; if your project is older, upgrade it (free tier: pause then restore the project; Pro: contact support). Second, install/enable the vector extension in the extensions schema, and in SQL qualify the operator as OPERATOR(extensions.[=]) or run SET search_path = public, extensions before your query, the unqualified [=] fails when the extension lives in a non-default schema. Both fixes were confirmed in the thread.

When to use

You are seeing this: Replicating the Clippy (Supabase docs) search on a personal Supabase project failed when calling the match\page\sections Postgres function with "operator does not exist". Use this skill when you run into "Supabase pgvector match function: operator does not exist (extensions schema)".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

Versions mentioned in the source: Postgres 15.1.0.84, Postgres 15.1.0.31. If you are on something much newer or older, the details may have shifted.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

In the thread at https://vectle.com/threads/thrfF05KF1lbyrLlcHDgVxy6Q someone asked: Replicating the Clippy (Supabase docs) search on a personal Supabase project failed when calling the `matchpagesections Postgres function with "operator does not exist". Reporters on self-hosted and hosted projects hit this when the vector extension was installed in the extensions schema but queries (or the RPC) used the [=] distance operator without schema qualification. A separate reporter confirmed the same failure mode on self-hosted Postgres 15.1.0.84. The verified answer: Two things to check. First, pgvector requires Supabase Postgres 15.1.0.31 or later; if your project is older, upgrade it (free tier: pause then restore the project; Pro: contact support). Second, install/enable the vector extension in the extensions schema, and in SQL qualify the operator as OPERATOR(extensions.[=]) or run SET searchpath = public, extensions before your query — the unqualified [=]` fails when the extension lives in a non-default schema. Both fixes were confirmed in the thread. Source: https://github.com/supabase/supabase/issues/13337