Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 3: Konfiguration und Zugang

Ziel: Du findest jeden Parameter samt Herkunft, änderst ihn per ALTER SYSTEM, weißt, wann Reload reicht und wann ein Neustart nötig ist, steuerst den Zugang über pg_hba.conf und sicherst Verbindungen mit einer eigenen CA ab. · Dauer: ca. 75 min · Voraussetzung: Tag 0

Briefing

  • Konfiguration liegt auf Ubuntu in /etc/postgresql/18/main, ALTER SYSTEM schreibt in postgresql.auto.conf im Datenverzeichnis – und die gewinnt.
  • Jeder Parameter hat einen context: user (sofort, pro Sitzung), sighup (Reload), postmaster (Neustart).
  • pg_hba.conf wird von oben nach unten gelesen, die erste passende Zeile entscheidet.
  • ssl = on mit „snakeoil“ verschlüsselt, beweist aber nicht, mit wem du sprichst. Erst eine eigene CA plus sslmode=verify-full tut das.

Alle Befehle laufen auf pg1, außer es steht pg2 dran.

Drill 1: Dateien und Herkunft finden

psql -Atc 'SHOW config_file' -c 'SHOW hba_file' -c 'SHOW data_directory'
ls /etc/postgresql/18/main/
grep -nE '^(include|data_directory|hba_file)' /etc/postgresql/18/main/postgresql.conf

Erwartet:

/etc/postgresql/18/main/postgresql.conf
/etc/postgresql/18/main/pg_hba.conf
/var/lib/postgresql/18/main
conf.d  environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf
start.conf
42:data_directory = '/var/lib/postgresql/18/main'       # use data in another directory
44:hba_file = '/etc/postgresql/18/main/pg_hba.conf' # host-based authentication file
879:include_dir = 'conf.d'          # include files ending in '.conf' from

