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