PostgreSQL

<h3 id="bkmrk-1.1.-einleitung">1.1. Einleitung</h3>
PostgreSQL ist ein relationales Open-Source-Datenbanksystem. Es eignet sich sowohl für klassische Anwendungsdatenbanken als auch für komplexere Fachverfahren.
Gegenüber MySQL bietet PostgreSQL in der Regel einen größeren Funktionsumfang für anspruchsvollere Datenmodelle. Dazu gehören unter anderem eine stärkere Orientierung am SQL-Standard, eine feinere Rechteverwaltung, Schemas zur logischen Trennung von Objekten sowie erweiterte Mechanismen für Transaktionen und Erweiterungen.
Gegenüber Oracle Database ist PostgreSQL lizenzkostenfrei und offen. Gleichzeitig stellt es viele vergleichbare Grundkonzepte bereit, zum Beispiel Rollen, Schemas, Views, Trigger und Transaktionen. Oracle ist dagegen stärker proprietär geprägt und wird häufig in großen Enterprise-Umgebungen mit herstellerspezifischen Werkzeugen und Lizenzmodellen betrieben.
In Docker- und Ansible-basierten Umgebungen ist PostgreSQL besonders attraktiv, weil es sich gut automatisieren, standardisieren und wartbar betreiben lässt.

<h3 id="bkmrk-1.2.-allgemein">1.2. Allgemein</h3>
<ul>
<li>Rollout per Ansible-Rolle</li>
<li>basiert auf Docker</li>
</ul>
Beispielsystem:
<ul>
<li>Server: <code>srv3306.lej.eis.network</code></li>
<li>Port: <code>5432</code></li>
<li>Datenbank: <code>postgres</code></li>
<li>Benutzer: <code>postgres</code></li>
<li>Passwort: siehe Serverpool oder <code>docker-compose</code></li>
</ul>
Ziel ist eine saubere Trennung zwischen administrativen Rollen, Applikationsrollen, Datenbanken, Schemas sowie Dump- und Restore-Prozessen.

<h3 id="bkmrk-1.3.-rollen-und-namenskonzept">1.3. Rollen- und Namenskonzept</h3>
Für den Betrieb wird empfohlen, administrative Rollen und Applikationsrollen sauber zu trennen.

Administrative Rolle
Die administrative Rolle ist für Verwaltungsaufgaben zuständig, zum Beispiel:
<ul>
<li>Datenbank anlegen</li>
<li>Schema anlegen</li>
<li>Rechte vergeben</li>
<li>Eigentümer von Datenbank oder Schema sein</li>
<li>administrative Änderungen innerhalb der jeweiligen Anwendung durchführen</li>
</ul>
Beispiele für Namenskonventionen:
<ul>
<li><code>sachdev</code></li>
<li><code>sachtest</code></li>
<li><code>sachpilot</code></li>
<li><code>sachprod</code></li>
<li><code>beratungdev</code></li>
<li><code>beratungtest</code></li>
<li><code>beratungpilot</code></li>
<li><code>beratungprod</code></li>
</ul>

Applikationsrolle
Die Applikationsrolle wird von der Anwendung im Tagesbetrieb verwendet.
Typische Rechte:
<ul>
<li><code>LOGIN</code></li>
<li><code>CONNECT</code></li>
<li><code>USAGE</code> auf das Anwendungsschema</li>
<li><code>SELECT</code></li>
<li><code>INSERT</code></li>
<li><code>UPDATE</code></li>
<li><code>DELETE</code></li>
<li>bei Bedarf <code>USAGE, SELECT</code> auf Sequenzen</li>
<li>nur bei echtem Bedarf <code>CREATE</code> im Anwendungsschema</li>
</ul>
Beispiele für Namenskonventionen:
<ul>
<li><code>sachworkdev</code></li>
<li><code>sachworktest</code></li>
<li><code>sachworkpilot</code></li>
<li><code>sachworkprod</code></li>
<li><code>beratungworkdev</code></li>
<li><code>beratungworktest</code></li>
<li><code>beratungworkpilot</code></li>
<li><code>beratungworkprod</code></li>
</ul>
Best Practice: Rollenattribute wie <code>CREATEDB</code> oder <code>CREATEROLE</code> nur vergeben, wenn sie tatsächlich benötigt werden. Eine allgemeine Vergabe von <code>CREATEROLE</code> ist in den meisten Betriebsfällen nicht erforderlich.

