Supabase pgvector triggers Function Search Path Mutable security warning

Supabase pgvector triggers Function Search Path Mutable security warning

The problem

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.

The verified fix

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. Source: https://github.com/supabase/supabase/issues/28507

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