Day 3: Configuration and Access
Goal: You find every parameter along with its source, change it via
ALTER SYSTEM, know when a reload is enough and when a restart is needed, control access throughpg_hba.confand secure connections with your own CA. · Duration: approx. 75 min · Prerequisite: Day 0
Briefing
- On Ubuntu, the configuration lives in
/etc/postgresql/18/main;ALTER SYSTEMwrites topostgresql.auto.confin the data directory – and that one wins. - Every parameter has a
context:user(immediately, per session),sighup(reload),postmaster(restart). pg_hba.confis read from top to bottom; the first matching line decides.ssl = onwith “snakeoil” encrypts, but doesn’t prove who you’re talking to. Only your own CA plussslmode=verify-fulldoes that.
All commands run on pg1 unless it says
pg2.
Drill 1: Find files and sources
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.confExpected:
/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
The reading order: postgresql.conf, then
conf.d/*.conf, finally postgresql.auto.conf.
With duplicate entries, the one read last wins. pg_settings
shows which value applies and where it comes from:
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;"Expected:
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 is given in the unit from unit:
16384 × 8 kB = 128 MB. internal are fixed values such as
block_size. pg_file_settings shows what’s in
the files (including values not yet applied):
psql -c "SELECT sourcefile, sourceline, name, setting, applied FROM pg_file_settings
WHERE name IN ('shared_buffers', 'listen_addresses', 'max_connections');"Expected:
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)
Save your progress? Sign up for free
Drill 2: ALTER SYSTEM and reload
work_mem has the context user – a reload is
enough, new values apply immediately to new queries:
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'Expected:
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 only changes the file; only the reload
(SIGHUP) reads it again. Equivalent:
sudo systemctl reload postgresql@18-main. Values can also
be set more narrowly – for the session, a role or a database:
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'Expected (abridged):
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)
Precedence from weak to strong: file, database
(ALTER DATABASE … SET), role, role in database,
SET in the session. The password is a lab
value.
Save your progress? Sign up for free
Drill 3: Restart parameters and rules of thumb
shared_buffers is PostgreSQL’s cache in shared memory –
context postmaster. A reload doesn’t load it:
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.logExpected (abridged):
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 is the view your monitoring should
query. Now the restart with the Debian tool:
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'Expected:
2026-11-19 04:42:46.985518+00
2026-11-19 04:50:30.658521+00
2GB
0
Under systemd, pg_ctlcluster redirects to
systemctl restart postgresql@18-main – both ways are the
same. A restart drops all connections.
Rules of thumb as starting values, then measure (Day 7):
| Parameter | Starting value | Meaning |
|---|---|---|
shared_buffers |
25 % RAM | own cache, restart |
effective_cache_size |
50–75 % RAM | planner assumption only, allocates nothing |
work_mem |
4–64 MB | per sort/hash step, not per session |
max_connections |
100–300 | every connection is a process |
work_mem is the dangerous number: a query with three
sorts and 200 connections can use 600 × work_mem. Better to
keep it small globally and raise it specifically for report roles (Drill
2). Many clients belong behind a pooler (Day 12), not in a higher
max_connections.
Save your progress? Sign up for free
Drill 4: Read and change pg_hba.conf
Each line: type, database, role, address, method. The first matching
line decides – including 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}';"Expected (abridged):
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)
On pg2:
PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_report -d notes -Atc 'SELECT 1'Expected (abridged):
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
Line 134 matches first, line 135 is never reached. Back on
pg1: delete the reject line and deliberately
add a typo. pg_hba_file_rules reads the file fresh and
checks it before the 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()'Expected (abridged):
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
A broken pg_hba.conf doesn’t take down the running
server – it keeps the old version. On the next restart, however, it
would not come up. Therefore: check pg_hba_file_rules
first, then reload. Now tag03_report gets in from
pg2:
PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_report -d notes \
-Atc 'SELECT current_user, inet_client_addr()'Expected:
tag03_report|172.16.0.123
Save your progress? Sign up for free
Drill 5: scram-sha-256 instead of md5
MD5 passwords are deprecated as of PostgreSQL 18. We deliberately create a role with an old hash, the kind still lying around after an upgrade from old versions:
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()'Expected (abridged):
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)
On pg2:
PGPASSWORD=lab-tag03 psql -h 172.16.0.182 -U tag03_alt -d notes -Atc 'SELECT 1'Expected (abridged):
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
password authentication failed for user "tag03_alt"
The server log on pg1 names the real reason, which the
client isn’t told for security reasons:
sudo tail -n 3 /var/log/postgresql/postgresql-18-main.logExpected (abridged):
... 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"
The fix: find old hashes and set the password again – with
password_encryption = scram-sha-256 a SCRAM hash is
created:
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'"Expected:
tag03_alt
ALTER ROLE
SCRAM-SHA-256$
After that, the login from pg2 works. The method
md5 in pg_hba.conf also accepts SCRAM hashes
(it then negotiates SCRAM) – scram-sha-256 there, on the
other hand, allows only SCRAM. Goal: scram-sha-256
everywhere, no md5 hashes left.
Save your progress? Sign up for free
Drill 6: TLS with your own CA
First on pg2: the client should verify the certificate.
C="host=172.16.0.182 dbname=notes user=tag03_report"
PGPASSWORD=lab-tag03 psql "$C sslmode=verify-full" -Atc 'SELECT 1'Expected (abridged):
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 needs a CA it trusts. On pg1 we
build a lab CA and a server certificate. The name the client dials must
be in the subjectAltName – here the 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 subjectAltNameExpected (abridged):
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
The CA (only ca.crt, never ca.key) goes
onto the client. From your own machine:
ssh [email protected] cat pg-ca/ca.crt | ssh [email protected] \
'mkdir -p ~/.postgresql && cat > ~/.postgresql/root.crt'On pg2 – the server still delivers the snakeoil certificate:
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'Expected:
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)
Install the new pair on pg1. The key must belong to
postgres and be readable only by it. The ssl_*
parameters have the context 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.logExpected (abridged):
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"
On 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'Expected:
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)
So far the client decides whether it wants TLS. hostssl
instead of host enforces it on the server side. On
pg1:
sudo sed -i 's/^host notes tag03_report /hostssl notes tag03_report /' $H
psql -Atc 'SELECT pg_reload_conf()'On 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 PGPASSWORDExpected (abridged):
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 levels: prefer (default) uses TLS
if possible but verifies nothing; require enforces TLS
without verification; verify-ca checks the chain;
verify-full also checks the name. Only
verify-full protects against a server that inserts itself
in between.
Warning: From today on, the certificate stays on
pg1(ssl_cert_fileandssl_key_fileinpostgresql.auto.conf), and~/.postgresql/root.crtstays onpg2. The CA with its key lives in~/pg-caonpg1– fine in the lab, but in production the CA key doesn’t belong on the database server.
Warning: If
~/.postgresql/root.crtexists, libpq checks the chain even withsslmode=require(likeverify-ca), andprefersilently falls back to plain text with a server that uses a different CA. Onpg2,psql "host=127.0.0.1 sslmode=require"therefore showsSSL error: certificate verify failed(snakeoil is still running there). For other servers, specifysslrootcert=<file>orsslrootcert=systemexplicitly.
Save your progress? Sign up for free
Drill 7: Cancel and terminate sessions
On pg2, start a hanging query (in a second terminal or in the background):
PGPASSWORD=lab-tag03 psql "$C application_name=report" -c 'SELECT pg_sleep(600)'On pg1, find it and first cancel it politely, then
terminate it hard:
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'"Expected (abridged):
pid | usename | app | client_addr | state | query
------+--------------+--------+--------------+--------+----------------------
3699 | tag03_report | report | 172.16.0.123 | active | SELECT pg_sleep(600)
(1 row)
t
On pg2 you see:
ERROR: canceling statement due to user request
pg_cancel_backend only cancels the running query; the
session lives on. pg_terminate_backend ends the whole
process – including open transactions that hold locks. Start the query
on pg2 once more, then on 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'"Expected:
pg_terminate_backend
----------------------
t
(1 row)
0
On pg2:
FATAL: terminating connection due to administrator command
SSL connection has been closed unexpectedly
connection to server was lost
Save your progress? Sign up for free
Exercise: Speed Drills
- Show all parameters waiting for a restart. Target time: 20 s
- Show the file and line that
max_connectionscomes from. Target time: 30 s - Check
pg_hba.conffor errors without reloading. Target time: 20 s - Find all roles that still have an MD5 hash. Target time: 30 s
- Terminate all sessions of the role
tag03_report. Target time: 30 s
Solution
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'"Expected (abridged):
/etc/postgresql/18/main/postgresql.conf:65
0
After the drills, the first, fourth and fifth queries return no rows.
Save your progress? Sign up for free
Checkpoint
psql -Atc 'SHOW ssl_cert_file'Expected:
/etc/postgresql/18/main/server.crt
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL'Expected: 0
psql -Atc 'SHOW password_encryption'Expected: scram-sha-256
On 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()'Expected: TLSv1.3 without a password
prompt (from .pgpass, Day 1).
openssl x509 -in ~/.postgresql/root.crt -noout -subjectExpected:
subject=CN = Lab PostgreSQL CA
Challenge
Make sure that sessions of tag03_report that sit idle in
an open transaction are terminated automatically after 5 seconds –
without a restart, without a reload and only for this role. Prove it
from pg2.
Hint: The parameter is called
idle_in_transaction_session_timeout.
Solution
psql -c "ALTER ROLE tag03_report SET idle_in_transaction_session_timeout = '5s'"On pg2:
PGPASSWORD=lab-tag03 psql "$C" <<'EOF'
BEGIN;
SELECT 1 AS eins;
\! sleep 7
SELECT 2 AS zwei;
EOFExpected:
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
Role settings apply from the next login. Open, idle transactions hold locks and slow down VACUUM (Day 6) – a timeout is cheap insurance.
Save your progress? Sign up for free
Cleanup
shared_buffers and work_mem back to their
defaults, test rules and roles gone. Your own certificate stays:
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.confExpected:
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 Note
- “Reload or restart?” –
pg_settings.contexttells you:user/superuser/sighupvia reload,postmasteronly via restart;pending_restartshows open cases. - “Why can’t a client get in even though the password is correct?” –
The first matching line in
pg_hba.conf(often arejector a wrong address), the method doesn’t match the hash (MD5 vs. SCRAM), or a forgotten reload. The real reason is in the server log. - “What does
sslmode=verify-fullgive you?” – It checks the chain and the host name; without it, TLS protects against eavesdropping but not against a fake server.
Enterprise Note
- Manage configuration via Ansible/Salt in
conf.d/*.conforpostgresql.conf; lockALTER SYSTEMwithallow_alter_system = off(from PG 17) so nothing gets changed behind the repository’s back. - Certificates from the internal PKI (Vault, cert-manager, ACME) with
expiry monitoring and automatic reload; clients with
sslmode=verify-full, client certificates if needed (hostssl … cert). pg_hba.confas tight as possible: a separate role and database per application, only its network, noall all 0.0.0.0/0; changes only after apg_hba_file_rulescheck.- Derive memory parameters from load tests, not from tables; tools like PGTune only provide the starting point.
- For report and batch roles, separate
work_mem,statement_timeoutandidle_in_transaction_session_timeoutvalues viaALTER ROLE.