Skip to main content

PostgreSQL

1. Einleitung

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.

1.1. Allgemein

  • Rollout per Ansible-Rolle
  • basiert auf Docker

Beispielsystem:

  • Server: srv3306.lej.eis.network
  • Port: 5432
  • Datenbank: postgres
  • Benutzer: postgres
  • Passwort: siehe Serverpool oder docker-compose

Ziel ist eine saubere Trennung zwischen administrativen Rollen, Applikationsrollen, Datenbanken, Schemas sowie Dump- und Restore-Prozessen.

1.2. Rollen- und Namenskonzept

Für den Betrieb wird empfohlen, administrative Rollen und Applikationsrollen sauber zu trennen.

1.2.1. Administrative Rolle

Die administrative Rolle ist für Verwaltungsaufgaben zuständig, zum Beispiel:

  • Datenbank anlegen
  • Schema anlegen
  • Rechte vergeben
  • Eigentümer von Datenbank oder Schema sein
  • administrative Änderungen innerhalb der jeweiligen Anwendung durchführen

Beispiele für Namenskonventionen:

  • sachdev
  • sachtest
  • sachpilot
  • sachprod
  • beratungdev
  • beratungtest
  • beratungpilot
  • beratungprod

Best Practice: Rollenattribute wie CREATEDB oder CREATEROLE nur vergeben, wenn sie tatsächlich benötigt werden. Eine allgemeine Vergabe von CREATEROLE ist in den meisten Betriebsfällen nicht erforderlich.

1.2.2. Applikationsrolle

Die Applikationsrolle wird von der Anwendung im Tagesbetrieb verwendet.

Typische Rechte:

  • LOGIN
  • CONNECT
  • USAGE auf das Anwendungsschema
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • bei Bedarf USAGE, SELECT auf Sequenzen
  • nur bei echtem Bedarf CREATE im Anwendungsschema

Beispiele für Namenskonventionen:

  • sachworkdev
  • sachworktest
  • sachworkpilot
  • sachworkprod
  • beratungworkdev
  • beratungworktest
  • beratungworkpilot
  • beratungworkprod

1.3. Beispiel: Keycloak-Datenbank

1.3.1. Administrative Rolle anlegen

CREATE USER keycloaktest WITH PASSWORD '<ADMIN_PASSWORT>';

Nur wenn die Rolle selbst Datenbanken anlegen soll:

CREATE USER keycloaktest WITH LOGIN CREATEDB PASSWORD '<ADMIN_PASSWORT>';

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

1.3.2. Datenbank anlegen

CREATE DATABASE keycloakdb;

Optional Eigentümer setzen:

ALTER DATABASE keycloakdb OWNER TO keycloaktest;

1.3.3. Verbindungsrecht vergeben

GRANT CONNECT ON DATABASE keycloakdb TO keycloaktest;

1.3.4. Applikationsrolle anlegen

CREATE USER keycloakworktest WITH PASSWORD '<APP_PASSWORT>';
GRANT CONNECT ON DATABASE keycloakdb TO keycloakworktest;

1.4. Arbeiten mit Schemas

1.4.1. Vorhandene Schemas anzeigen

\dn

1.4.2. Eigenes Schema anlegen

CREATE SCHEMA keycloak AUTHORIZATION keycloaktest;

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

1.4.3. Rechte auf Schema und Tabellen setzen

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;

Damit neu angelegte Tabellen und Sequenzen ebenfalls passend berechtigt werden:

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;

1.4.4. Wichtiger Hinweis zu GRANT

Dieser Befehl ist fachlich nicht korrekt:

GRANT SELECT, INSERT, UPDATE, DELETE ON keycloakdb TO keycloakworktest;

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

Korrekt ist auf Datenbankebene zum Beispiel:

GRANT CONNECT ON DATABASE keycloakdb TO keycloakworktest;

1.5. SSL-gesicherte Verbindungen

Je nach Umgebung werden Verbindungsinformationen und Zertifikate getrennt bereitgestellt.

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>
}

Für den Client werden typischerweise benötigt:

  • Root-CA-Zertifikat
  • Client-Zertifikat
  • privater Schlüssel
  • Hostname oder IP
  • Port
  • Benutzername
  • Datenbankname

Beispielhafte psql-Verbindung:

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"

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

1.6. Import und Restore

Die Dump-Files werden je nach Quelle entweder als SQL-Skript, Custom-Format-Archiv, Directory-Format oder TAR-Archiv bereitgestellt.

Wichtige Unterscheidung:

  • Plain SQL wird mit psql eingespielt.
  • Custom-/Directory-/Tar-Dumps aus pg_dump werden mit pg_restore eingespielt.

1.6.1. Dateien vorbereiten

mkdir /srv/postgresql/data/import_dump/
cp -r ../exx/z_backups/postgresql/rollenerfassung* /srv/postgresql/data/import_dump/

1.6.2. Berechtigungen setzen

Falls der PostgreSQL-Container mit UID/GID 70:70 arbeitet:

