# Schema

## Was es ist

Ein Schema ist ein Namensraum innerhalb einer Datenbank: ein Behälter für Tabellen, Views, Datentypen, Funktionen und Operatoren. Derselbe Name kann deshalb konfliktfrei in zwei Schemas vorkommen. Das PostgreSQL-Handbuch vergleicht Schemas mit Verzeichnissen eines Dateisystems. Sie lassen sich allerdings nicht verschachteln: Eine Datenbank enthält Schemas, Schemas enthalten Objekte. Das Wort hat eine zweite, ebenso gebräuchliche, lockerere Bedeutung: Das Schema einer Datenbank ist ihre gesamte Struktur mit jeder Tabelle, Spalte, jedem Schlüssel und jeder Integritätsbedingung. Beide Bedeutungen kommen unten vor. Der Plural Schemas meint fast immer die Namensräume.

Der Abrechnungsbereich einer Anwendung kann in einem eigenen Schema liegen, und eine Sitzung kann wählen, in welchen Schemas sie sucht:

```sql
CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total  numeric(12, 2) NOT NULL
);

SHOW search_path;                  -- "$user", public
SET search_path = billing, public;
SELECT count(*) FROM invoices;     -- now resolves to billing.invoices
```

Ein Name mit vorangestelltem Schema wie `billing.invoices` meint immer dieselbe Tabelle. Ohne Schema wird er über search_path gesucht: eine Liste von Schemas, die der Server bis zum ersten Treffer der Reihe nach durchläuft. Die Standardliste lautet `"$user", public`: ein nach der aktuellen Rolle benanntes Schema, falls vorhanden, danach das Schema public, mit dem jede neue Datenbank beginnt. Im ersten vorhandenen Schema des Pfads legt CREATE TABLE auch eine Tabelle ab, deren Name kein Schema enthält. Deshalb landen Objekte in public, wenn niemand etwas anderes vorgibt. Ein in keinem aufgeführten Schema gefundener Name verursacht einen Fehler, selbst wenn ein anderes Schema derselben Datenbank ihn enthält.

## Warum es wichtig ist

Der erste Grund ist Ordnung. Eine Datenbank mit einigen Hundert Tabellen braucht eine Ebene über der Tabelle. Schemas schaffen sie: Abrechnung in einem, Konten in einem anderen, die Tabellen eines Drittanbieterwerkzeugs in einem dritten, ohne die Namen der anderen zu belegen. Eine Clientverbindung erreicht eine einzige Datenbank. Schemas sind aber nicht starr getrennt: Erlauben es die Rechte, kann eine Rolle Objekte aus jedem Schema der verbundenen Datenbank verwenden, und eine Abfrage kann Tabellen mehrerer Schemas verknüpfen.

Der zweite Grund ist Zugriffskontrolle. USAGE auf einem Schema lässt eine Rolle die enthaltenen Objekte nachschlagen, CREATE lässt sie neue hinzufügen. USAGE zu entziehen blockiert den Zugriff auf die Objekte. Ihre Namen bleiben allerdings aus den Systemkatalogen lesbar, sodass ein Schema die Daten schützt und nicht das Wissen darum, was existiert. `CREATE SCHEMA name AUTHORIZATION role` macht die angegebene Rolle zur Besitzerin eines neuen Schemas. Mit `pg_` beginnende Namen sind dem System vorbehalten.

Der dritte Grund ist Sicherheit, weil Schemas an der Namensauflösung beteiligt sind. Die meisten Namen in Abfragen stehen ohne Schema. Mit den Worten des Handbuchs vertraut man durch Hinzufügen eines Schemas zu search_path praktisch jeder Rolle, die dort Objekte anlegen darf. Eine solche Rolle kann eine gewöhnliche Abfrage Funktionen ihrer Wahl ausführen lassen, als hätte die abfragende Person sie aufgerufen. Sichere Muster halten Schemas, in die andere schreiben dürfen, aus dem Suchpfad eines Nutzers heraus. Ein Beispiel ist ein privates Schema je Nutzer, angelegt mit `CREATE SCHEMA alice AUTHORIZATION alice`, das der Eintrag `"$user"` dann zuerst findet. PostgreSQL 15 führte neue Datenbanken näher an dieses Muster heran: Es entzog das allgemeine Recht, in public Objekte anzulegen, und machte den Datenbankbesitzer über die Rolle pg_database_owner zum Besitzer von public.

