Skip to main content

PostgreSQL

<h3 id="bkmrk-1.1.-einleitung"><span class="nh-number">1.1.

</span>Einleitung</h3>
<p>PostgreSQL ist ein relationales Open-Source-Datenbanksystem. Es eignet sich sowohl für klassische Anwendungsdatenbanken als auch für komplexere Fachverfahren.</p>
<p>Gegenüber <strong>MySQL</strong> 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.</p>
<p>Gegenüber <strong>Oracle Database</strong> 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.</p>
<p>In Docker- und Ansible-basierten Umgebungen ist PostgreSQL besonders attraktiv, weil es sich gut automatisieren, standardisieren und wartbar betreiben lässt.</p>

<h3 id="bkmrk-1.2.-allgemein"><span class="nh-number">1.2. </span>Allgemein

    </h3>
    <ul>
    <li>Rollout per Ansible-Rolle </li>
    <li>basiert auf Docker</li>
    </ul>
    <p><strong>Beispielsystem:</strong></p>
    <ul>
    <li>Server:

    <code>srv3306.lej.eis.network: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>
    <p>Ziel ist eine saubere Trennung zwischen administrativen Rollen, Applikationsrollen, Datenbanken, Schemas sowie Dump- und Restore-Prozessen.</p>

    Datenbank:<h3 postgresid="bkmrk-1.3.-rollen-und-namenskonzept"><span class="nh-number">1.3. </span>Rollen- und Namenskonzept</h3>
    <p>Für den Betrieb wird empfohlen, administrative Rollen und Applikationsrollen sauber zu trennen.</p>

    Benutzer:<p><strong>Administrative postgresRolle</strong></p>
    <p>Die administrative Rolle ist für Verwaltungsaufgaben zuständig, zum Beispiel:</p>
    <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>
    <p>Beispiele für Namenskonventionen:</p>
    <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>

    Pw:<p><strong>Applikationsrolle</strong></p>
    <p>Die sieheApplikationsrolle serverpoolwird von der Anwendung im Tagesbetrieb verwendet.</p>
    <p>Typische Rechte:</p>
    <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>
    <p>Beispiele für Namenskonventionen:</p>
    <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>
    <p><strong>Best Practice:</strong> Rollenattribute wie <code>CREATEDB</code> oder docker-compose<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.</p>

    <h3 id="bkmrk-1.2.4.-beispiel-keycloak-datenbank"><span

    class="nh-number">1.4. </span>Beispiel: Keycloak-Datenbank</h3>
    <p><strong>Administrative Rolle anlegen</strong></p>
    <pre><code>CREATE USER keycloaktest WITH PASSWORD '&lt;ADMIN_PASSWORT&gt;';</code></pre>

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

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

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

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

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

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

    <h3 id="bkmrk-1.5.-arbeiten-mit-schemas"><span class="nh-number">1.5. </span>Arbeiten mit Schemas</h3>
    <p><strong>Vorhandene Schemas anzeigen</strong></p>
    <pre><code>\dn</code></pre>

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

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

    <p><strong>Rechte auf Schema und Tabellen setzen</strong></p>
    <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>

    <p>Damit neu angelegte Tabellen und Sequenzen ebenfalls passend berechtigt werden:</p>
    <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>

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

    <p><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.</p>

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

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

    <pre><code>database_public_ip = 35.198.170.75

    database_ca_certificates = {
      shareddb = &lt;CA-Zertifikat&gt;
    }

    database_private_keys = {
      shareddb = &lt;Client-Private-Key&gt;
    }

    database_public_keys = {
      shareddb = &lt;Client-Zertifikat&gt;
    }</code></pre>

    <p>Für den Client werden typischerweise benötigt:</p>
    <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>

    <p>Beispielhafte <code>psql</code>-Verbindung:</p>
    <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>

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

    <h3 id="bkmrk-1.7.-import-und-restore"><span class="nh-number">1.7. </span>Import (Restore)und

    Restore</h3>
    <p>Die Dump-Files wurdenwerden je nach Quelle entweder als tarSQL-Skript, zurCustom-Format-Archiv, VerfügungDirectory-Format gestelltoder TAR-Archiv bereitgestellt.</p>

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

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

    </code></pre>

    im<p><strong>Berechtigungen entpacktensetzen</strong></p>
    <p>Falls Ordnerder befindenPostgreSQL-Container sichmit dat-DateienUID/GID und<code>70:70</code> einearbeitet:</p>
    <pre><code>chown restore.sql-R 70:70 /srv/postgresql/data/import_dump</code></pre>

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

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

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

    </code></pre>

    mit

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

    prüfen<p>So lässt sich feststellen:</p>
    <ul>
    <li>wie die Datenbank undheißt</li>
    <li>wie derdie BenutzerRolle heißt</li>
    <li>welche Pfade ersetzt werden müssen</li>
    </ul>

    Datenbank<h3 id="bkmrk-1.8.-beispiel-rollenerfassung"><span class="nh-number">1.8. </span>Beispiel: Rollenerfassung</h3>
    <p><strong>Rolle anlegen</strong></p>
    <pre><code>CREATE USER rollenerfassung WITH PASSWORD '&lt;PASSWORT&gt;';</code></pre>

    <p><strong>Datenbanken anlegen</strong></p>
    <p>Für allgemeine Dokumentation besser ohne starre Locale-Vorgabe arbeiten, da diese plattform- und Benutzerumgebungsabhängig anlegen

    ist:</p>
    <pre><code>CREATE 
    DATABASE rollenerfassung_server TEMPLATE template0 ENCODING 'UTF8';
    CREATE DATABASE rollenerfassung_raspberrypi WITH TEMPLATE = template0 ENCODING = 'UTF8';</code></pre>