<h3 id="bkmrk-1.4.-beispiel-keycloak-datenbank">1.4. Beispiel: Keycloak-Datenbank</h3>
Administrative Rolle anlegen
<pre><code>CREATE USER keycloaktest WITH PASSWORD '<ADMIN_PASSWORT>';</code></pre>

Nur wenn die Rolle selbst Datenbanken anlegen soll:
<pre><code>CREATE USER keycloaktest WITH LOGIN CREATEDB PASSWORD '<ADMIN_PASSWORT>';</code></pre>

<code>CREATEROLE</code> sollte nur vergeben werden, wenn die Rolle tatsächlich weitere Rollen verwalten muss.

Datenbank anlegen
<pre><code>CREATE DATABASE keycloakdb;</code></pre>

Optional Eigentümer setzen:
<pre><code>ALTER DATABASE keycloakdb OWNER TO keycloaktest;</code></pre>

Verbindungsrecht vergeben
<pre><code>GRANT CONNECT ON DATABASE keycloakdb TO keycloaktest;</code></pre>

Applikationsrolle anlegen
<pre><code>CREATE USER keycloakworktest WITH PASSWORD '<APP_PASSWORT>';
GRANT CONNECT ON DATABASE keycloakdb TO keycloakworktest;</code></pre>

<h3 id="bkmrk-1.5.-arbeiten-mit-schemas">1.5. Arbeiten mit Schemas</h3>
Vorhandene Schemas anzeigen
<pre><code>\dn</code></pre>

Eigenes Schema anlegen
<pre><code>CREATE SCHEMA keycloak AUTHORIZATION keycloaktest;</code></pre>

Das Schema gehört damit der administrativen Rolle. Das ist meist sauberer, als das Schema direkt der Applikationsrolle zu überlassen.

Rechte auf Schema und Tabellen setzen
<pre><code>GRANT USAGE ON SCHEMA keycloak TO keycloakworktest;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA keycloak TO keycloakworktest;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA keycloak TO keycloakworktest;</code></pre>

Damit neu angelegte Tabellen und Sequenzen ebenfalls passend berechtigt werden:
<pre><code>ALTER DEFAULT PRIVILEGES FOR ROLE keycloaktest IN SCHEMA keycloak
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO keycloakworktest;

ALTER DEFAULT PRIVILEGES FOR ROLE keycloaktest IN SCHEMA keycloak
GRANT USAGE, SELECT ON SEQUENCES TO keycloakworktest;</code></pre>

Wichtiger Hinweis zu GRANT
Dieser Befehl ist fachlich nicht korrekt:
<pre><code>GRANT SELECT, INSERT, UPDATE, DELETE ON keycloakdb TO keycloakworktest;</code></pre>

<code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code> und <code>DELETE</code> gelten für Tabellen oder Views, nicht für die Datenbank selbst.

Korrekt ist auf Datenbankebene zum Beispiel:
<pre><code>GRANT CONNECT ON DATABASE keycloakdb TO keycloakworktest;</code></pre>

<h3 id="bkmrk-1.6.-ssl-gesicherte-verbindungen">1.6. SSL-gesicherte Verbindungen</h3>
Je nach Umgebung werden Verbindungsinformationen und Zertifikate getrennt bereitgestellt.

<pre><code>database_public_ip = 35.198.170.75

database_ca_certificates = {
shareddb = <CA-Zertifikat>
}

database_private_keys = {
shareddb = <Client-Private-Key>
}

database_public_keys = {
shareddb = <Client-Zertifikat>
}</code></pre>

Für den Client werden typischerweise benötigt:
<ul>
<li>Root-CA-Zertifikat</li>
<li>Client-Zertifikat</li>
<li>privater Schlüssel</li>
<li>Hostname oder IP</li>
<li>Port</li>
<li>Benutzername</li>
<li>Datenbankname</li>
</ul>

Beispielhafte <code>psql</code>-Verbindung:
<pre><code>psql -W "port=5432 host=35.198.170.75 user=keycloaktest dbname=keycloakdb sslcert=client.crt sslkey=client.key sslrootcert=root.crt sslmode=verify-ca"</code></pre>

Für produktive Umgebungen ist <code>verify-ca</code> oder <code>verify-full</code> in der Regel sinnvoller als <code>prefer</code>.

<h3 id="bkmrk-1.7.-import-und-restore">1.7. Import und Restore</h3>
Die Dump-Files werden je nach Quelle entweder als SQL-Skript, Custom-Format-Archiv, Directory-Format oder TAR-Archiv bereitgestellt.