Portabilität ist der letzte Punkt. Der SQL-Standard kennt Schemas, aber kein Schema public. Das Handbuch merkt an, dass manche SQL-Systeme möglicherweise überhaupt keine Schemas implementieren. Qualifizierte Namen lassen sich besser übertragen als Code, der auf search_path baut.

## Wie es in der Praxis aussieht

Das Datenmodell in [348 Tabellen in 34 Postgres-Schemas modelliert](/de/portfolio/modeled-348-tables-across-34-postgres-schemas/) verwendet Schemas in der ersten Bedeutung als Ebene über der Tabelle. Die maritime Domäne mit Fachkräften, Unternehmen, Schiffen, Stellen und Bewertungen ist in normalisierten, nach Domäne geordneten PostgreSQL-Schemas aufgebaut: 348 Tabellen in 34 Schemas. Tabellennamen stehen ausnahmslos im Plural, Schemanamen im Singular. Das vermeidet zugleich viele Kollisionen mit reservierten Wörtern. Echte Fremdschlüssel und Constraints sichern die Beziehungen. Eine neue Funktionalität hinzuzufügen heißt meist, das Schema entlang seiner vorhandenen Struktur zu erweitern, statt dagegen zu arbeiten. Laut Seite ist das der einzige Grund, warum 34 Schemas eine Struktur und kein Wildwuchs sind.

Ein Schema kann auch zwei Aufgaben voneinander abgrenzen. In [Stripe und In-App-Käufe mit Berechtigungen verbunden](/de/portfolio/built-stripe-and-in-app-purchase-entitlements/) laufen Stripe-Kartenzahlungen und In-App-Käufe von Apple und Google in einem Zahlungsschema mit 9 Tabellen und 34 Funktionen zusammen. Ein getrenntes Zugriffsschema mit 11 Tabellen und 34 Funktionen beantwortet „Darf dieses Konto das?“, ohne zu wissen, über welchen Store bezahlt wurde. Die bei jeder Anfrage auf dem Server erneut geprüfte Antwort steuert den Zugang zu Unternehmensverzeichnis, Suche und Datenexport. Der Preis sind laut Seite zwei Schemas, wo ein kleineres Produkt eines bräuchte, sowie eine Berechtigungsabfrage bei Anfragen, die sonst ohne diesen Aufwand auskämen.

Die zweite Bedeutung des Worts behandelt [1.022 Migrationen hinter einer Prüfung auf Schemaabweichungen](/de/portfolio/1022-migrations-behind-a-schema-drift-gate/). Die meisten Projekte beschreiben ein Schema zweimal: im Anfangsaufbau, der es von Grund auf erzeugt, und in den Migrationen, die es wachsen ließen. Ohne Prüfung entwickeln sie sich auseinander. Hier ist die Datenbank als 1.022 Migrationen von 036 bis 1102 versioniert. Ein CI-Job baut sie bei jeder Änderung zweimal: einmal frisch und einmal mit allen der Reihe nach eingespielten Migrationen. Danach vergleicht er beides, die vorbefüllten Daten ebenso wie die Struktur. Eine Migration, die Daten in einer Nachschlagetabelle falsch nachträgt, richtet ebenso viel Schaden an wie eine, die eine Spalte vergisst. Nur eines davon erscheint im Schemavergleich. Jede Abweichung lässt den Build mit ausgegebenem Unterschied scheitern. Deshalb muss jede Migration beim Einspielen der gesamten Folge und beim Aufbau von Grund auf funktionieren.

## Wo es schiefgeht

