Supabase RLS in Production: The Patterns That Actually Work

Row Level Security is Supabase's answer to data isolation — write a policy, and the database enforces it on every query without touching your application code. The concept is simple. The production reality is full of silent failures, performance traps, and behavior that looks correct until it isn't.

These are the patterns I use across seven production apps, and the gotchas I've run into along the way.

Always Use FORCE ROW LEVEL SECURITY#

When you run ALTER TABLE t ENABLE ROW LEVEL SECURITY, you're enabling RLS for API-level access. But the table owner — the postgres superuser — still bypasses it silently. This matters because Supabase's own backend jobs and some SDK operations run as postgres.

The fix is one extra line:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY makes RLS apply to the table owner as well. Without it, you may test as an API user and see correct behavior, then discover that something running as postgres is reading all rows without restriction.

Always Specify the Role in Your Policy#

Without a TO clause, a policy applies to every role that has table-level GRANT access — including anon. This is a common source of unintended public access.

-- Dangerous: applies to anon too if anon has SELECT grant
CREATE POLICY "user_reads_own" ON documents
  USING (auth.uid() = user_id);

-- Correct: only authenticated users
CREATE POLICY "user_reads_own" ON documents
  FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

The TO authenticated clause short-circuits policy evaluation for unauthenticated requests entirely. It's both a security guard and a performance optimization.

Wrap auth.uid() in SELECT — Always#

This is the single highest-impact performance change you can make to existing RLS policies, and almost nobody does it by default.

-- Slow: auth.uid() is evaluated on every row
USING (auth.uid() = user_id)

-- Fast: evaluated once per statement, result is cached
USING ((SELECT auth.uid()) = user_id)

Without the SELECT wrapper, PostgreSQL calls auth.uid() once per row during the policy check. On a 100,000-row table, benchmarks show this difference: 171ms vs under 0.1ms. The wrapping causes the planner to treat it as an initPlan — computed once, then reused. Apply the same pattern to auth.jwt() and any security definer function that returns a fixed value per query.

Also index every column that appears in a policy USING clause:

CREATE INDEX idx_documents_user_id ON documents(user_id);

Without the index, the policy turns every query into a sequential scan.

UPDATE Policies Silently Fail Without SELECT#

This one wastes hours. PostgreSQL requires a SELECT policy to be in place for UPDATE to work. Without it, an UPDATE returns 0 affected rows — no error, no feedback, just silence. If your mutations appear to succeed but nothing changes in the database, check whether you have a SELECT policy.

For UPDATE specifically, always supply both USING and WITH CHECK:

CREATE POLICY "user_updates_own" ON documents
  FOR UPDATE TO authenticated
  USING ((SELECT auth.uid()) = user_id)       -- which rows can I target?
  WITH CHECK ((SELECT auth.uid()) = user_id); -- is the result still mine?

USING filters the rows that can be targeted. WITH CHECK validates the new state after the update. Without WITH CHECK, a user could update a row's user_id to someone else's ID — transferring ownership of data they legitimately own.

Fix Infinite Recursion With SECURITY DEFINER Functions#

If an RLS policy on a table references that same table in a subquery, PostgreSQL will infinitely recurse evaluating the policy. The classic case is checking a user's role from a profiles table inside a policy on profiles itself.

-- BROKEN: infinite recursion
CREATE POLICY "read_own" ON profiles
  FOR SELECT USING (
    id = auth.uid() OR
    role = (SELECT role FROM profiles WHERE id = auth.uid()) -- recurses
  );

The fix is a SECURITY DEFINER function. These functions run as the function owner (bypassing RLS on the tables they touch), breaking the cycle:

CREATE OR REPLACE FUNCTION get_my_role()
RETURNS text LANGUAGE sql
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT role FROM public.profiles WHERE id = (SELECT auth.uid());
$$;

CREATE POLICY "read_own" ON profiles
  FOR SELECT USING (
    id = (SELECT auth.uid()) OR role = (SELECT get_my_role())
  );

Two things to always do with SECURITY DEFINER functions: set search_path = '' (prevents search path injection), and don't expose them in Supabase's API schema (they can be called directly via RPC).

Views Bypass RLS — Use security_invoker#

Standard PostgreSQL views run as the view owner (postgres), which bypasses RLS on their underlying tables. A view over a documents table with RLS will expose every row to anyone who can select from the view.

In Postgres 15+ (which Supabase uses), the fix is one option:

CREATE VIEW public.my_documents
WITH (security_invoker = true) AS
SELECT * FROM documents;

security_invoker = true makes the view run RLS as the calling role, not the view owner. Supabase's Security Advisor will flag any views missing this option. Check it regularly — it catches this and other common misconfigurations.

Multi-Tenant Isolation With JWT Claims#

For multi-tenant apps where rows belong to an organization rather than a user, I store a tenant_id on every table and resolve the current tenant from the JWT.

The safest place to embed tenant context is app_metadata, which is server-controlled and cannot be modified by the user:

CREATE POLICY "tenant_isolation" ON orders
  FOR ALL TO authenticated
  USING (
    tenant_id = (
      (SELECT auth.jwt()) -> 'app_metadata' ->> 'org_id'
    )::uuid
  )
  WITH CHECK (
    tenant_id = (
      (SELECT auth.jwt()) -> 'app_metadata' ->> 'org_id'
    )::uuid
  );

Never use raw_user_meta_data for authorization — users can write to it themselves. Only app_metadata is safe for access control.

To embed org_id in the JWT at login time, use Supabase's Custom Access Token Hook. This runs a function before the token is issued and lets you add custom claims — avoiding a database lookup on every request.

One caveat: JWT claims are cached until the token refreshes (default one hour). If you revoke a user's org membership, that change won't take effect in RLS policies until their token expires. For time-sensitive revocations, you need to force a token refresh on the client.

The Service Role Key Bypasses Everything#

The service role key grants Supabase's service_role Postgres role, which has BYPASSRLS privilege. Every query made with it ignores all RLS policies — unconditionally, regardless of what your policies say.

Use it only in server-side code: API routes, Server Actions, cron jobs, backend scripts. Never in browser code, mobile apps, or any client-visible context. Exposing the service role key is equivalent to handing someone direct unrestricted database access.

The setup is straightforward:

import { createClient } from '@supabase/supabase-js';

// Server-only — never expose this key to clients
const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

Keep the service role key in server-side environment variables only. In Next.js, that means no NEXT_PUBLIC_ prefix.

The Debugging Pattern That Saves Time#

When a policy is blocking something it shouldn't (or allowing something it shouldn't), run the query directly in the Supabase SQL editor while impersonating the relevant role:

SET LOCAL role = authenticated;
SET LOCAL request.jwt.claims = '{"sub": "your-user-uuid", "role": "authenticated"}';

EXPLAIN ANALYZE SELECT * FROM documents;

This shows exactly which policies are being evaluated and whether the query is using indexes. Most RLS debugging dead-ends disappear once you can see the actual execution plan.