Description

Ensure views and functions that run with SECURITY DEFINER are reviewed and kept only where they are truly needed. Views should use security_invoker so they respect the caller's permissions, and SECURITY DEFINER functions should not be callable by the anon or authenticated roles unless that is intended.

Rationale

A SECURITY DEFINER view or function runs with the permissions of its owner, usually a powerful role, instead of the user calling it. This means it can bypass Row Level Security. If such a view or function is exposed through the Data API, any user may be able to read or change data they should not have access to.

Impact

  • Views respect the caller's RLS policies

  • Reduces the risk of RLS being bypassed

  • Some features may need to be redesigned

Default Value

Views in Postgres run with the owner's permissions by default unless security_invoker is set. Functions are SECURITY INVOKER by default unless created with SECURITY DEFINER.

Pre-Requisites

  • Project Owner or Admin role

  • Postgres 15 or later for security_invoker on views

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 findings Security Definer View, and Security Definer Function Executable by anon or authenticated.

  5. If any view or function is listed without a clear reason, 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 view, run: alter view public.<view_name> set (security_invoker = on);

  5. For each listed function that should not be public, run: revoke execute on function public.<function_name> from anon, authenticated;

  6. Rerun the Security Advisor and confirm the findings are resolved.

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. For a view, run: alter view public.<view_name> set (security_invoker = off);

  5. For a function, run: grant execute on function public.<function_name> to anon, authenticated;

Reference