Schemas und search_path

Schema = Namespace

Eine Datenbank besteht aus mehreren Schemas. Jeder Schema-Eintrag ist ein eigener Container für Objekte:

CREATE SCHEMA app;
CREATE TABLE app.orders (id serial primary key, total numeric);
CREATE TABLE app.customers (id serial primary key, email text);

Ein Postgres-Cluster bringt von Haus aus mehrere Schemas mit:

pg_catalog ist immer verfügbar — auch ohne USAGE-Privileg.

search_path — wer wird zuerst gefunden?

Wenn du SELECT * FROM orders; schreibst, ohne ein Schema zu nennen, sucht Postgres die Tabelle im search_path ab — der Reihe nach.

SHOW search_path;
--  search_path
-- ---------------
--  "$user", public

SET search_path TO app, public;

"$user" ist ein Sonderwert: Postgres ersetzt ihn zur Laufzeit durch den Namen der aktuellen Rolle. Wenn ein User alice heißt und ein Schema alice existiert, wird das durchsucht. Existiert es nicht, wird der Eintrag still übersprungen.

Mit SET search_path TO app, public; legst du fest: erst in app suchen, dann in public.

Schreibvorgänge (CREATE TABLE foo … ohne Schema-Präfix) landen immer im ersten existierenden Schema des search_path — also bei obigem Setting in app.

search_path persistent setzen

SET gilt nur für die aktuelle Session. Für persistenten Effekt:

ALTER ROLE app_user SET search_path = app, public;

ALTER DATABASE myapp SET search_path = app, public;

Beide Varianten greifen ab der nächsten Session. Wer sich gerade verbunden hat, hat noch den alten Wert.

Auch in Funktionen kann der search_path lokal gesetzt werden:

CREATE FUNCTION calculate_tax(amount numeric)
RETURNS numeric
LANGUAGE sql
SET search_path = app, pg_temp
AS $$
    SELECT amount * (SELECT rate FROM tax_rates LIMIT 1);
$$;

Das ist insbesondere bei SECURITY DEFINER-Funktionen Pflicht — sonst öffnet man Lücken (siehe Pitfall unten).

Das public-Schema — Geschichte und PG 15

Bis PostgreSQL 14 hatte das public-Schema sehr permissive Defaults: jede Rolle (PUBLIC) durfte dort CREATE und USAGE. Das war bequem für Einsteiger und gefährlich für alle anderen, die mit mehreren Usern arbeiteten.

Seit PostgreSQL 15 ist public deutlich restriktiver:

Praktische Folge nach Major-Upgrade: Anwendungen, die ihre Tabellen im public-Schema anlegen wollen, brauchen entweder ein eigenes Schema (sauberer) oder explizites GRANT CREATE ON SCHEMA public TO app_user;.

Schema-basierte Mandantentrennung

Eine elegante Methode für Multi-Tenancy: ein Schema pro Mandant, alle Tabellen identisch strukturiert.

CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Identisches Schema-Layout in beiden:
CREATE TABLE tenant_acme.orders   (id serial primary key, total numeric);
CREATE TABLE tenant_globex.orders (id serial primary key, total numeric);

Pro Login-Rolle ein search_path:

CREATE ROLE acme_user LOGIN PASSWORD 'changeme';
ALTER ROLE acme_user SET search_path = tenant_acme;

acme_user sieht nur Daten aus tenant_acme — dieselbe Anwendung, dieselben Queries, andere Daten.

Vorteile:

• starke Isolation auf DB-Ebene (kein versehentlicher Cross-Tenant-Zugriff durch fehlerhafte WHERE-Klausel)
• pro-Tenant-Backup einfacher (pg_dump --schema=tenant_acme)
• pro-Tenant-Migration möglich

Nachteile:

• Schema-Updates müssen für jeden Tenant durchgeführt werden (Migrations-Tooling muss das können)
• Anzahl Tenants begrenzt (tausende Schemas können den Katalog aufblähen)
• Cross-Tenant-Reporting wird umständlich

In der Praxis: gut für mittlere Tenant-Zahlen (10–500), nicht für SaaS mit zehntausenden Kunden — dort ist Row-Level Security mit Tenant-ID-Spalte oft besser.

Weiterführende Ressourcen

Externe Quellen

• Schemas – PostgreSQL Documentation
• The Schema Search Path
• SET search_path – PostgreSQL Documentation
• Release Notes 15: public schema permissions
• Writing SECURITY DEFINER Functions Safely

Verwandte Artikel

• Rollen und Privilegien — Übersicht
• GRANT/REVOKE — Grundlagen
• Default Privileges
• Schema für Mandant anlegen