Wichtige Unterscheidung:
<ul>
<li>Plain SQL wird mit <code>psql</code> eingespielt.</li>
<li>Custom-/Directory-/Tar-Dumps aus <code>pg_dump</code> werden mit <code>pg_restore</code> eingespielt.</li>
</ul>

Dateien vorbereiten
<pre><code>mkdir /srv/postgresql/data/import_dump/
cp -r ../exx/z_backups/postgresql/rollenerfassung* /srv/postgresql/data/import_dump/</code></pre>

Berechtigungen setzen
Falls der PostgreSQL-Container mit UID/GID <code>70:70</code> arbeitet:
<pre><code>chown -R 70:70 /srv/postgresql/data/import_dump</code></pre>

In den Container wechseln
<pre><code>docker exec -it postgres bash</code></pre>

Beispielpfad im Container:
<pre><code>cd /var/lib/postgresql/data/import_dump/rollenerfassung_raspberrypi/</code></pre>

Restore-SQL prüfen
<pre><code>sed -e 's/\$\$PATH\$\$/\/tmp\/dvdrental/g' restore.sql | grep --color dvdrental</code></pre>

Zusätzlich kann der Anfang der Datei geprüft werden:
<pre><code>head -n 50 restore.sql</code></pre>

So lässt sich feststellen:
<ul>
<li>wie die Datenbank heißt</li>
<li>wie die Rolle heißt</li>
<li>welche Pfade ersetzt werden müssen</li>
</ul>

<h3 id="bkmrk-1.8.-beispiel-rollenerfassung">1.8. Beispiel: Rollenerfassung</h3>
Rolle anlegen
<pre><code>CREATE USER rollenerfassung WITH PASSWORD '<PASSWORT>';</code></pre>

Datenbanken anlegen
Für allgemeine Dokumentation besser ohne starre Locale-Vorgabe arbeiten, da diese plattform- und umgebungsabhängig ist:
<pre><code>CREATE DATABASE rollenerfassung_server TEMPLATE template0 ENCODING 'UTF8';
CREATE DATABASE rollenerfassung_raspberrypi TEMPLATE template0 ENCODING 'UTF8';</code></pre>

Wenn in einer konkreten Umgebung eine bestimmte Locale erforderlich ist, sollte sie explizit passend zur Zielplattform dokumentiert werden.

Eigentümer setzen
<pre><code>ALTER DATABASE rollenerfassung_raspberrypi OWNER TO rollenerfassung;
ALTER DATABASE rollenerfassung_server OWNER TO rollenerfassung;</code></pre>

Connect-Rechte vergeben
<pre><code>GRANT CONNECT ON DATABASE rollenerfassung_server TO rollenerfassung;
GRANT CONNECT ON DATABASE rollenerfassung_raspberrypi TO rollenerfassung;</code></pre>

<h3 id="bkmrk-1.9.-restore-eines-sql-skripts-mit-psql">1.9. Restore eines SQL-Skripts mit psql</h3>
Wenn <code>restore.sql</code> ein Plain-SQL-Skript ist, kann der Pfad beim Einspielen ersetzt werden.

<pre><code>sed -e 's/\$\$PATH\$\$/\/var\/lib\/postgresql\/data\/import_dump\/rollenerfassung/g' restore.sql | psql -h localhost -p 5432 -U postgres -d rollenerfassung_server</code></pre>

Dabei wird:
<ul>
<li><code>$$PATH$$</code> durch den echten Pfad ersetzt</li>
<li>das Ergebnis direkt an <code>psql</code> übergeben</li>
<li>das SQL-Skript in die Zieldatenbank eingespielt</li>
</ul>

<h3 id="bkmrk-1.10.-restore-eines-archivs-mit-pg_restore">1.10. Restore eines Archivs mit pg_restore</h3>
Wenn ein Dump mit <code>pg_dump -Fc</code> oder in einem anderen Archivformat erstellt wurde, wird <code>pg_restore</code> verwendet.

<pre><code>docker exec -i postgres pg_restore --clean -U postgres -d rollenerfassung_server < restore.dump</code></pre>

Erklärung
<ul>
<li><code>docker exec -i postgres</code> führt den Befehl im Container aus</li>
<li><code>pg_restore</code> stellt einen Archiv-Dump wieder her</li>
<li><code>--clean</code> entfernt vorhandene Objekte vor dem Restore</li>
<li><code>-U postgres</code> nutzt die Rolle <code>postgres</code></li>
<li><code>-d rollenerfassung_server</code> gibt die Zieldatenbank an</li>
</ul>

