Multi-Tenant Postgres mit RLS: Der Weg zu partial unique indexes

Wer ein Multi-Tenant-SaaS auf Postgres baut, landet früher oder später bei Row-Level Security. Die meisten Tutorials enden, sobald die erste Policy steht. Das ist der Punkt, an dem die echten Probleme erst anfangen.

In zwei meiner Side-Projects (OrionReach und Objecto) habe ich beide Wege durchgespielt. Erst der naive Ansatz mit Unique-Constraints über ganze Tabellen, der spektakulär scheitert. Dann partial unique indexes, die das Problem korrekt lösen. Hier ist der Weg dahin.

Das Setup#

Wir bauen eine leads-Tabelle mit Workspace-Isolation. Jeder Workspace soll seine eigenen Leads haben, und die gleiche Email darf in unterschiedlichen Workspaces existieren (verschiedene Sales-Teams arbeiten unabhängig voneinander am gleichen Markt).

CREATE TABLE workspaces (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL
);
 
CREATE TABLE leads (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  email text NOT NULL,
  linkedin_url text,
  full_name text,
  created_at timestamptz NOT NULL DEFAULT now()
);

Naiver Versuch eins: globaler Unique-Constraint#

Erste Idee, die alle haben:

ALTER TABLE leads ADD CONSTRAINT leads_email_unique UNIQUE (email);

Das funktioniert offensichtlich nicht, weil zwei Workspaces dieselbe Email haben dürfen.

Naiver Versuch zwei: Composite Unique#

Der nächste Versuch ist sauberer:

ALTER TABLE leads
  ADD CONSTRAINT leads_workspace_email_unique
  UNIQUE (workspace_id, email);

Das funktioniert technisch. Aber es deckt nur einen Teil der Anforderung ab. In OrionReach habe ich gemerkt: Leads kommen oft nur über LinkedIn-URL rein, ohne Email. Erst später (nach Email-Enrichment) wird die Email-Spalte gefüllt. Der UNIQUE-Constraint verbietet aber mehrere NULL-Emails im selben Workspace nicht. Postgres behandelt NULL in Unique-Constraints als verschieden.

In der Praxis bedeutet das: ein Workspace kann 5.000 Leads haben, alle ohne Email. Wenn dann die Enrichment-Pipeline für 200 davon die gleiche Email findet (weil Apollo dieselben Daten zurückliefert), kollidiert nichts beim Insert, aber es entstehen 200 Duplikate. Erst beim Versuch, den Status der Email zu updaten, fliegt es auf.

Der saubere Weg: partial unique indexes#

Postgres erlaubt UNIQUE-Constraints nur über alle Zeilen einer Tabelle. Aber UNIQUE-Indexes sind flexibler: sie können Bedingungen tragen.

CREATE UNIQUE INDEX leads_workspace_email_unique_idx
  ON leads (workspace_id, email)
  WHERE email IS NOT NULL;
 
CREATE UNIQUE INDEX leads_workspace_linkedin_unique_idx
  ON leads (workspace_id, linkedin_url)
  WHERE linkedin_url IS NOT NULL;

Das löst beide Probleme auf einmal. Mehrere NULL-Werte sind erlaubt, weil sie die WHERE-Klausel nicht passieren. Sobald aber zwei Zeilen im gleichen Workspace dieselbe Email haben, kollidiert der Index.

In Drizzle sieht das so aus:

import { pgTable, uuid, text, timestamp, uniqueIndex } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
 
export const leads = pgTable(
  "leads",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    workspaceId: uuid("workspace_id").notNull(),
    email: text("email"),
    linkedinUrl: text("linkedin_url"),
    fullName: text("full_name"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("leads_workspace_email_unique")
      .on(t.workspaceId, t.email)
      .where(sql`${t.email} IS NOT NULL`),
    uniqueIndex("leads_workspace_linkedin_unique")
      .on(t.workspaceId, t.linkedinUrl)
      .where(sql`${t.linkedinUrl} IS NOT NULL`),
  ]
);

Die RLS-Policy dazu#

Der Index allein verhindert Duplikate. Die RLS-Policy verhindert, dass Workspace A jemals Daten von Workspace B sieht. Das sind unabhängige Konzepte, die zusammen gehören.

Naive Tutorials zeigen oft das hier:

-- BAD: Subquery wird pro Row evaluiert
CREATE POLICY leads_workspace_isolation ON leads
  FOR ALL TO authenticated
  USING (
    workspace_id IN (
      SELECT workspace_id FROM workspace_members
      WHERE user_id = auth.uid()
    )
  );

Das funktioniert, aber Postgres' Query-Planner kann die Subquery in vielen Fällen nicht gut hoisten. Bei SELECT * FROM leads WHERE created_at > now() - interval '7 days' sieht der Plan oft einen Nested-Loop-Join über workspace_members pro Row. Bei 100.000 Leads schlägt das auf 80 bis 200ms statt der erwarteten 5ms durch.

