LLM Best Practices

Supabase Row Level Security: Best Practices

Last updated 4 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Row Level Security (RLS) makes Postgres filter every query by policies, whichever client sent it. Supabase exposes tables over PostgREST, so a client-side filter is a convenience: a caller can edit the request and reach any row that no grant or policy blocks. RLS and grants are the security boundary. Key handling is in supabase.

Pair grants with policies

Postgres runs two checks before a client touches a table. Grants decide whether a role may run an operation at all; policies decide which rows the operation affects. A table in an exposed schema without RLS is readable and writable by any role holding a grant. Existing projects typically gave anon and authenticated broad default grants, and adding policies does not take those back.

revoke all on table public.reports from anon, authenticated;
grant select, insert, update, delete on table public.reports to authenticated;

New tables are no longer exposed to the Data API automatically (the default for new projects since 2026-05-30, enforced on all existing projects on 2026-10-30), so grants must be explicit; see supabase. Revoke anon access unless the data is meant to be public.

Enable RLS on every exposed table, explicitly

Tables created with raw SQL ship with RLS off, so PostgREST exposes every row to anyone with a grant. Enable RLS yourself and then add policies; with RLS on and no policies, nothing is accessible, which is the safe default.

alter table public.profiles enable row level security;

Write USING for visibility and WITH CHECK for new values

USING filters existing rows for SELECT, UPDATE, and DELETE. WITH CHECK validates the row an INSERT or UPDATE writes. INSERT needs WITH CHECK; UPDATE needs both, and an UPDATE also needs a matching SELECT policy. Name the role with to.

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

An UPDATE policy with USING but no WITH CHECK lets a user reassign user_id and write rows they should not own. Always pair them.

Wrap auth functions and index policy columns

auth.uid() returns null for unauthenticated requests, so an equality policy matches nothing. Write (select auth.uid()), not auth.uid(), so Postgres evaluates it once per statement through an initPlan instead of per row. Index every column a policy filters on.

create index profiles_user_id_idx on public.profiles (user_id);

Supabase’s performance guide reports large improvements on big tables from indexing policy columns. See postgres for indexing strategy.

Do not trust user_metadata, and make views obey RLS

  • auth.jwt() claims in user_metadata are editable by the user. Keep authorization data (roles, tenant ids) in app_metadata.
  • A view runs with its owner’s rights and bypasses RLS by default. On Postgres 15 and later create it with with (security_invoker = true) so it obeys the caller’s policies.
  • SECURITY DEFINER functions run as their owner, so one owned by postgres bypasses the caller’s RLS. Prefer SECURITY INVOKER and keep definer functions narrow.

Never ship the secret key

sb_secret_... keys and the legacy service_role key map to the service_role role, which has BYPASSRLS. The table owner and the postgres role also bypass RLS. Keep secret keys on the backend only, store them per secrets-and-env, and review exposure when an agent or mcp-security surface can reach them. See python-security for backend-secret posture, auth-sessions for how tokens reach the database, and oauth-vs-jwt for the claim model.

Test policies as the client would

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

Run automated pgTAP tests for each policy where possible. Before shipping, list tables without RLS and review policies:

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.

Backlinks