chown -R 70:70 /srv/postgresql/data/import_dump

1.6.3. In den Container wechseln

docker exec -it postgres bash

Beispielpfad im Container:

cd /var/lib/postgresql/data/import_dump/rollenerfassung_raspberrypi/

1.6.4. Restore-SQL prüfen

sed -e 's/\$\$PATH\$\$/\/tmp\/dvdrental/g' restore.sql | grep --color dvdrental

Zusätzlich kann der Anfang der Datei geprüft werden:

head -n 50 restore.sql

So lässt sich feststellen:

  • wie die Datenbank heißt
  • wie die Rolle heißt
  • welche Pfade ersetzt werden müssen

1.7. Beispiel: Rollenerfassung

1.7.1. Rolle anlegen

CREATE USER rollenerfassung WITH PASSWORD '<PASSWORT>';

1.7.2. Datenbanken anlegen

Für allgemeine Dokumentation besser ohne starre Locale-Vorgabe arbeiten, da diese plattform- und umgebungsabhängig ist:

CREATE DATABASE rollenerfassung_server TEMPLATE template0 ENCODING 'UTF8';
CREATE DATABASE rollenerfassung_raspberrypi TEMPLATE template0 ENCODING 'UTF8';

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

1.7.3. Eigentümer setzen

ALTER DATABASE rollenerfassung_raspberrypi OWNER TO rollenerfassung;
ALTER DATABASE rollenerfassung_server OWNER TO rollenerfassung;

1.7.4. Connect-Rechte vergeben

GRANT CONNECT ON DATABASE rollenerfassung_server TO rollenerfassung;
GRANT CONNECT ON DATABASE rollenerfassung_raspberrypi TO rollenerfassung;

1.8. Restore eines SQL-Skripts mit psql

Wenn restore.sql ein Plain-SQL-Skript ist, kann der Pfad beim Einspielen ersetzt werden.

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

Dabei wird:

  • $$PATH$$ durch den echten Pfad ersetzt
  • das Ergebnis direkt an psql übergeben
  • das SQL-Skript in die Zieldatenbank eingespielt

1.9. Restore eines Archivs mit pg_restore

Wenn ein Dump mit pg_dump -Fc oder in einem anderen Archivformat erstellt wurde, wird pg_restore verwendet.

docker exec -i postgres pg_restore --clean -U postgres -d rollenerfassung_server < restore.dump

1.9.1. Erklärung

  • docker exec -i postgres führt den Befehl im Container aus
  • pg_restore stellt einen Archiv-Dump wieder her
  • --clean entfernt vorhandene Objekte vor dem Restore
  • -U postgres nutzt die Rolle postgres
  • -d rollenerfassung_server gibt die Zieldatenbank an

1.10. Dump erstellen

Ein Dump im Custom-Format kann aus dem Container heraus mit pg_dump erzeugt werden.

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

1.10.1. Erklärung

  • $CONTAINER ist der Name des PostgreSQL-Containers
  • -Fc erzeugt ein Custom-Format für pg_restore
  • -U postgres verwendet die Rolle postgres
  • $DATABASE ist die zu sichernde Datenbank
  • $OUTPUT/$DATABASE-$DATE.dump ist die Zieldatei

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

1.11. Einfacher Funktionstest

Nach Einrichtung von Rolle und Rechten kann ein Funktionstest durchgeführt werden.

1.11.1. Testtabelle anlegen

CREATE TABLE persons (
    personid int PRIMARY KEY,
    firstname varchar(50),
    lastname varchar(50)
);

1.11.2. Tabellen anzeigen

\d

1.11.3. Datensatz einfügen

INSERT INTO persons VALUES (1, 'Alex', 'Hofmann');

1.11.4. Daten prüfen

SELECT * FROM persons;

1.11.5. Tabelle wieder löschen

DROP TABLE persons;

1.11.6. Sitzung beenden

\q

1.12. Empfohlenes Betriebsmodell

1.12.1. Administrative Rolle

  • Datenbanken anlegen, wenn erforderlich
  • Schemas anlegen
  • Rechte vergeben
  • Eigentümer von Datenbank oder Schema sein
  • administrative Änderungen innerhalb der Anwendung durchführen

1.12.2. Applikationsrolle

  • Verbindung zur Datenbank herstellen
  • Daten lesen und schreiben
  • keine unnötigen administrativen Rechte besitzen

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

1.13. Sicherheitshinweise

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

1.14. Zusammenfassung

Für PostgreSQL in Docker-Umgebungen empfiehlt sich ein einheitliches Vorgehen:

  • Rollout per Ansible-Rolle
  • Trennung von administrativen Rollen und Applikationsrollen
  • klare Namenskonventionen
  • gezielte Rechtevergabe
  • saubere Trennung zwischen psql-Restore und pg_restore
  • dokumentierte Pfade und Container-Befehle
  • optional SSL-gesicherte Verbindungen mit strenger Zertifikatsprüfung

So bleibt die Umgebung übersichtlich, wartbar und sicher.

No comments to display

Back to top