**Eine eingeschleuste Funktion in einem gemeinsamen Schema.** Die Anleitung zu CVE-2018-1058 im PostgreSQL-Wiki zeigt den Angriff: Ein Nutzer legt `lower(varchar)` in public an. Das eingebaute `lower` liegt in pg_catalog und nimmt text entgegen. Für eine varchar-Spalte passt die eingeschleuste Funktion daher genauer. Ein Kollege, der `SELECT lower(full_name) FROM a` ausführt, führt stattdessen den eingeschleusten Code aus. Qualifizieren Sie Aufrufe in sensiblem Code als `pg_catalog.lower`, entziehen Sie CREATE auf public den Rollen, die es nicht brauchen, und listen Sie mit `\df public.*` auf, was bereits vorhanden ist.

**Ein Upgrade behält alte Rechte bei.** Der sicherere Standard von PostgreSQL 15 gilt für neue Cluster und neu angelegte Datenbanken. Ein Clusterupgrade oder das Wiederherstellen eines Dumps behält die vorhandenen public-Rechte bei. Ein Server mit Version 15 oder später kann deshalb weiterhin jeder Rolle das Anlegen von Objekten dort erlauben. Laut Handbuch ist der Standardpfad mit CREATE auf public niemals ein sicheres Muster; zugleich ist er der Standard in Datenbanken, die von 14 oder früher aktualisiert wurden. Führen Sie dort `REVOKE CREATE ON SCHEMA public FROM PUBLIC` aus und prüfen Sie public anschließend auf Objekte mit Namen wie in pg_catalog.

**Eine Tabelle entsteht am falschen Ort.** Eine Migration erstellt `invoices` ohne Schema. Die Tabelle landet im ersten vorhandenen Schema des Suchpfads der ausführenden Rolle. Die Anwendung verbindet sich mit einem anderen Pfad und liest dann eine andere Tabelle oder erhält `relation "invoices" does not exist`. Qualifizieren Sie jeden Namen in Migrationen und prüfen Sie `SELECT current_schema()` in der ausführenden Sitzung.

**Eine Löschung reicht über das Schema hinaus.** `DROP SCHEMA billing` weigert sich, solange das Schema etwas enthält, weil RESTRICT der Standard ist. Mit CASCADE löscht es jedes Objekt darin und jedes davon abhängige Objekt. Das Handbuch warnt, dass dadurch auch Objekte in anderen Schemas als dem genannten verschwinden können. Es rät, DROP zuerst ohne CASCADE auszuführen und die DETAIL-Zeilen zu lesen, die die betroffenen Objekte aufzählen.

**Ein Name, den das System zuerst auflöst.** pg_catalog wird implizit vor den in search_path aufgeführten Schemas durchsucht, wenn es im Pfad fehlt. Ist es ausdrücklich genannt, legt seine Position die Suchreihenfolge fest. Eine eigene Tabelle, deren Name mit `pg_` beginnt, kann verdeckt werden, wenn eine spätere Version eine gleichnamige Systemtabelle ergänzt. Das Handbuch rät deshalb von solchen Namen ab. Reservierte Wörter wie `user` und `order` verursachen eine mildere Form des Problems: Eine so benannte Tabelle braucht in jeder Anweisung doppelte Anführungszeichen.

## Weiterführende Lektüre

- [5.10. Schemas](https://www.postgresql.org/docs/current/ddl-schemas.html) — Das Handbuchkapitel zu qualifizierten Namen, public, search_path, sicheren Nutzungsmustern und Portabilität.
- [CREATE SCHEMA](https://www.postgresql.org/docs/current/sql-createschema.html) — Befehlssyntax, AUTHORIZATION-Klausel, reserviertes Präfix `pg_` und Abweichungen PostgreSQLs vom Standard.
- [5.8. Rechte](https://www.postgresql.org/docs/current/ddl-priv.html) — Was USAGE und CREATE bei einem Schema bedeuten, neben allen übrigen Rechten.
- [E.20. Version 15](https://www.postgresql.org/docs/15/release-15.html) — Die Version, die CREATE auf public bei neuen Datenbanken entzog und bei aktualisierten beibehielt.
- [CVE-2018-1058: Den Suchpfad schützen](https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path) — Die Anleitung des Projekts zum Angriff mit eingeschleuster Funktion, samt Abhilfe und Prüfkommandos für eine Datenbank.

<https://engineer.company/de/glossary/schema/>
