LLM Best Practices

Supabase Row Level Security: Best Practices

4 min read

View markdown source

Overview

Supabase Row Level Security (RLS) enforces per-row authorization inside Postgres so that every query, regardless of which client sends it, is filtered by policies the database evaluates. Because the Supabase client libraries expose your tables directly over PostgREST, RLS is the security boundary. Client-side filters are convenience, not protection; a caller can edit the request and read or write any row that no policy blocks. Treat RLS as the only line that matters.

Enable RLS on every exposed table, explicitly

RLS is enabled by default only on tables created through the dashboard Table Editor. Tables created with raw SQL or the SQL editor ship with RLS OFF, which means PostgREST exposes every row to every caller (Supabase RLS docs). Always enable it yourself, then add policies.

alter table public.profiles enable row level security;

A table with RLS enabled and zero policies denies all access by default. That is the safe state. Add policies to open specific paths.

Write USING for reads and deletes, WITH CHECK for writes

USING is the visibility filter: it decides which existing rows a SELECT, UPDATE, or DELETE can see. WITH CHECK validates the new row values an INSERT or UPDATE tries to write (Supabase RLS docs). INSERT needs WITH CHECK. UPDATE needs BOTH: USING to pick which rows the user may target, WITH CHECK to constrain what they may set.

create policy "owners read own profiles"
  on public.profiles for select
  using ( (select auth.uid()) = user_id );
 
create policy "owners insert own profiles"
  on public.profiles for insert
  with check ( (select auth.uid()) = user_id );
 
create policy "owners update own profiles"
  on public.profiles for update
  using ( (select auth.uid()) = user_id )
  with check ( (select auth.uid()) = user_id );

The classic failure is an UPDATE policy with USING but no WITH CHECK, or an INSERT without WITH CHECK. A user can then reassign user_id to someone else and write rows they should not own. Always pair the clauses.

Use auth.uid() and auth.jwt(), and account for null

auth.uid() returns the authenticated user’s ID; auth.jwt() returns the full claims object for role or tenant checks. When no valid token is sent, auth.uid() returns null, so an unauthenticated request matches no = user_id policy and sees nothing (Supabase RLS docs). See auth-sessions for how tokens reach the database, and oauth-vs-jwt for the claim model.

Never ship service_role; guard SECURITY DEFINER

The service_role key authorizes the service_role Postgres role, which carries BYPASSRLS and skips every policy. The table owner and the postgres role also bypass RLS (Supabase docs). Keep service_role on the backend only; a leaked key reads and writes all data. Store it per secrets-and-env and review exposure when an agent or mcp-security surface can reach it.

SECURITY DEFINER functions run as the role that created them, so a function owned by postgres bypasses the caller’s RLS. Use SECURITY INVOKER unless you deliberately need elevation, and keep definer functions narrow. See python-security for the broader backend-secret posture.

Optimize: wrap auth calls and index policy columns

Wrap auth.uid() and other functions in a subselect so the planner runs an initPlan once per statement instead of once per row: (select auth.uid()) = user_id instead of auth.uid() = user_id (Supabase RLS performance docs). Then index every column a policy filters on.

create index profiles_user_id_idx on public.profiles (user_id);

Indexing policy columns can yield over 100x improvement on large tables (Supabase RLS performance docs). See postgres for indexing strategy.

Test policies by impersonating roles

Verify policies by switching to the authenticated role and setting JWT claims in a transaction, then running the query the client would run.

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"00000000-0000-0000-0000-000000000000","role":"authenticated"}';
select * from public.profiles;
rollback;

Confirm RLS is on and policies exist before shipping:

select tablename, rowsecurity from pg_tables where schemaname = 'public';
select schemaname, tablename, policyname, cmd from pg_policies where schemaname = 'public';

Any public table with rowsecurity = false is a hole.

Graph View

Backlinks