<h3 id="bkmrk-1.11.-dump-erstellen">1.11. Dump erstellen</h3>
Ein Dump im Custom-Format kann aus dem Container heraus mit <code>pg_dump</code> erzeugt werden.

<pre><code>docker exec $CONTAINER pg_dump -Fc -U postgres $DATABASE > $OUTPUT/$DATABASE-$DATE.dump</code></pre>

Erklärung
<ul>
<li><code>$CONTAINER</code> ist der Name des PostgreSQL-Containers</li>
<li><code>-Fc</code> erzeugt ein Custom-Format für <code>pg_restore</code></li>
<li><code>-U postgres</code> verwendet die Rolle <code>postgres</code></li>
<li><code>$DATABASE</code> ist die zu sichernde Datenbank</li>
<li><code>$OUTPUT/$DATABASE-$DATE.dump</code> ist die Zieldatei</li>
</ul>

Hinweis: Für Custom-Format ist <code>.dump</code> oder <code>.backup</code> als Dateiendung verständlicher als <code>.psql</code>.

<h3 id="bkmrk-1.12.-einfacher-funktionstest">1.12. Einfacher Funktionstest</h3>
Nach Einrichtung von Rolle und Rechten kann ein Funktionstest durchgeführt werden.

Testtabelle anlegen
<pre><code>CREATE TABLE persons (
personid int PRIMARY KEY,
firstname varchar(50),
lastname varchar(50)
);</code></pre>

Tabellen anzeigen
<pre><code>\d</code></pre>

Datensatz einfügen
<pre><code>INSERT INTO persons VALUES (1, 'Alex', 'Hofmann');</code></pre>

Daten prüfen
<pre><code>SELECT * FROM persons;</code></pre>

Tabelle wieder löschen
<pre><code>DROP TABLE persons;</code></pre>

Sitzung beenden
<pre><code>\q</code></pre>

<h3 id="bkmrk-1.13.-empfohlenes-betriebsmodell">1.13. Empfohlenes Betriebsmodell</h3>
Administrative Rolle
<ul>
<li>Datenbanken anlegen, wenn erforderlich</li>
<li>Schemas anlegen</li>
<li>Rechte vergeben</li>
<li>Eigentümer von Datenbank oder Schema sein</li>
<li>administrative Änderungen innerhalb der Anwendung durchführen</li>
</ul>

Applikationsrolle
<ul>
<li>Verbindung zur Datenbank herstellen</li>
<li>Daten lesen und schreiben</li>
<li>keine unnötigen administrativen Rechte besitzen</li>
</ul>

Dadurch wird vermieden, dass Anwendungen mit überprivilegierten Rollen laufen.

<h3 id="bkmrk-1.14.-sicherheitshinweise">1.14. Sicherheitshinweise</h3>
<ul>
<li>Keine echten Passwörter im Wiki speichern</li>
<li>Keine privaten Schlüssel im Klartext dokumentieren</li>
<li>Zertifikate und Schlüssel nur über sichere Ablagen bereitstellen</li>
<li>administrative Rollen und Applikationsrollen trennen</li>
<li>Rechte nach dem Prinzip der minimal nötigen Berechtigung vergeben</li>
<li><code>CREATEROLE</code> und <code>CREATEDB</code> nur bei echtem Bedarf vergeben</li>
<li>Restore-Befehle zuerst in Testumgebungen prüfen</li>
<li>vor produktiven Restores immer ein aktuelles Backup erstellen</li>
<li>für produktive SSL-Verbindungen <code>verify-ca</code> oder <code>verify-full</code> bevorzugen</li>
</ul>

<h3 id="bkmrk-1.15.-zusammenfassung">1.15. Zusammenfassung</h3>
Für PostgreSQL in Docker-Umgebungen empfiehlt sich ein einheitliches Vorgehen:
<ul>
<li>Rollout per Ansible-Rolle</li>
<li>Trennung von administrativen Rollen und Applikationsrollen</li>
<li>klare Namenskonventionen</li>
<li>gezielte Rechtevergabe</li>
<li>saubere Trennung zwischen <code>psql</code>-Restore und <code>pg_restore</code></li>
<li>dokumentierte Pfade und Container-Befehle</li>
<li>optional SSL-gesicherte Verbindungen mit strenger Zertifikatsprüfung</li>
</ul>
So bleibt die Umgebung übersichtlich, wartbar und sicher.