Eine Web-Anwendung mit eigenem Postgres-User ist der Standard — aber wie weit man bei den Rechten zurückgeht, macht den Unterschied zwischen „läuft” und „läuft sicher”. Dieses Rezept zeigt ein robustes Setup: drei getrennte Rollen, eigenes Schema, Read/Write-Privilegien sauber gesplittet, Default-Privileges für künftige Migrations.

01 · Abschnitt

Das Drei-Rollen-Pattern

RolleTypAufgabe
app_ownerNOLOGINBesitzt das Schema und alle Objekte. Migrationen laufen unter dieser Rolle.
app_rwNOLOGINSammelt die Read-/Write-Privilegien. Anwendungs-User sind Mitglieder.
app_userLOGINService-Account, der in der Connection-String steht.

Vorteile dieses Setups:

  • Trennung DDL vs. DML. Die Anwendung kann keine Tabellen droppen oder Schema-Änderungen machen — auch wenn sie einen SQL-Injection-Bug hat.
  • Migrations-Trail klar. Alle Schema-Objekte gehören app_owner. Wer SQL-Owner-Hierarchie auditieren will, hat einen einzigen Anlaufpunkt.
  • Mehrere Anwendungs-Logins ohne Reibung. Wenn morgen ein Worker oder Cron-Job dazukommt, wird er einfach Mitglied von app_rw — keine Rechte-Kopie nötig.
02 · Abschnitt

Setup-Skript

SQL 01-bootstrap-app.sql 
-- 1. Datenbank anlegen (als Cluster-Admin)
CREATE DATABASE myapp;

\c myapp

-- 2. Owner-Rolle (NOLOGIN, Schema-Eigentuemer)
CREATE ROLE app_owner;

-- 3. App-Rolle (NOLOGIN, Container fuer Privilegien)
CREATE ROLE app_rw;

-- 4. Service-Login (LOGIN, fuer die Anwendung)
CREATE ROLE app_user
    WITH LOGIN
         PASSWORD 'changeme'
         CONNECTION LIMIT 50;

-- 5. Mitgliedschaft: app_user erbt von app_rw
GRANT app_rw TO app_user;

-- 6. Datenbank-Owner: app_owner besitzt die DB
ALTER DATABASE myapp OWNER TO app_owner;
SQL 02-bootstrap-schema.sql (als app_owner) 
SET ROLE app_owner;

-- 1. Eigenes Schema (statt public!)
CREATE SCHEMA app;

-- 2. App-Rolle darf Schema benutzen
GRANT USAGE ON SCHEMA app TO app_rw;

-- 3. Connect-Recht auf der Datenbank
GRANT CONNECT ON DATABASE myapp TO app_user;

-- 4. Default-Privileges fuer ZUKUENFTIGE Migrations:
--    alle Tabellen, die app_owner anlegt, bekommen
--    automatisch SELECT/INSERT/UPDATE/DELETE fuer app_rw
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO app_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT EXECUTE ON FUNCTIONS TO app_rw;

-- 5. search_path fuer den Service-User: app zuerst,
--    public als Fallback fuer Extensions wie pgcrypto
ALTER ROLE app_user SET search_path = app, public;

Damit ist das Grund-Setup fertig. Migrations laufen jetzt mit SET ROLE app_owner; (oder verbinden direkt als app_owner, falls man dem ein Login gibt) — alle erzeugten Tabellen gehören app_owner und vergeben automatisch die richtigen Rechte an app_rw.

03 · Abschnitt

Migration ausführen

SQL 100-create-orders.sql (Beispiel-Migration) 
SET ROLE app_owner;

CREATE TABLE orders (
    id          serial primary key,
    customer_id integer not null,
    total       numeric(10,2) not null,
    created_at  timestamptz not null default now()
);

CREATE INDEX orders_customer_idx ON orders (customer_id);

Du musst hier nichts an Privilegien setzen — die Default-Privileges greifen automatisch, sobald die Tabelle existiert. app_user (über app_rw) darf SELECT/INSERT/UPDATE/DELETE auf orders, ohne dass jemand das explizit grantet.

Test:

SQL Als app_user verbinden und testen 
$ psql -h localhost -U app_user -d myapp
myapp=> SELECT current_user, current_schema();
 current_user | current_schema
