The Complete Production SOP: How to Deploy and Run Supabase Client & PostgreSQL Row Level Security (RLS) in Autonomous AI Agents

---
name: "supabase-auth-row-level-security"
description: "Enforce zero-trust multi-tenant data access policies at the database layer with Supabase and RLS."
metadata:
  author: "Supabase"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "write postgres rls policy supabase"
  - "setup @supabase/ssr nextjs"
  - "auth.uid() rls rule"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';

export async function createClient() {
  const cookieStore = await cookies();

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll();
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            );
          } catch {
            // The `setAll` method was called from a Server Component.
            // This can be ignored if you have middleware refreshing user sessions.
          }
        },
      },
    }
  );
}

-- Create documents table
CREATE TABLE IF NOT EXISTS public.documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE DEFAULT auth.uid(),
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc'::text, now())
);

-- Step 1: Force Row Level Security
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents FORCE ROW LEVEL SECURITY;

-- Step 2: Grant read access only to owners
CREATE POLICY "Users can view own documents"
ON public.documents
FOR SELECT
USING (auth.uid() = user_id);

-- Step 3: Grant write access constrained by identity
CREATE POLICY "Users can insert own documents"
ON public.documents
FOR INSERT
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own documents"
ON public.documents
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete own documents"
ON public.documents
FOR DELETE
USING (auth.uid() = user_id);

import { describe, it, expect } from 'vitest';
import { createClient } from '@supabase/supabase-js';

describe('Supabase RLS Boundary Enforcement', () => {
  it('prevents authenticated user from reading foreign tenant documents', async () => {
    // Client authenticated as Tenant B
    const clientB = createClient(
      process.env.NEXT_PUBLIC_SUPABASE_URL!,
      process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
      { auth: { persistSession: false } }
    );
    
    // Assume mock token for tenant-b-user
    const { data, error } = await clientB
      .from('documents')
      .select('*')
      .eq('user_id', '11111111-1111-1111-1111-111111111111'); // Tenant A ID

    expect(error).toBeNull();
    expect(data).toHaveLength(0); // Zero-trust boundary returns empty array
  });
});