Supabase security definer views: why your view skips RLS

Updated Oct 22, 20265 min read

A Supabase security definer view is a view that runs with its owner's rights. You turned on RLS, wrote a policy per user and added a view to make a query easier. Unless you set security_invoker, that view runs as its owner, and on Supabase the owner is usually postgres, which skips RLS. security definer functions do the same thing, and anyone with your anon key can call them through the API.

Why a view ignores your policies

A Postgres view checks permissions as the role that owns it, not as the caller. Supabase's RLS docs note that views bypass RLS by default because they are usually created by the postgres user. Since Postgres 15 you can switch that with security_invoker = true, and then the caller's policies apply.

Views in public are part of the API like tables are, so with Supabase's default grants the request needs nothing more than the anon key your frontend already ships.

Security definer functions work the same way

A function marked security definer runs as its creator. The Supabase docs on database functions warn that such a function can return rows the caller isn't allowed to read, and that any role can call it by default, including anon. Functions in public are reachable at /rest/v1/rpc/name.

That's fine when the function checks the caller itself, like a helper that looks up auth.uid() for an RLS policy. It's a hole when the function takes the user from its arguments:

create function public.update_user_subscription(
  p_user_id uuid, p_new_plan_id uuid)
returns void language plpgsql security definer as $$
begin
  insert into user_subscriptions (user_id, plan_id, status)
  values (p_user_id, p_new_plan_id, 'active');
end $$;

Anyone can call this with any user id and any plan. It's the database version of a server action that trusts its arguments.

Three patterns to look for

A view over a table with RLS. Per-user totals, dashboards and join views, created without security_invoker. One repo had a migration called "remove security definer from views" that dropped the views and created them again, still without the option.

A view over auth.users. select u.email from auth.users u join profiles p ... in a public view puts every user's email into the API.

An RPC that trusts its arguments. A security definer function with p_user_id, p_order_id or p_admin_id parameters and no auth.uid() check: mark an order as paid, give a user a plan, send a notification in someone's name, use up someone's credits.

What we found in public repos

We searched public GitHub code for migrations that create views next to RLS policies and read the SQL from 25 repos. Our check flagged views in 13 of them. Examples were per-user purchase totals, every user's tasks, contracts with the other party's email and a view that joins auth.users for email addresses. Leaderboards and vote counts were usually meant to be public, so we now skip aggregates that don't group by a user or team.

In a second sample of 25 repos with security definer functions that take p_user_id, the check flagged functions in 12, among them functions that set an order's payment status, switch any user's subscription or write a contributor's role with the caller's own claim that they are an admin. In a control sample of 15 repos that use security_invoker = true on their views, the check flagged views in one repo, where some views had security_invoker = false set on purpose.

We picked these files because they matched the search, so this is not a rate across all Supabase apps. We read about 30 flagged items by hand and didn't contact the repo owners.

How to fix a Supabase security definer view

1. Set security_invoker on every view

   alter view public.order_totals
   set (security_invoker = true);

   Put the option in every create or replace view too, because a replace that doesn't list it turns it off again.

2. Move auth.users out of public views

   Keep a public.profiles table with the fields the app needs, filled by a trigger, and give it its own RLS policies. If you need a view over auth.users, put it in a schema that isn't exposed.

3. Check the caller inside security definer functions

   Use auth.uid() instead of a user id parameter. If an admin passes another user's id, check the admin role in the function from app_metadata or a roles table, not user_metadata.

4. Revoke execute on server-only functions

   revoke execute on function public.update_order_payment_status
   from public, anon, authenticated;

   Your backend still calls it with the service role key.

5. Pin the search path

   Add set search_path = '' and write public.table in the body. Supabase's docs recommend this for security definer functions.

6. Run the Security Advisor

   Database > Advisors in the Supabase dashboard lists security definer views for your live project, including ones made in the SQL editor.

create view public.order_totals as
select user_id, sum(total) as spent
from public.orders
group by user_id;

create view public.order_totals
with (security_invoker = true) as
select user_id, sum(total) as spent
from public.orders
group by user_id;

Fix prompt you can paste

Review every view and function in supabase/migrations.
For each view in the public schema: if it reads a table with RLS or
auth.users, recreate it WITH (security_invoker = true) or move it to a
non-exposed schema, and keep the option in every later CREATE OR REPLACE.
For each SECURITY DEFINER function in public: replace user id parameters
with auth.uid(), add an ownership or admin check (admin role from
app_metadata or a roles table), add SET search_path = '' and
schema-qualify every table, and if only server code calls it, REVOKE
EXECUTE FROM public, anon, authenticated. List each change and add a
test where user B calls the view and each RPC and gets none of user A's
rows.

How Leakcade checks this

Our view and function check reads your SQL migrations in order. It flags public views that read a table with RLS (or auth.users) without security_invoker, and security definer functions in public that take a user id or update rows without any auth check and are still callable by anon or authenticated. Tables that are public-read anyway, counts per post or day, trigger functions and cleanup jobs are left alone. Grants changed only in the dashboard don't show up in migrations, so the report points you to the Security Advisor for the live state.

Further reading

1. Supabase docs: Row Level Security (views)
2. Supabase docs: Database functions (security definer, privileges)
3. Supabase docs: Database Advisors
4. PostgreSQL docs: CREATE VIEW

FAQ