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 überpg_hba.confund 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 SYSTEMschreibt inpostgresql.auto.confim Datenverzeichnis – und die gewinnt. - Jeder Parameter hat einen
context:user(sofort, pro Sitzung),sighup(Reload),postmaster(Neustart). pg_hba.confwird von oben nach unten gelesen, die erste passende Zeile entscheidet.ssl = onmit „snakeoil“ verschlüsselt, beweist aber nicht, mit wem du sprichst. Erst eine eigene CA plussslmode=verify-fulltut 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.confErwartet:
/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.logErwartet (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.logErwartet (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 subjectAltNameErwartet (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.logErwartet (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 PGPASSWORDErwartet (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_fileundssl_key_fileinpostgresql.auto.conf),~/.postgresql/root.crtbleibt aufpg2. Die CA mit Schlüssel liegt in~/pg-caaufpg1– 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 beisslmode=requiredie Kette (wieverify-ca), undpreferfällt bei einem Server mit fremder CA still auf Klartext zurück. Aufpg2zeigtpsql "host=127.0.0.1 sslmode=require"deshalbSSL error: certificate verify failed(dort läuft noch snakeoil). Für andere Server gezieltsslrootcert=<datei>odersslrootcert=systemangeben.
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
- Zeige alle Parameter, die auf einen Neustart warten. Zeitziel: 20 s
- Zeige Datei und Zeile, aus der
max_connectionskommt. Zeitziel: 30 s - Prüfe
pg_hba.confauf Fehler, ohne neu zu laden. Zeitziel: 20 s - Finde alle Rollen, die noch einen MD5-Hash haben. Zeitziel: 30 s
- 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 -subjectErwartet:
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;
EOFErwartet:
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.confErwartet:
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.contextsagt es:user/superuser/sighupper Reload,postmasternur per Neustart;pending_restartzeigt offene Fälle. - „Warum kommt ein Client nicht rein, obwohl das Passwort stimmt?“ –
Erste passende Zeile in
pg_hba.conf(oft einrejectoder 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/*.confoderpostgresql.confverwalten;ALTER SYSTEMmitallow_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.confso eng wie möglich: pro Anwendung eigene Rolle und Datenbank, nur deren Netz, keinall all 0.0.0.0/0; Änderungen nur nachpg_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- undidle_in_transaction_session_timeout-Werte perALTER ROLE.