---

name: eng-tenant-isolation-row-level-security
description: Use when designing, auditing, or debugging the PostgreSQL Row Level Security (RLS) policies that enforce tenant isolation in the multi-tenant legal AI product. Covers the membership-based isolation model, policy patterns for all core tables, common bypass traps, and the mandatory integration test suite that verifies no cross-tenant data leakage.
license: MIT
metadata:
id: eng.tenant-isolation-row-level-security
category: eng
jurisdictions: [multi]
priority: P2
intent: [eng, rls, multi-tenant, security, supabase, postgresql]
related: [eng-supabase-edge-functions-patterns, eng-supabase-index-knowledge-pipeline, eng-pii-redaction-preprocessor, safety-client-confidentiality-cross-tenant]
source: Louis — HAQQ Legal AI (github.com/sboghossian/mini-claude-for-legal)
version: "1.0"

Tenant Isolation — Row Level Security

What it does

In a legal AI product handling confidential client documents from multiple law firms and companies, a data leak between tenants is catastrophic — ethically, legally, and commercially. Row Level Security (RLS) is the primary database-level enforcement mechanism that guarantees Tenant A's data can never be read by Tenant B, regardless of application-layer bugs.

This skill defines the RLS model, policy patterns, mandatory test cases, and common pitfalls for the Supabase/PostgreSQL multi-tenant schema.

Setup / auth

RLS must be enabled on every table that contains tenant-scoped data:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE document_chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE pii_audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE explainer_videos ENABLE ROW LEVEL SECURITY;

The access identity comes from auth.uid() (Supabase JWT). The JWT is propagated to Postgres via the anon or authenticated role set by the PostgREST gateway.

Capabilities

Tenancy model

The product uses a workspace-based multi-tenancy model:

auth.users (one per person)
    ↓
memberships (user ↔ workspace, with role: owner | admin | member | viewer)
    ↓
workspaces (one per law firm / company)
    ↓
all data tables (workspace_id / tenant_id column)

The key lookup helper:

-- Reusable inline function (materialized once per query via security definer)
CREATE FUNCTION current_workspace_ids()
RETURNS UUID[]
LANGUAGE SQL SECURITY DEFINER STABLE AS $
  SELECT ARRAY(
    SELECT workspace_id FROM memberships
    WHERE user_id = auth.uid() AND status = 'active'
  )
$;

Canonical RLS policy pattern

-- Single-workspace tables (user belongs to exactly one workspace for a resource)
CREATE POLICY "tenant read isolation"
  ON documents FOR SELECT
  USING (tenant_id = ANY(current_workspace_ids()));

CREATE POLICY "tenant write isolation"
  ON documents FOR INSERT
  WITH CHECK (tenant_id = ANY(current_workspace_ids()));

CREATE POLICY "tenant update isolation"
  ON documents FOR UPDATE
  USING (tenant_id = ANY(current_workspace_ids()))
  WITH CHECK (tenant_id = ANY(current_workspace_ids()));

CREATE POLICY "tenant delete isolation"
  ON documents FOR DELETE
  USING (tenant_id = ANY(current_workspace_ids()));

Role-based access within a tenant

Some tables need finer control within a workspace (e.g., only owner / admin can delete documents):

CREATE POLICY "admin delete documents"
  ON documents FOR DELETE
  USING (
    tenant_id = ANY(current_workspace_ids())
    AND EXISTS (
      SELECT 1 FROM memberships
      WHERE user_id = auth.uid()
        AND workspace_id = documents.tenant_id
        AND role IN ('owner', 'admin')
        AND status = 'active'
    )
  );

System-wide knowledge (no tenant_id)

For tables that hold shared legal knowledge (legislation, public regulatory guidance) accessible to all authenticated users:

CREATE POLICY "authenticated read system knowledge"
  ON system_chunks FOR SELECT
  USING (auth.role() = 'authenticated');

-- No INSERT/UPDATE/DELETE for non-service-role users

Audit log (append-only)

The pii_audit_log table must be append-only for users — no UPDATE or DELETE:

CREATE POLICY "tenant audit log read"
  ON pii_audit_log FOR SELECT
  USING (tenant_id = ANY(current_workspace_ids()));

CREATE POLICY "tenant audit log insert"
  ON pii_audit_log FOR INSERT
  WITH CHECK (tenant_id = ANY(current_workspace_ids()));

-- No UPDATE or DELETE policies → implicit deny

Integration test suite (mandatory)

Every deploy must pass these tests before reaching production:

describe("RLS tenant isolation", () => {
  test("user from tenant A cannot read tenant B documents", async () => {
    const docB = await createDoc(tenantB);
    const { data, error } = await supabaseAs(userA).from("documents").select().eq("id", docB.id);
    expect(data).toHaveLength(0);
  });

  test("user from tenant A cannot read tenant B chunks", async () => {
    const chunkB = await createChunk(tenantB);
    const { data } = await supabaseAs(userA).from("document_chunks").select().eq("id", chunkB.id);
    expect(data).toHaveLength(0);
  });

  test("vector search does not return cross-tenant chunks", async () => {
    const chunks = await retrieveRelevantChunks(testQuery, tenantA.id);
    chunks.forEach(c => expect(c.tenant_id).toBe(tenantA.id));
  });

  test("audit log cannot be deleted by tenant user", async () => {
    const { error } = await supabaseAs(userA).from("pii_audit_log").delete().neq("id", "none");
    expect(error).not.toBeNull(); // RLS blocks delete
  });

  test("service role bypasses RLS (expected)", async () => {
    const { data } = await supabaseServiceRole.from("documents").select();
    expect(data!.length).toBeGreaterThan(1); // sees all tenants
  });
});

The last test confirms the expected behavior of service role — document it so engineers don't accidentally use service role in user-facing Edge Functions.

Common traps

Trap	Consequence	Fix
Table created without ENABLE ROW LEVEL SECURITY	All rows readable by all users	Add ALTER TABLE ... ENABLE ROW LEVEL SECURITY and a permissive/deny policy
Using service role key in a user-facing Edge Function	RLS bypassed	Audit all Edge Function deployments for key type
current_workspace_ids() not SECURITY DEFINER	Can be overridden by malicious Postgres session	Always use SECURITY DEFINER for helper functions
Forgetting WITH CHECK on INSERT/UPDATE	Can insert rows into another tenant's scope	Always pair USING with WITH CHECK
pgvector match_chunks RPC bypasses RLS	Cross-tenant vector search results	Embed WHERE tenant_id = in the SQL function itself
Soft-delete (deleted_at) without RLS filter	Deleted rows from other tenants still visible	Add AND deleted_at IS NULL to USING clause

Related skills

• [[eng-supabase-edge-functions-patterns]] — Edge Functions must use anon key (not service role) to benefit from RLS
• [[eng-supabase-index-knowledge-pipeline]] — chunk table RLS is critical for RAG safety
• [[eng-pii-redaction-preprocessor]] — data layer safety complements RLS at the application layer
• [[safety-client-confidentiality-cross-tenant]] — policy that mandates this technical control