Die Lesereihenfolge: postgresql.conf, dann conf.d/*.conf, zuletzt postgresql.auto.conf. Bei doppelten Einträgen gewinnt der zuletzt gelesene. Welcher Wert gilt und woher er kommt, zeigt pg_settings:

psql -c "SELECT name, setting, unit, context, source FROM pg_settings
  WHERE name IN ('shared_buffers', 'work_mem', 'listen_addresses',
  'max_connections', 'effective_cache_size') ORDER BY name;"
psql -c "SELECT context, count(*) FROM pg_settings GROUP BY 1 ORDER BY 2 DESC;"

Erwartet:

         name         | setting | unit |  context   |       source
----------------------+---------+------+------------+--------------------
 effective_cache_size | 524288  | 8kB  | user       | default
 listen_addresses     | *       |      | postmaster | configuration file
 max_connections      | 100     |      | postmaster | configuration file
 shared_buffers       | 16384   | 8kB  | postmaster | configuration file
 work_mem             | 4096    | kB   | user       | default
(5 rows)

      context      | count
-------------------+-------
 user              |   151
 sighup            |   104
 postmaster        |    69
 superuser         |    50
 internal          |    20
 superuser-backend |     4
 backend           |     2
(7 rows)

setting steht in der Einheit aus unit: 16384 × 8 kB = 128 MB. internal sind feste Werte wie block_size. Was in den Dateien steht (auch noch nicht Angewendetes), zeigt pg_file_settings:

psql -c "SELECT sourcefile, sourceline, name, setting, applied FROM pg_file_settings
  WHERE name IN ('shared_buffers', 'listen_addresses', 'max_connections');"

Erwartet:

                    sourcefile                    | sourceline |       name       | ...
--------------------------------------------------+------------+------------------+----
 /etc/postgresql/18/main/postgresql.conf          |         65 | max_connections  | ...
 /etc/postgresql/18/main/postgresql.conf          |        132 | shared_buffers   | ...
 /var/lib/postgresql/18/main/postgresql.auto.conf |          3 | listen_addresses | ...
(3 rows)

Fortschritt speichern? Kostenlos registrieren

Drill 2: ALTER SYSTEM und Reload

work_mem hat den Kontext user – ein Reload reicht, neue Werte gelten sofort für neue Abfragen:

psql -c "ALTER SYSTEM SET work_mem = '16MB';"
sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf
psql -Atc 'SHOW work_mem'
psql -Atc 'SELECT pg_reload_conf()'
psql -Atc 'SHOW work_mem'

Erwartet:

ALTER SYSTEM
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
work_mem = '16MB'
4MB
t
16MB

ALTER SYSTEM ändert nur die Datei, erst der Reload (SIGHUP) liest sie neu. Gleichwertig: sudo systemctl reload postgresql@18-main. Werte lassen sich auch enger setzen – für die Sitzung, eine Rolle oder eine Datenbank:

psql -c "SET work_mem = '64MB'" -c 'SHOW work_mem'
psql -c "CREATE ROLE tag03_report LOGIN PASSWORD 'lab-tag03'"
psql -c "ALTER ROLE tag03_report SET work_mem = '256MB'"
PGPASSWORD=lab-tag03 psql -h 127.0.0.1 -U tag03_report -d notes \
  -Atc 'SHOW work_mem' -c "SELECT source FROM pg_settings WHERE name = 'work_mem'"
psql -c '\drds'

Erwartet (gekürzt):

SET
 work_mem
----------
 64MB
(1 row)

CREATE ROLE
ALTER ROLE
256MB
user
             List of settings
     Role     | Database |    Settings
--------------+----------+----------------
 tag03_report |          | work_mem=256MB
(1 row)

Rangfolge von schwach nach stark: Datei, Datenbank (ALTER DATABASE … SET), Rolle, Rolle in Datenbank, SET in der Sitzung. Das Passwort ist ein Lab-Wert.

Fortschritt speichern? Kostenlos registrieren

Drill 3: Neustart-Parameter und Daumenregeln

shared_buffers ist der Cache von PostgreSQL im Shared Memory – Kontext postmaster. Ein Reload lädt ihn nicht:

psql -c "ALTER SYSTEM SET shared_buffers = '2GB';"
psql -Atc 'SELECT pg_reload_conf()'
psql -c "SELECT name, setting, unit, pending_restart FROM pg_settings
  WHERE pending_restart;"
psql -c "SELECT name, setting, applied, error FROM pg_file_settings
  WHERE name = 'shared_buffers';"
sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log

Erwartet (gekürzt):

      name      | setting | unit | pending_restart
----------------+---------+------+-----------------
 shared_buffers | 16384   | 8kB  | t
(1 row)

      name      | setting | applied |            error
----------------+---------+---------+------------------------------
 shared_buffers | 128MB   | f       |
 shared_buffers | 2GB     | f       | setting could not be applied
(2 rows)

... LOG:  received SIGHUP, reloading configuration files
... LOG:  parameter "shared_buffers" cannot be changed without restarting the server
... LOG:  configuration file "/var/lib/postgresql/18/main/postgresql.auto.conf"
contains errors; unaffected changes were applied

pending_restart = t ist die Sicht, die das Monitoring abfragen sollte. Jetzt der Neustart mit dem Debian-Werkzeug:

psql -Atc 'SELECT pg_postmaster_start_time()'
sudo pg_ctlcluster 18 main restart
psql -Atc 'SELECT pg_postmaster_start_time()' -c 'SHOW shared_buffers' \
  -c 'SELECT count(*) FROM pg_settings WHERE pending_restart'

Erwartet:

2026-11-19 04:42:46.985518+00
2026-11-19 04:50:30.658521+00
2GB
0

pg_ctlcluster leitet unter systemd auf systemctl restart postgresql@18-main um – beide Wege sind gleich. Ein Neustart trennt alle Verbindungen.

Daumenregeln als Startwerte, danach messen (Tag 7):

Parameter Startwert Bedeutung
shared_buffers 25 % RAM eigener Cache, Neustart
effective_cache_size 50–75 % RAM nur Planer-Annahme, belegt nichts
work_mem 4–64 MB pro Sortier-/Hash-Schritt, nicht pro Sitzung
max_connections 100–300 jede Verbindung ist ein Prozess

work_mem ist die gefährliche Zahl: Eine Abfrage mit drei Sortierungen und 200 Verbindungen kann 600 × work_mem belegen. Lieber global klein und für Berichtsrollen gezielt größer (Drill 2). Viele Clients gehören hinter einen Pooler (Tag 12), nicht in ein höheres max_connections.

Fortschritt speichern? Kostenlos registrieren

Drill 4: pg_hba.conf lesen und ändern

Jede Zeile: Typ, Datenbank, Rolle, Adresse, Methode. Die erste passende Zeile entscheidet – auch reject:

H=/etc/postgresql/18/main/pg_hba.conf
cat <<'EOF' | sudo tee -a $H
host  notes  tag03_report  172.16.0.123/32  reject
host  notes  tag03_report  172.16.0.0/24    scram-sha-256
EOF
psql -Atc 'SELECT pg_reload_conf()'
psql -c "SELECT line_number AS nr, type, database, user_name, address, auth_method
  FROM pg_hba_file_rules WHERE user_name <> '{all}';"

Erwartet (gekürzt):

 nr  | type  | database |   user_name    |   address    |  auth_method
-----+-------+----------+----------------+--------------+---------------
 118 | local | {all}    | {postgres}     |              | peer
 133 | host  | {notes}  | {notes_app}    | 172.16.0.0   | scram-sha-256
 134 | host  | {notes}  | {tag03_report} | 172.16.0.123 | reject
 135 | host  | {notes}  | {tag03_report} | 172.16.0.0   | scram-sha-256
(4 rows)

Auf pg2:

PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_report -d notes -Atc 'SELECT 1'

Erwartet (gekürzt):

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
pg_hba.conf rejects connection for host "172.16.0.123", user "tag03_report",
database "notes", SSL encryption

Zeile 134 passt zuerst, Zeile 135 wird nie erreicht. Zurück auf pg1: reject-Zeile löschen und absichtlich einen Tippfehler einbauen. pg_hba_file_rules liest die Datei frisch und prüft sie vor dem Reload:

sudo sed -i '/tag03_report  172.16.0.123\/32  reject$/d' $H
echo 'host  notes  tag03_report  172.16.0.0/24  scram-sha-265' | sudo tee -a $H
psql -c "SELECT line_number AS nr, error FROM pg_hba_file_rules
  WHERE error IS NOT NULL;"
psql -Atc 'SELECT pg_reload_conf()'
sudo tail -n 2 /var/log/postgresql/postgresql-18-main.log
sudo sed -i '$d' $H
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL' \
  -c 'SELECT pg_reload_conf()'

Erwartet (gekürzt):

 nr  |                     error
-----+-----------------------------------------------
 135 | invalid authentication method "scram-sha-265"
(1 row)

t
... CONTEXT:  line 135 of configuration file "/etc/postgresql/18/main/pg_hba.conf"
... LOG:  /etc/postgresql/18/main/pg_hba.conf was not reloaded
0
t

Eine kaputte pg_hba.conf legt den laufenden Server nicht lahm – er behält die alte Fassung. Beim nächsten Neustart würde er aber nicht hochkommen. Deshalb: erst pg_hba_file_rules prüfen, dann laden. Jetzt kommt tag03_report von pg2 an:

PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_report -d notes \
  -Atc 'SELECT current_user, inet_client_addr()'

Erwartet:

tag03_report|172.16.0.123

Fortschritt speichern? Kostenlos registrieren

Drill 5: scram-sha-256 statt md5

MD5-Passwörter sind ab PostgreSQL 18 abgekündigt. Wir legen absichtlich eine Rolle mit altem Hash an, wie sie nach einem Upgrade von alten Versionen noch herumliegt:

psql -Atc 'SHOW password_encryption'
psql <<'EOF'
SET password_encryption = 'md5';
CREATE ROLE tag03_alt LOGIN PASSWORD 'lab-tag03';
RESET password_encryption;
EOF
psql -c "SELECT rolname, left(rolpassword, 14) AS hash FROM pg_authid
  WHERE rolname LIKE 'tag03%';"
echo 'host  notes  tag03_alt  172.16.0.0/24  scram-sha-256' | sudo tee -a $H
psql -Atc 'SELECT pg_reload_conf()'

Erwartet (gekürzt):

scram-sha-256
SET
WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release
of PostgreSQL.
CREATE ROLE
RESET
   rolname    |      hash
--------------+----------------
 tag03_report | SCRAM-SHA-256$
 tag03_alt    | md598d97dd2d87
(2 rows)

Auf pg2:

PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_alt -d notes -Atc 'SELECT 1'

Erwartet (gekürzt):

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
password authentication failed for user "tag03_alt"

Das Server-Log auf pg1 nennt den echten Grund, den der Client aus Sicherheitsgründen nicht erfährt:

sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log

Erwartet (gekürzt):

... tag03_alt@notes DETAIL:  User "tag03_alt" does not have a valid SCRAM secret.
    Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 135: "host
notes  tag03_alt  172.16.0.0/24  scram-sha-256"

Abhilfe: alte Hashes finden und das Passwort neu setzen – mit password_encryption = scram-sha-256 entsteht ein SCRAM-Hash:

psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'"
psql -c "ALTER ROLE tag03_alt PASSWORD 'lab-tag03'"
psql -Atc "SELECT left(rolpassword, 14) FROM pg_authid WHERE rolname = 'tag03_alt'"

Erwartet:

tag03_alt
ALTER ROLE
SCRAM-SHA-256$

Danach klappt der Login von pg2. Die Methode md5 in pg_hba.conf nimmt auch SCRAM-Hashes an (sie verhandelt dann SCRAM) – scram-sha-256 dort lässt dagegen nur SCRAM zu. Ziel: überall scram-sha-256, keine md5-Hashes mehr.

Fortschritt speichern? Kostenlos registrieren

Drill 6: TLS mit eigener CA

Zuerst auf pg2: Der Client soll das Zertifikat prüfen.

C="host=172.16.0.182 dbname=notes user=tag03_report"
PGPASSWORD=lab-tag03 psql "$C sslmode=verify-full" -Atc 'SELECT 1'

Erwartet (gekürzt):

psql: error: connection to server at "172.16.0.182", port 5432 failed: root
certificate file "/home/ubuntu/.postgresql/root.crt" does not exist

verify-full braucht eine CA, der es vertraut. Auf pg1 bauen wir eine Lab-CA und ein Server-Zertifikat. Der Name, den der Client anwählt, muss im subjectAltName stehen – hier die IP:

mkdir -p ~/pg-ca && cd ~/pg-ca
openssl req -x509 -newkey rsa:4096 -nodes -days 3650 -subj "/CN=Lab PostgreSQL CA" \
  -keyout ca.key -out ca.crt
openssl req -newkey rsa:2048 -nodes -subj "/CN=172.16.0.182" \
  -keyout server.key -out server.csr
printf 'subjectAltName = IP:172.16.0.182\nextendedKeyUsage = serverAuth\n' \
  > server.ext
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -days 825 -extfile server.ext -out server.crt
openssl x509 -in server.crt -noout -subject -issuer -ext subjectAltName

Erwartet (gekürzt):

Certificate request self-signature ok
subject=CN = 172.16.0.182
subject=CN = 172.16.0.182
issuer=CN = Lab PostgreSQL CA
X509v3 Subject Alternative Name:
    IP Address:172.16.0.182

Die CA (nur ca.crt, nie ca.key) kommt auf den Client. Von deinem Rechner aus:

ssh [email protected] cat pg-ca/ca.crt | ssh [email protected] \
  'mkdir -p ~/.postgresql && cat > ~/.postgresql/root.crt'

Auf pg2 – noch liefert der Server das snakeoil-Zertifikat:

PGPASSWORD=lab-tag03 psql "$C sslmode=verify-full" -Atc 'SELECT 1'
openssl s_client -starttls postgres -connect 172.16.0.182:5432 </dev/null 2>/dev/null \
  | grep -E 'subject=|issuer=|Verify return'

Erwartet:

psql: error: connection to server at "172.16.0.182", port 5432 failed: SSL error:
certificate verify failed
subject=CN = argo
issuer=CN = argo
Verify return code: 18 (self-signed certificate)

Auf pg1 das neue Paar einbauen. Der Schlüssel muss postgres gehören und darf nur für ihn lesbar sein. Die ssl_*-Parameter haben Kontext sighup:

D=/etc/postgresql/18/main
sudo install -o postgres -g postgres -m 644 ~/pg-ca/server.crt $D/server.crt
sudo install -o postgres -g postgres -m 600 ~/pg-ca/server.key $D/server.key
psql -c "ALTER SYSTEM SET ssl_cert_file = '$D/server.crt';" \
  -c "ALTER SYSTEM SET ssl_key_file = '$D/server.key';"
psql -Atc 'SELECT pg_reload_conf()'
sudo tail -n 2 /var/log/postgresql/postgresql-18-main.log

Erwartet (gekürzt):

ALTER SYSTEM
ALTER SYSTEM
t
... LOG:  parameter "ssl_cert_file" changed to "/etc/postgresql/18/main/server.crt"
... LOG:  parameter "ssl_key_file" changed to "/etc/postgresql/18/main/server.key"

Auf pg2:

PGPASSWORD=lab-tag03 psql "$C sslmode=verify-full" -c '\conninfo' \
  | grep -E 'Host|SSL (Conn|Proto)'
openssl s_client -starttls postgres -connect 172.16.0.182:5432 \
  -CAfile ~/.postgresql/root.crt </dev/null 2>/dev/null \
  | grep -E 'subject=|issuer=|Verify return'

Erwartet:

 Host                 | 172.16.0.182
 SSL Connection       | true
 SSL Protocol         | TLSv1.3
subject=CN = 172.16.0.182
issuer=CN = Lab PostgreSQL CA
Verify return code: 0 (ok)

Bisher entscheidet der Client, ob er TLS will. hostssl statt host erzwingt es auf Serverseite. Auf pg1:

sudo sed -i 's/^host  notes  tag03_report /hostssl  notes  tag03_report /' $H
psql -Atc 'SELECT pg_reload_conf()'

Auf pg2:

export PGPASSWORD=lab-tag03
psql "$C sslmode=disable" -Atc 'SELECT 1'
psql "$C sslmode=verify-full" \
  -Atc 'SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid()'
unset PGPASSWORD

Erwartet (gekürzt):

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:  no
pg_hba.conf entry for host "172.16.0.123", user "tag03_report", database "notes",
no encryption
t|TLSv1.3

sslmode-Stufen: prefer (Standard) nimmt TLS, wenn möglich, prüft aber nichts; require erzwingt TLS ohne Prüfung; verify-ca prüft die Kette; verify-full prüft zusätzlich den Namen. Nur verify-full schützt gegen einen Server, der sich dazwischenschaltet.

Achtung: Das Zertifikat bleibt ab heute auf pg1 (ssl_cert_file und ssl_key_file in postgresql.auto.conf), ~/.postgresql/root.crt bleibt auf pg2. Die CA mit Schlüssel liegt in ~/pg-ca auf pg1 – im Labor in Ordnung, in Produktion gehört der CA-Schlüssel nicht auf den Datenbankserver.

Achtung: Liegt ~/.postgresql/root.crt, prüft libpq auch bei sslmode=require die Kette (wie verify-ca), und prefer fällt bei einem Server mit fremder CA still auf Klartext zurück. Auf pg2 zeigt psql "host=127.0.0.1 sslmode=require" deshalb SSL error: certificate verify failed (dort läuft noch snakeoil). Für andere Server gezielt sslrootcert=<datei> oder sslrootcert=system angeben.

Fortschritt speichern? Kostenlos registrieren

Drill 7: Sitzungen abbrechen und beenden

Auf pg2 eine hängende Abfrage starten (im zweiten Terminal oder im Hintergrund):

PGPASSWORD=lab-tag03 psql "$C application_name=report" -c 'SELECT pg_sleep(600)'

Auf pg1 finden und erst höflich abbrechen, dann hart beenden:

psql -c "SELECT pid, usename, application_name AS app, client_addr, state,
  left(query, 24) AS query FROM pg_stat_activity WHERE usename = 'tag03_report';"
psql -Atc "SELECT pg_cancel_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report'"

Erwartet (gekürzt):

 pid  |   usename    |  app   | client_addr  | state  |        query
------+--------------+--------+--------------+--------+----------------------
 3699 | tag03_report | report | 172.16.0.123 | active | SELECT pg_sleep(600)
(1 row)

t

Auf pg2 steht:

ERROR:  canceling statement due to user request

pg_cancel_backend bricht nur die laufende Abfrage ab, die Sitzung lebt weiter. pg_terminate_backend beendet den ganzen Prozess – auch offene Transaktionen, die Sperren halten. Starte die Abfrage auf pg2 noch einmal, dann auf pg1:

psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report';"
psql -Atc "SELECT count(*) FROM pg_stat_activity WHERE usename = 'tag03_report'"

Erwartet:

 pg_terminate_backend
----------------------
 t
(1 row)

0

Auf pg2:

FATAL:  terminating connection due to administrator command
SSL connection has been closed unexpectedly
connection to server was lost

Fortschritt speichern? Kostenlos registrieren

Übung: Speed-Drills

  1. Zeige alle Parameter, die auf einen Neustart warten. Zeitziel: 20 s
  2. Zeige Datei und Zeile, aus der max_connections kommt. Zeitziel: 30 s
  3. Prüfe pg_hba.conf auf Fehler, ohne neu zu laden. Zeitziel: 20 s
  4. Finde alle Rollen, die noch einen MD5-Hash haben. Zeitziel: 30 s
  5. Beende alle Sitzungen der Rolle tag03_report. Zeitziel: 30 s

Lösung

psql -Atc 'SELECT name FROM pg_settings WHERE pending_restart'
psql -Atc "SELECT sourcefile || ':' || sourceline FROM pg_settings
  WHERE name = 'max_connections'"
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL'
psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'"
psql -Atc "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report'"

Erwartet (gekürzt):

/etc/postgresql/18/main/postgresql.conf:65
0

Die erste, vierte und fünfte Abfrage liefern nach den Drills keine Zeilen.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

psql -Atc 'SHOW ssl_cert_file'

Erwartet: /etc/postgresql/18/main/server.crt

psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL'

Erwartet: 0

psql -Atc 'SHOW password_encryption'

Erwartet: scram-sha-256

Auf pg2:

psql "host=172.16.0.182 dbname=notes user=notes_app sslmode=verify-full" \
  -Atc 'SELECT version FROM pg_stat_ssl WHERE pid = pg_backend_pid()'

Erwartet: ohne Passwortabfrage (aus .pgpass, Tag 1) TLSv1.3.

openssl x509 -in ~/.postgresql/root.crt -noout -subject

Erwartet: subject=CN = Lab PostgreSQL CA

Challenge

Sorge dafür, dass Sitzungen von tag03_report, die in einer offenen Transaktion untätig herumstehen, nach 5 Sekunden automatisch beendet werden – ohne Neustart, ohne Reload und nur für diese Rolle. Weise es von pg2 aus nach.

Hinweis: Der Parameter heißt idle_in_transaction_session_timeout.

Lösung

psql -c "ALTER ROLE tag03_report SET idle_in_transaction_session_timeout = '5s'"

Auf pg2:

PGPASSWORD=lab-tag03 psql "$C" <<'EOF'
BEGIN;
SELECT 1 AS eins;
\! sleep 7
SELECT 2 AS zwei;
EOF

Erwartet:

BEGIN
 eins
------
    1
(1 row)

FATAL:  terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly
connection to server was lost

Rollen-Einstellungen gelten ab der nächsten Anmeldung. Offene, untätige Transaktionen halten Sperren und bremsen VACUUM (Tag 6) – ein Timeout ist billige Versicherung.

Fortschritt speichern? Kostenlos registrieren

Aufräumen

shared_buffers und work_mem zurück auf den Standard, Testregeln und Rollen weg. Das eigene Zertifikat bleibt:

psql -c 'ALTER SYSTEM RESET shared_buffers' -c 'ALTER SYSTEM RESET work_mem'
sudo systemctl restart postgresql@18-main
sudo sed -i '/ tag03_/d' /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -c 'DROP ROLE tag03_report' -c 'DROP ROLE tag03_alt'
sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf

Erwartet:

ALTER SYSTEM
ALTER SYSTEM
t
DROP ROLE
DROP ROLE
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file = '/etc/postgresql/18/main/server.key'

Interview-Notiz

  • „Reload oder Neustart?“ – pg_settings.context sagt es: user/superuser/sighup per Reload, postmaster nur per Neustart; pending_restart zeigt offene Fälle.
  • „Warum kommt ein Client nicht rein, obwohl das Passwort stimmt?“ – Erste passende Zeile in pg_hba.conf (oft ein reject oder falsche Adresse), Methode passt nicht zum Hash (MD5 vs. SCRAM) oder Reload vergessen. Der echte Grund steht im Server-Log.
  • „Was bringt sslmode=verify-full?“ – Prüft Kette und Hostnamen; ohne das ist TLS gegen Abhören, aber nicht gegen einen falschen Server geschützt.

Enterprise-Notiz

  • Konfiguration per Ansible/Salt in conf.d/*.conf oder postgresql.conf verwalten; ALTER SYSTEM mit allow_alter_system = off (ab PG 17) sperren, damit nichts am Repository vorbei geändert wird.
  • Zertifikate aus der internen PKI (Vault, cert-manager, ACME) mit Laufzeit-Monitoring und Reload-Automatik; Clients mit sslmode=verify-full, ggf. Client-Zertifikate (hostssl … cert).
  • pg_hba.conf so eng wie möglich: pro Anwendung eigene Rolle und Datenbank, nur deren Netz, kein all all 0.0.0.0/0; Änderungen nur nach pg_hba_file_rules-Prüfung.
  • Speicherparameter aus Lasttests ableiten, nicht aus Tabellen; Tools wie PGTune liefern nur den Startpunkt.
  • Für Berichts- und Batch-Rollen eigene work_mem-, statement_timeout- und idle_in_transaction_session_timeout-Werte per ALTER ROLE.