Row-Level Security Patterns That Actually Work

Supabase exposes your Postgres database directly to the client via PostgREST. That means row-level security is not optional. It is your primary access control layer. Get it wrong and your users' data is exposed. Here are the patterns that work and the mistakes to avoid.

How RLS Works

RLS policies are rules attached to tables that filter rows based on session context. Every query is silently rewritten to include the policy's condition. In Supabase, the current user's ID is available via auth.uid(), which reads the JWT from the request.

Pattern 1: User Owns Row

Each row has a user_id column, and users can only access their own rows:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_read_own" ON documents
  FOR SELECT USING (user_id = auth.uid());

CREATE POLICY "users_insert_own" ON documents
  FOR INSERT WITH CHECK (user_id = auth.uid());

CREATE POLICY "users_update_own" ON documents
  FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "users_delete_own" ON documents
  FOR DELETE USING (user_id = auth.uid());

Note the difference: USING filters which existing rows the user can access. WITH CHECK validates data being written. For UPDATE you need both, so users cannot change user_id to someone else's.

Pattern 2: Multi-Tenant Isolation

For SaaS where users belong to organizations, isolate at the tenant level:

CREATE OR REPLACE FUNCTION user_org_ids()
RETURNS SETOF UUID LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public AS $$
  SELECT org_id FROM org_members WHERE user_id = auth.uid();
$$;

CREATE POLICY "members_read_org_projects" ON projects
  FOR SELECT USING (org_id IN (SELECT user_org_ids()));

The helper function keeps policies clean and can be optimized independently. Mark it SECURITY DEFINER carefully and always set search_path.

Pattern 3: Role-Based Access Within a Tenant

Different permission levels within an organization:

CREATE OR REPLACE FUNCTION user_org_role(check_org_id UUID)
RETURNS TEXT LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public AS $$
  SELECT role FROM org_members
  WHERE org_id = check_org_id AND user_id = auth.uid();
$$;

-- Anyone in the org can read
CREATE POLICY "members_read" ON projects
  FOR SELECT USING (user_org_role(org_id) IS NOT NULL);

-- Members and admins can create
CREATE POLICY "members_insert" ON projects
  FOR INSERT WITH CHECK (user_org_role(org_id) IN ('member', 'admin'));

-- Only admins can delete
CREATE POLICY "admins_delete" ON projects
  FOR DELETE USING (user_org_role(org_id) = 'admin');

Common Mistakes

Forgetting to enable RLS. If RLS is not enabled on a table, every client can read and write every row. New tables default to RLS disabled. Every migration that creates a table must include ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Catch violations with:

SELECT schemaname, tablename FROM pg_tables
WHERE schemaname = 'public'
  AND tablename NOT IN (
    SELECT relname FROM pg_class WHERE relrowsecurity = true
  );

Run this in CI. If it returns rows, something is exposed.

Missing WITH CHECK on UPDATE. Without it, a user could update their own document and set user_id to another user's ID, transferring ownership. Always include WITH CHECK on INSERT and UPDATE policies.

Leaking the service role key. The service_role key bypasses RLS entirely. Never put it in client-side code or frontend environment variables. Use it only in server-side functions.

Overly broad policies. A policy like USING (auth.role() = 'authenticated') gives every logged-in user access to every row. Unless you are building a public content platform, this is almost never correct.

Testing RLS Policies

Impersonate a user by setting JWT claims in your session:

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

-- Verify they see only their rows
SELECT * FROM documents;

-- Verify writes to other users fail
INSERT INTO documents (user_id, title) VALUES ('user-b-uuid', 'Should fail');

RESET role;
RESET request.jwt.claims;

For automated testing, use pgTAP or any framework that runs SQL assertions. Test that each role sees exactly the rows they should and nothing more.

The Checklist

Before shipping: every public table has RLS enabled, every table has explicit policies for each operation, UPDATE and INSERT include WITH CHECK, the service role key is server-side only, and you have tested policies by impersonating different users. RLS is not something you add later. It is the foundation of your data access layer.