Setting the file. One moment.
Skill 01 · Supabase Postgres Best Practices
Subchapter 1.34
references/security-rls-performance.mdMarkdown2 KBView on GitHub
Poorly written RLS policies can cause severe performance issues. Use subqueries and indexes strategically.
Incorrect (function called for every row):
create policy orders_policy on orders
using (auth.uid() = user_id); -- auth.uid() called per row!
-- With 1M rows, auth.uid() is called 1M timesCorrect (wrap functions in SELECT):
create policy orders_policy on orders
using ((select auth.uid()) = user_id); -- Called once, cached
-- 100x+ faster on large tablesUse security definer functions for complex checks:
SECURITY DEFINER functions run with the creator’s privileges and bypass RLS on any tables they touch — which is what makes them useful for internal lookups, but also what makes them dangerous if misused. Always include an explicit auth.uid() check inside the function body, keep them in a non-exposed schema, and revoke EXECUTE from any role that shouldn’t call them directly.
-- Create helper function in a private schema
create or replace function private.is_team_member(team_id bigint)
returns boolean
language sql
security definer
set search_path = ''
as $$
select exists (
select 1 from public.team_members
-- always check the calling user's identity inside the function
where team_id = $1 and user_id = (select auth.uid())
);
$$;
-- Revoke direct execution from public roles
revoke execute on function private.is_team_member(bigint) from PUBLIC, anon, authenticated, service_role;
-- Use in policy (indexed lookup, not per-row check)
create policy team_orders_policy on orders
using ((select private.is_team_member(team_id)));Always add indexes on columns used in RLS policies:
create index orders_user_id_idx on orders (user_id);Reference: RLS Performance (opens in a new tab)