Supabase Row Level Security: Enforcing Multi‑Tenant Isolation in Postgres

When you ship a SaaS on a shared Postgres schema, any query can accidentally read another tenant's rows. Moving the isolation boundary into the database with Row Level Security (RLS) eliminates that risk without requiring every developer to remember a where‑clause.

Enable RLS and write ownership policies

RLS is PostgreSQL‑native and exposed through Supabase’s dashboard and Management API. Once enabled, every SELECT, INSERT, UPDATE, and DELETE is filtered by policy expressions evaluated by the query planner. If no policy permits the operation, the row is invisible to the role.

-- Run in the Supabase Dashboard SQL editor as the database owner (postgres role)
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

-- Select rows only for the authenticated user
CREATE POLICY projects_select_own ON public.projects
  FOR SELECT
  USING (user_id = auth.uid());

-- Insert rows only with the authenticated user’s ID
CREATE POLICY projects_insert_own ON public.projects
  FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- Update rows only if the user already owns them
CREATE POLICY projects_update_own ON public.projects
  FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

Simple ownership with auth.uid()

Supabase injects the authenticated user’s UUID into the request context as auth.uid(). This function resolves to the auth.uid claim from the JWT, so you can write ownership checks directly in the policy without any application‑level middleware.

Because auth.uid() returns NULL for requests using the anon key, public read access requires an explicit USING (true) policy for the anon role, or a separate policy that allows the anon role.

Hierarchical tenancy with custom JWT claims

Supabase stores arbitrary claims in request.jwt.claims. You can reference them in policies to enforce organization‑level isolation, for example:

CREATE POLICY projects_org_read ON public.projects
  FOR SELECT
  USING (
    organization_id = (
      current_setting('request.jwt.claims', true)::jsonb → 'app_metadata' →> 'organization_id')::uuid
  );

Claims must be validated at token issuance (e.g., in your auth provider) so the value stored in the JWT matches the data in your tables.

Performance, service_role risks, and verification

Policy evaluation happens at planning time. Simple equality checks on indexed columns, such as user_id = auth.uid(), add negligible overhead because the planner can combine the policy predicate with the query predicate and use the index.

Complexity grows with joins or subqueries in USING/CHECK clauses. A policy that references another table can prevent index usage and increase latency proportionally to policy complexity. Keep policies sargable: reference indexed columns directly, avoid volatile functions, and test with EXPLAIN ANALYZE on an RLS‑protected table versus an unprotected copy.

Enabling RLS on a table with existing data does not retroactively filter active sessions, but writes are immediately subject to the CHECK clause. Plan enablement during low‑traffic windows or first create permissive policies, then tighten them.

service_role key bypasses all RLS. If exposed client‑side it grants full database access. Rotate immediately if leaked and never embed the service_role key in frontend bundles.

Verification steps

1. Create a test table with RLS enabled, insert rows as different users via Supabase Auth, and confirm SELECT/INSERT/UPDATE/DELETE behavior matches policy expectations using the Supabase dashboard SQL editor.
2. Run EXPLAIN ANALYZE SELECT * FROM public.projects WHERE user_id = 'some-uuid'; on the RLS‑protected table and compare the plan with an identical query on an unprotected copy.
3. Export the schema with the Supabase CLI: supabase db dump --schema public. Inspect the generated SQL for correct policy syntax and role targeting.
4. Test service_role bypass by executing a query with the service_role key in a server‑side script (e.g., using the Postgres client) and confirming it returns rows invisible to the anon key.
5. Decode a Supabase‑issued access token (e.g., via jwt.io) and confirm custom claims appear under user_metadata or app_metadata as referenced in policies.

Trade‑offs and operational discipline

The biggest operational risk is the service_role key. It bypasses RLS completely; if embedded in a frontend bundle it grants full database access. Rotate immediately if leaked and never expose it client‑side.

RLS does not protect against SQL injection in application code; parameterized queries remain essential even when policies are correctly defined.

Policies on partitioned tables apply per partition. New partitions added by maintenance tools (e.g., pg_partman) need policies recreated manually.

Cross‑table policy references (e.g., checking membership in a teams table) can create circular dependency risks during schema migrations. Create referenced tables before policies that read them.

Check your setup

Verify behavior by creating a test table with RLS, inserting rows as different users via Supabase Auth, and confirming SELECT/INSERT/UPDATE/DELETE results match expectations in the SQL Editor.

Export schema with supabase db dump --schema public and inspect generated policy SQL for correct role targeting.

Decode a Supabase access token to confirm custom claims appear under app_metadata or user_metadata as referenced in policies.

Use the Supabase CLI with supabase start for local development and supabase db diff in CI to verify policy changes before deployment.

Row Level Security moves the data‑access boundary from application code into PostgreSQL, giving you a single source of truth for multi‑tenant isolation. Start with ownership policies on user_id, add hierarchical policies with JWT claims when needed, and always validate policies with EXPLAIN ANALYZE and the Supabase CLI before production deployment.