--------------+----------------
 app_user     | app

myapp=> INSERT INTO orders (customer_id, total) VALUES (1, 99.95);
INSERT 0 1

myapp=> DROP TABLE orders;
ERROR:  must be owner of table orders

Genau das wollten wir: schreiben darf er, droppen nicht.

04 · Abschnitt

Variante: Read-Only-User für Reporting

Wenn neben dem Writer auch ein Reporting-User dazu soll:

SQL 
CREATE ROLE app_ro;
CREATE ROLE reporting LOGIN PASSWORD 'changeme'
    CONNECTION LIMIT 5;

GRANT app_ro TO reporting;
GRANT CONNECT ON DATABASE myapp TO reporting;

SET ROLE app_owner;
GRANT USAGE ON SCHEMA app TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT ON TABLES TO app_ro;

ALTER ROLE reporting SET search_path = app, public;

reporting darf lesen, aber nicht schreiben — perfekt für BI-Tools, ad-hoc-Analysen, Read-Replicas. Alternativ ist pg_read_all_data noch knapper, aber das öffnet alle Schemas (auch pg_catalog und potenzielle Sensitive-Daten in anderen Schemas).

05 · Abschnitt

Connection-String

In der Anwendung:

Bash .env 
DATABASE_URL="postgresql://app_user:changeme@db.example.com:5432/myapp?sslmode=require"

Wichtig: sslmode=require für alles, was nicht localhost ist. prefer (Default) lehnt sich nicht zurück, wenn der Server SSL ablehnt — require schon.

06 · Abschnitt

Besonderheiten

Warum nicht einfach pg_write_all_data?

Die Predefined Role klingt nach Abkürzung — aber sie umfasst alle Schemas, auch zukünftige. Wenn jemand morgen ein internal-Schema mit Sensitive-Daten anlegt, hätte deine Webapp dort plötzlich Schreibrechte. Das eigene Schema + explizite Mitgliedschaft ist sauberer und expliziter.

Kein eigener Schema-Name? Dann zumindest aus public raus.

Wenn die App in public Tabellen anlegt, kollidieren irgendwann andere Tools (Migrations-Tools, Extensions, Backup-Skripte) damit. Ein eigenes Schema kostet zwei Zeilen und vermeidet alle Konflikte. Ein guter Name: app, der App-Name selbst, oder <feature> bei modular-aufgeteilten Projekten.

ALTER DEFAULT PRIVILEGES ist FOR ROLE-spezifisch.

Wenn deine Migrations unter einem anderen User laufen als der, der ALTER DEFAULT PRIVILEGES ausgeführt hat, greifen die Defaults nicht. Das FOR ROLE app_owner-Stück ist Pflicht — sonst funktioniert das Setup für genau die Rolle, die es eingerichtet hat (etwa postgres), aber nicht für deine Migrations-Rolle.

search_path fuer Functions explizit setzen.

Wenn deine App eigene PL/pgSQL-Functions hat: in jeder davon SET search_path = app, pg_temp setzen — entweder per SET im Function-Body oder als Function-Property (CREATE FUNCTION … SET search_path = …). Sonst macht ein bösartiger search_path-Wert in der Session-Konfiguration die Funktion verwundbar.

Connection-Pooling steht ueber dem Setup.

50 Connections pro App-Instanz × 5 Instanzen = 250 Connections am Server — das ist viel. Ein PgBouncer davor verteilt eine kleine Pool-Größe (z. B. 50) auf viele Client-Connections. Für Webapps in Produktion fast immer eingesetzt; siehe Kapitel Performance.

Bei Major-DB-Updates Mitgliedschaften pruefen.

Bei pg_dump/pg_restore-Migrationen kann es passieren, dass GRANT app_rw TO app_user nicht 1:1 erhalten bleibt — vor allem, wenn nur einzelne Schemas gedumpt werden (--schema-only nimmt keine Rollen mit). Nach jedem Restore-Schritt: \du und Mitgliedschaften prüfen.

07 · Abschnitt

Weiterführende Ressourcen

Externe Quellen

/ Weiter

Zurück zu Server-Administration

Zur Übersicht