LOCALE

    <p>Wenn =in 'German_Germany.1252';einer konkreten Umgebung eine bestimmte Locale erforderlich ist, sollte sie explizit passend zur Zielplattform dokumentiert werden.</p>

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


    ALTER

    DATABASE

    rollenerfassung_server
    OWNER
    TO

    in Ordner im Container kopierenrollenerfassung;</code></pre>

    dann<p><strong>Connect-Rechte Berechtigungvergeben</strong></p>
    <pre><code>GRANT setzen

    CONNECT 
    chownON -RDATABASE 70:70rollenerfassung_server TO rollenerfassung;
    GRANT CONNECT ON DATABASE rollenerfassung_raspberrypi TO rollenerfassung;</srv/postgresql/data/import_dump

    code></var/lib/postgresql/data/import_dump/rollenerfassungpre>

    <h3 id="bkmrk-1.9.-restore-eines-sql-skripts-mit-psql"><span class="nh-number">1.9. </span>Restore eines SQL-Skripts mit diesenpsql</h3>
    <p>Wenn Befehl<code>restore.sql</code> denein Plain-SQL-Skript ist, kann der Pfad inbeim derEinspielen sqlersetzt ändernwerden.</p>

    im Container

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

    <p>Dabei USERwird:</p>
    <ul>
    <li><code>$$PATH$$</code> rollenerfassungdurch WITHden PASSWORDechten 'xxxxx';Pfad GRANTersetzt</li>
    <li>das CONNECTErgebnis ONdirekt DATABASEan rollenerfassung_server<code>psql</code> TOübergeben</li>
    <li>das rollenerfassung;SQL-Skript GRANTin CONNECTdie ONZieldatenbank DATABASEeingespielt</li>
    </ul>

    rollenerfassung_raspberrypi

    <h3 TOid="bkmrk-1.10.-restore-eines-archivs-mit-pg_restore"><span rollenerfassung;class="nh-number">1.10. CREATE</span>Restore DATABASEeines rollenerfassung_serverArchivs WITHmit TEMPLATEpg_restore</h3>
    <p>Wenn =ein template0Dump ENCODINGmit =<code>pg_dump 'UTF8'-Fc</code> LOCALEoder =in 'German_Germany.1252';einem CREATEanderen DATABASEArchivformat rollenerfassung_raspberrypierstellt WITHwurde, TEMPLATEwird =<code>pg_restore</code> template0verwendet.</p>

    ENCODING = 'UTF8' LOCALE = 'German_Germany.1252';   ALTER DATABASE rollenerfassung_raspberrypi OWNER TO rollenerfassung; ALTER DATABASE rollenerfassung_server OWNER TO rollenerfassung;

    1.3. Wiederherstellung

    <pre><code>docker exec -i postgres pg_restore --clean -U postgres -d rollenerfassung_server <&lt; restore.psql

    dump</code></pre>

    <p><strong>Erklärung</strong></p>
    <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.4.11.-dump-erstellen"><span

    class="nh-number">1.11. </span>Dump 
    erstellen</h3>
    <p>Ein Dump im Custom-Format kann aus dem Container heraus mit <code>pg_dump</code> erzeugt werden.</p>

    <pre><code>docker exec $CONTAINER pg_dump -Fc -U postgespostgres $DATABASE >&gt; $OUTPUT/$DATABASE-$DATE.dump</code></pre>

    <p><strong>Erklärung</strong></p>
    <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>

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

    </code>.</p>

    <h3

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

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

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

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

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

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

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

    <h3 id="bkmrk-1.13.-empfohlenes-betriebsmodell"><span class="nh-number">1.13. </span>Empfohlenes Betriebsmodell</h3>
    <p><strong>Administrative Rolle</strong></p>
    <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>

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

    <p>Dadurch wird vermieden, dass Anwendungen mit überprivilegierten Rollen laufen.</p>

    <h3 id="bkmrk-1.14.-sicherheitshinweise"><span class="nh-number">1.14. </span>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"><span class="nh-number">1.15. </span>Zusammenfassung</h3>
    <p>Für PostgreSQL in Docker-Umgebungen empfiehlt sich ein einheitliches Vorgehen:</p>
    <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>
    <p>So bleibt die Umgebung übersichtlich, wartbar und sicher.</p>

    Back to top