Description

Ensure every Postgres function in exposed schemas has a fixed search_path set, preferably an empty string, and refers to objects with fully qualified names such as public.table_name. This makes sure the function always uses the objects it was written for.

Rationale

If a function's search_path is not fixed, it depends on the search_path of the user calling it. An attacker who can create objects in a schema on that path could make the function use a fake table or function instead of the real one. This is especially dangerous for SECURITY DEFINER functions, which run with higher permissions.

Impact

  • Prevents functions from being tricked into using the wrong objects

  • Makes function behavior predictable

  • Functions may need fully qualified object names

Default Value

By default, Postgres functions do not have a fixed search_path and use the caller's search_path.

Pre-Requisites

  • Project Owner or Admin role

  • Access to the SQL Editor

Test Plan

  1. Sign in to the Supabase Dashboard at https://supabase.com/dashboard.

  2. Select your project.

  3. Open Advisors in the sidebar and select Security Advisor.

  4. Check for the finding Function Search Path Mutable.

  5. If any function is listed, follow the implementation steps.

Implementation Steps

  1. Sign in to the Supabase Dashboard at https://supabase.com/dashboard.

  2. Select your project.

  3. Open the SQL Editor in the sidebar.

  4. For each listed function, run: alter function public.<function_name>(<argument types>) set search_path = '';

  5. Update the function body to use fully qualified names (for example, public.orders) if needed.

  6. Test the function and rerun the Security Advisor.

Backout Plan

  1. Sign in to the Supabase Dashboard at https://supabase.com/dashboard.

  2. Select your project.

  3. Open the SQL Editor in the sidebar.

  4. Run: alter function public.<function_name>(<argument types>) reset search_path;

Reference