Eng Tenant Isolation Row Level Security

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

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