Datenbankzugang (Soll-Architektur)

Kurzantwort

Die sicherste Variante ohne VPN ist nicht „Passwort über TLS im Internet“, sondern:

1. PostGIS nie öffentlich (kein Port 3401 ins Netz, nur Docker-Netz).
2. Apps (Editor, Dashboard, FastAPI) verbinden sich nur intern zur DB — Clients sehen Postgres nicht.
3. QGIS und andere Postgres-Clients gehen über einen Auth-Proxy, der das Postgres-Protokoll spricht, mit gegenseitigem TLS (mTLS) und pro Person/Gerät einem Client-Zertifikat (kurzlebig, widerrufbar).

Ja: Keys + Auth-Proxy, der auf PostGIS weiterleitet, ist genau dieses Muster. Die „Keys“ sind X.509-Client-Zertifikate, keine API-Keys im HTTP-Sinn — QGIS kann keine Authorization-Header auf Port 5432 senden.

Ist vs. Soll

flowchart LR
  subgraph gone [Ist — abgeschaltet]
    Q1[QGIS] -->|plain TCP + Passwort| WG[WireGuard]
    WG --> T1[Traefik TCP 3401]
    T1 --> PG1[(PostGIS)]
  end

flowchart TB
  subgraph public [Öffentlich HTTPS / TLS]
    ED[Linetra Editor]
    DASH[Dashboard]
    QGIS[QGIS]
  end
  ED -->|Session / HTTPS| API[FastAPI]
  DASH -->|Session / HTTPS| API
  QGIS -->|TLS 1.3 + Client-Zertifikat| PROXY[PgBouncer]
  API -->|nur Docker-Netz| PG[(PostGIS)]
  PROXY -->|nur Docker-Netz| PG

VPN darf als zusätzliche Hülle für andere NAS-Dienste bleiben, ist aber keine Voraussetzung für QGIS oder die Apps.

Warum nicht nur TLS + Passwort?

Editor und Dashboard brauchen keinen DB-Proxy: Stackregel ist HTTPS zur FastAPI (Lastenheft Q-02). Einfache Editor-Nutzer bekommen nur ein Web-Konto; sie schreiben über die API in PostGIS, ohne Client-Zertifikat. Siehe Linetra Editor nutzen.

Was der Proxy tun muss

QGIS spricht Postgres-Wire-Protocol. Traefik-HTTP, OAuth2-Proxy oder nginx-HTTP helfen hier nicht.

Der Proxy muss:

• SSLRequest / TLS von libpq verstehen (oder Postgres 17 sslnegotiation=direct)
• Client-Zertifikat prüfen (eigene CA)
• Verbindung nur dann nach intern zu PostGIS öffnen
• das Zertifikat auf eine Mandanten-DB-Rolle abbilden: CN = {slug}_edit oder {slug}_read (ADR-0031)

Live: PgBouncer (client_tls_sslmode=verify-full, auth_type=cert). Ungeeignet: Traefik TLS-Termination auf einem TCP-Entrypoint — libpq sendet zuerst SSLRequest, kein TLS-ClientHello.

QGIS: Host=… Port=5432 SSL=verify-full
      SSL-Zertifikat + Schlüssel + CA
        → PgBouncer (TLS in PgBouncer, Direct-Publish)
            → postgres:5432 (Compose-Netz, unpublished)

PostGIS: nur Compose-Netz. Superuser postgres nicht über den Proxy.

Zertifikate („Keys“)

QGIS: Verbindung → SSL → Zertifikat, Schlüssel, CA-Datei. Das ist der native Key-Weg. Ausstellung: ./scripts/pki/issue-client-cert.sh edit <name>.

Passwörter intern zwischen Proxy und Postgres zusätzlich behalten — nicht statt mTLS nach außen. pgbouncer.get_auth liefert denselben HMAC-Klartext wie die Mandanten-Rollen (nicht den SCRAM-Hash aus pg_authid); sonst antwortet PgBouncer mit wrong password type. Shared-Rollen linetra_edit / linetra_read stehen weiter als Klartext in userlist.txt.

Abnahme (Lastenheft INF-04–INF-06)

Siehe Lastenheft Infrastruktur. Kurz:

• Von außen kein Login an PostGIS ohne gültiges Client-Zertifikat (bzw. ohne App-Session auf der API).
• Port der Datenbank ist nicht im Internet.
• Entzug eines Zertifikats sperrt den Client, ohne das VPN-Gitter umzubauen.

Übergang

1. Proxy + CA intern, QGIS testweise mit Zertifikat zusätzlich zum VPN.
2. sslmode=verify-full verbindlich.
3. Erledigt: Öffentliches Publish von 3401 / Traefik-TCP auf wg0 abgeschaltet; der Proxy trägt.
4. Geteiltes QGIS-Passwort abschaffen, sobald Rollen am Zertifikat hängen.

Lokale Entwicklung: Compose darf Postgres weiter auf localhost:3401 legen — nicht das Server-Soll.

Gleiche Live-DB von der Workstation: lokale FastAPI darf denselben PgBouncer-Pfad nutzen wie QGIS (DATABASE_HOST = öffentlicher Hostname, DATABASE_SSLMODE=verify-full, Client-Zertifikat). Production-FastAPI bleibt intern postgres:5432. Siehe ADR-0013.

Erstmalige Mandanten-Migration

Bestehende GIS-/Stammdaten liegen heute in der Control-DB linetra. Einmalig in den ersten Mandanten ziehen, danach Fachtabellen aus der Control-DB entfernen:

1. Control-Alembic inkl. 0011_tenants: docker compose exec backend uv run alembic upgrade head. PgBouncer: ./scripts/create_app_roles.sh (legt pgbouncer.get_auth an) und Wildcard * in pgbouncer/pgbouncer.ini.
2. Ersten Kunden anlegen mit Kopie der Fachdaten: POST /api/v1/tenants mit { "slug": "…", "name": "…", "copy_control_data": true } oder ./scripts/provision_tenant.sh <slug> --copy-data plus Catalog-POST.
3. Web-User legen selbst ein Organisationskonto an (POST /tenants); app_users.tenant_id wird gesetzt. Plattform-Admins dürfen tenant_id null behalten.
4. QGIS neu verbinden: nach API-Login liefert /auth/me dbname=linetra_<slug> und CN {slug}_edit. Cert-ZIP neu ausstellen.
5. Wenn die Mandanten-DB die Fachdaten hält: ./scripts/strip_control_fachdaten.sh (löscht Fachtabellen in linetra, behält app_users / tenants / Tokens).
6. Weitere Mandanten ohne Kopie: POST /tenants (copy_control_data false) klont linetra_tenant_template. Schema-Updates: ./scripts/alembic_upgrade_all.sh.

Lokal bleibt LINETRA_SHARED_DB=true (Compose-Dev): eine physische DB, logischer Mandant dev.