Enable Row Level Security for Multi-Tenant Data

Why This Matters

Without Row Level Security (RLS), all authenticated users can access all rows in your tables. This is a critical security vulnerability in multi-tenant applications where users should only see their own data.

RLS is Postgres's built-in mechanism for row-level access control. When enabled, every query is filtered by policies you define. This provides:

• Database-enforced security: Even if application code has bugs, the database prevents unauthorized access
• Multi-tenant isolation: Users can only access rows where they match the policy conditions
• Defense in depth: Works alongside application-level checks for comprehensive security
• Audit compliance: Database-level policies are easier to audit and verify

The Problem

Without RLS enabled, any authenticated user can read, update, or delete any row:

-- Table without RLS: all authenticated users can access all rows
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  title TEXT NOT NULL,
  content TEXT
);

-- Any authenticated user can query all documents
-- User A can see User B's documents!
SELECT * FROM documents;

This is a critical security vulnerability. User A can access User B's data simply by querying the table, even if your application code tries to filter by user_id.

The Solution

Enable RLS and create policies that restrict access based on auth.uid():

-- Enable RLS on the table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only SELECT their own documents
CREATE POLICY "Users can view own documents"
  ON documents
  FOR SELECT
  USING (user_id = auth.uid());

-- Policy: Users can only INSERT documents for themselves
CREATE POLICY "Users can insert own documents"
  ON documents
  FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- Policy: Users can only UPDATE their own documents
CREATE POLICY "Users can update own documents"
  ON documents
  FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- Policy: Users can only DELETE their own documents
CREATE POLICY "Users can delete own documents"
  ON documents
  FOR DELETE
  USING (user_id = auth.uid());

The auth.uid() function returns the authenticated user's ID from Supabase Auth. Policies using USING filter existing rows, while WITH CHECK validates new/modified rows.

Force RLS on All Tables

To prevent accidentally creating tables without RLS, you can force RLS on all tables in a schema:

-- Force RLS on all existing tables in public schema
DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
    EXECUTE 'ALTER TABLE ' || quote_ident(r.tablename) || ' ENABLE ROW LEVEL SECURITY';
  END LOOP;
END $$;

Common Mistakes

• Enabling RLS without policies: This locks everyone out. Always create policies immediately after enabling RLS.
• Forgetting to enable RLS on new tables: Make it part of your table creation workflow. Consider using the force RLS script above.
• Using application-level filtering only: RLS provides defense in depth. Don't rely solely on application code to filter data.
• Not testing with different users: Always test RLS policies by authenticating as different users to verify isolation works.
• Mixing service role and anon keys: Service role bypasses RLS. Only use it server-side. Client-side code must use anon key.

How to Find Tables Without RLS

Use this query to find tables in your database that don't have RLS enabled:

SELECT 
  schemaname,
  tablename
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename NOT IN (
    SELECT tablename 
    FROM pg_tables t
    JOIN pg_class c ON c.relname = t.tablename
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relrowsecurity = true
      AND n.nspname = 'public'
  )
ORDER BY tablename;

Any tables returned by this query are vulnerable and should have RLS enabled immediately.