Die Lösung ist eine SECURITY DEFINER-Helper-Function, die einmal pro Connection die Workspaces des Users liefert. Postgres caches das Ergebnis innerhalb der Statement-Boundary.

CREATE OR REPLACE FUNCTION public.is_workspace_member(check_workspace_id uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.workspace_members
    WHERE user_id = auth.uid()
      AND workspace_id = check_workspace_id
  );
$$;
 
CREATE INDEX workspace_members_user_workspace_idx
  ON public.workspace_members (user_id, workspace_id);
 
ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY leads_workspace_isolation ON leads
  FOR ALL TO authenticated
  USING (is_workspace_member(workspace_id))
  WITH CHECK (is_workspace_member(workspace_id));

STABLE plus SECURITY DEFINER sind beide wichtig. STABLE sagt Postgres: das Ergebnis ändert sich nicht innerhalb derselben Query. Der Planner kann den Function-Call hoisten und das Index-Lookup nur einmal machen, statt pro Row.

Drei weitere Sachen, die in den ersten Tutorials oft fehlen.

Erstens: USING prüft beim SELECT/UPDATE/DELETE, WITH CHECK beim INSERT/UPDATE. Beides setzen, sonst kann ein User in fremde Workspaces inserten.

Zweitens: für Service-Zugriff (n8n, Background-Jobs) verwende ich nicht authenticated, sondern service_role. Das umgeht RLS komplett. Das ist beabsichtigt, weil Background-Jobs explizit mit dem Workspace-Kontext arbeiten, den sie aus dem Job-Payload bekommen. RLS in Background-Workern erzwingen ist die Quelle vieler subtiler Production-Bugs.

Drittens: SECURITY DEFINER heißt, die Function läuft mit den Rechten des Eigentümers. Achte darauf, sie als postgres-User anzulegen, nicht als anon oder authenticated. Sonst hast du eine Privilege-Escalation gebaut.

Performance-Check#

Ein partial index ist nicht „kostenlos". Er liest beim Insert/Update einen zusätzlichen B-Tree. Bei kleinen Tabellen unter 100.000 Zeilen merkst du davon nichts. Bei einer Tabelle, die ich auf 2.5M Zeilen gepushed habe (synthetic load test), waren Inserts mit zwei partial indexes ca. 18% langsamer als ohne.

Das ist im Trade-off gegen Datenqualität immer noch den Aufwand wert. Was die meisten unterschätzen: Duplikate in einer Multi-Tenant-DB sind nicht nur ein Datenproblem, sie sind ein Bug-Magnet. Jeder Code-Pfad, der mit Leads arbeitet, muss damit rechnen, dass mehrere Zeilen dieselbe „Identität" haben. Das macht jede Iteration teurer.

Migration im laufenden System#

Das letzte Stück: wenn du diesen Index nachträglich auf einer existierenden Tabelle anlegst, die schon Duplikate hat, scheitert die Migration. CREATE UNIQUE INDEX failt mit ERROR: could not create unique index.

Lösung in zwei Schritten. Erst CREATE INDEX CONCURRENTLY ohne UNIQUE, dann Duplikate finden und auflösen, dann den Index droppen und mit UNIQUE neu erstellen.

-- Schritt 1: Find duplicates
SELECT workspace_id, email, count(*), array_agg(id) AS lead_ids
FROM leads
WHERE email IS NOT NULL
GROUP BY workspace_id, email
HAVING count(*) > 1;
 
-- Schritt 2: Resolve (manuell oder per script)
-- Behalte den ältesten Lead, merge alles andere darin
 
-- Schritt 3: Index erstellen
CREATE UNIQUE INDEX CONCURRENTLY leads_workspace_email_unique_idx
  ON leads (workspace_id, email)
  WHERE email IS NOT NULL;

CONCURRENTLY ist wichtig in Production. Ohne lockt der Index die Tabelle und alle Schreibzugriffe blockieren bis das fertig ist. Mit CONCURRENTLY läuft das nebenbei.

Zusammenfassung#

Partial unique indexes plus RLS-Policy sind das Pattern, das ich in jedem Multi-Tenant-Postgres-Setup verwende. Der Aufwand ist 30 Minuten, der Wert über die Lebensdauer eines Projekts ist enorm. Die Alternative (Duplikate-Bereinigung im Application-Code) skaliert nicht, weil jede Code-Änderung das Risiko hat, neue Klassen von Duplikaten einzuführen.

Wenn du gerade an einem Multi-Tenant-Setup baust und unsicher bist, ob deine Constraints wirklich greifen, schreib mir kurz. Ich schaue gerne in deine Schema-Datei rein. Manchmal sieht man den Bug erst, wenn jemand anderes draufschaut.