PostgreSQL 17 on Debian 13: TLS, SCRAM, Restore

pajus 2026-10-28 13 min read

Most PostgreSQL install guides end at CREATE USER. Then the server runs for years with a default pg_hba.conf, one superuser shared by every app, no TLS on the wire and a backup that has never been restored. This tutorial sets up PostgreSQL 17 on Debian 13 the way I want it on a production box. It binds only to localhost and a WireGuard IP and uses scram-sha-256 only. The pg_hba.conf is short and allows only hostssl over TCP. TLS uses a certbot deploy hook, admin and app roles are separate, and the last step proves that a dump actually restores.

Every step ends with a check. If a check fails, stop and fix it before you move on.

Tested on 2026-10-28 on a fresh Debian 13.7 VM (arm64, postgresql-17 17.11-0+deb13u1): every step and check in this article, with a private CA in place of Let's Encrypt and network namespaces in place of the WireGuard tunnel.

What we build and prerequisites

The example network is a WireGuard tunnel: the database server is 10.8.0.1, the application host is 10.8.0.2 and an admin workstation is 10.8.0.3. Clients reach the server as db.example.net, and the TLS certificate is issued for that name. If you don't have a tunnel yet, see the WireGuard on Debian 13 setup.

Component	Version on Debian 13 (trixie)
`postgresql-17`	17.11-0+deb13u1 (accepted into stable-security on 2026-08-13)
`postgresql-common`	278
Default `password_encryption`	`scram-sha-256`
Default `ssl_min_protocol_version`	`TLSv1.2`
Default `listen_addresses`	`localhost`

Make sure the security suite is in your APT sources, or you will be stuck on an older point release.

Step 1: Install PostgreSQL 17 and learn the Debian cluster layout

apt update
apt install postgresql-17
pg_lsclusters
systemctl status postgresql@17-main

The package creates a cluster called main automatically. pg_lsclusters shows the version, cluster name, port, status, owner, data directory and log file:

Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

Upstream guides miss this part. On Debian the configuration lives in /etc/postgresql/17/main/ (postgresql.conf, pg_hba.conf, start.conf). The data lives in /var/lib/postgresql/17/main. Edit files in /etc, not in the data directory. Each cluster has its own systemd unit, postgresql@17-main, so a reload or restart affects only that cluster. Don't use dashes in cluster names, because they break the unit naming.

When you run pg_ctlcluster as root, it hands the action to systemctl so the cluster stays under systemd supervision. I use systemctl directly.

Check where Debian put the include directory for drop-in configs:

grep -n include_dir /etc/postgresql/17/main/postgresql.conf

If that returns include_dir = 'conf.d', put your changes into /etc/postgresql/17/main/conf.d/ as shown below. If it returns nothing, put the same lines at the end of postgresql.conf.

Step 2: Restrict listen addresses

listen_addresses = 'localhost,10.8.0.1'
password_encryption = 'scram-sha-256'

The WireGuard address has to exist when PostgreSQL starts, so order the unit after the tunnel. The drop-in directory doesn't exist by default, so create it first (or use systemctl edit postgresql@17-main):

install -d /etc/systemd/system/postgresql@17-main.service.d

[Unit]
After=wg-quick@wg0.service
Wants=wg-quick@wg0.service

Warning: listen_addresses only takes effect at server start, so the next command restarts PostgreSQL and drops every open connection. Do it in a maintenance window.

systemctl daemon-reload
systemctl restart postgresql@17-main
ss -tlnp | grep 5432

You should see listeners on 127.0.0.1:5432 and 10.8.0.1:5432, plus [::1]:5432 if localhost resolves to IPv6 too. You should not see 0.0.0.0:5432 or *:5432.

If 10.8.0.1:5432 is missing, the tunnel address did not exist when PostgreSQL started. The start does not fail: systemctl still shows the unit as active, and PostgreSQL listens on localhost only. The only trace is a warning in the log:

grep 'could not create listen socket' /var/log/postgresql/postgresql-17-main.log

2026-10-28 22:19:22.672 UTC [3644] WARNING:  could not create listen socket for "10.8.0.1"

Bring the tunnel up and restart PostgreSQL once more.

Port 5432 should also be closed in your firewall except from the tunnel. My nftables default-drop ruleset works for this, with one extra accept rule for the app host and the admin workstation:

iifname "wg0" ip saddr { 10.8.0.2, 10.8.0.3 } tcp dport 5432 accept

Step 3: TLS with a certbot deploy hook

Enable TLS before you write hostssl lines. PostgreSQL ignores hostssl records (with a warning) when SSL is off, and you would be locked out.

Debian's pg_createcluster enables SSL with the snakeoil certificate when it exists. That gives you encryption, but clients can't verify the server. We replace it with a real certificate.

If db.example.net has a public DNS record pointing at this server's public web server, a webroot challenge works. On the clients, point the name at the WireGuard IP in /etc/hosts so verify-full matches. For a host with no public name, use a private CA: the files and paths below stay the same, and clients use sslrootcert=/path/to/ca.crt instead of system.

/usr/bin/certbot certonly --webroot -w /var/www/html -d db.example.net
install -d -o postgres -g postgres -m 0700 /etc/postgresql/17/main/tls

Certbot keeps keys readable only by root, and PostgreSQL rejects a key file with loose permissions. The hook copies the files into a directory owned by postgres with the key at mode 0600, then reloads. Certbot exports RENEWED_LINEAGE (the live directory) and RENEWED_DOMAINS to deploy hooks.

#!/bin/sh
set -e
case " $RENEWED_DOMAINS " in
  *" db.example.net "*) ;;
  *) exit 0 ;;
esac
dst=/etc/postgresql/17/main/tls
install -o postgres -g postgres -m 0644 "$RENEWED_LINEAGE/fullchain.pem" "$dst/server.crt"
install -o postgres -g postgres -m 0600 "$RENEWED_LINEAGE/privkey.pem" "$dst/server.key"
systemctl reload postgresql@17-main

The hook didn't exist yet when certbot issued the certificate, and trixie's certbot 4.0 doesn't run deploy directory hooks for a new certificate from certonly (that only arrived in Certbot 5.3.0). So run the hook once by hand for the first copy:

chmod 0755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
RENEWED_LINEAGE=/etc/letsencrypt/live/db.example.net RENEWED_DOMAINS=db.example.net /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh

ssl = on
ssl_cert_file = '/etc/postgresql/17/main/tls/server.crt'
ssl_key_file = '/etc/postgresql/17/main/tls/server.key'
ssl_min_protocol_version = 'TLSv1.3'

Use TLSv1.2 instead if you have old clients. A reload makes PostgreSQL read the certificate files again. If they are broken, it keeps the old SSL configuration and logs the error, so always check the log:

systemctl reload postgresql@17-main
tail -n 20 /var/log/postgresql/postgresql-17-main.log
sudo -u postgres psql -c "SELECT name, setting, context FROM pg_settings WHERE name LIKE 'ssl%file' OR name = 'ssl';"

If context for ssl shows postmaster on your build, restart instead of reloading. Certbot's own renewals run the hook automatically through certbot.timer, so don't add a cron job.

Step 4: Least-privilege roles

postgres stays the only superuser and logs in only through the local socket with peer. dbadmin is a human admin who isn't a superuser. appowner owns the schema and can't log in. app_rw is what the application uses, and it can only read and write rows. Use \password so passwords don't end up in shell history or the server log.

-- sudo -u postgres psql
CREATE ROLE dbadmin LOGIN CREATEDB CREATEROLE;
CREATE ROLE appowner NOLOGIN;
CREATE ROLE app_rw LOGIN;
GRANT appowner TO dbadmin;
\password dbadmin
\password app_rw
CREATE DATABASE appdb OWNER appowner;
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_rw, dbadmin;
\c appdb
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA app AUTHORIZATION appowner;
GRANT USAGE ON SCHEMA app TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER ROLE app_rw IN DATABASE appdb SET search_path = app;

Since PostgreSQL 15, new databases no longer give everyone CREATE on public, but every role still has USAGE there. The revoke above removes that too, and it matters on databases upgraded from 14 or older, which still allow CREATE. Migrations run as dbadmin after SET ROLE appowner, so new tables get the default grants.

Check that no login role still has an MD5 hash. A role with an MD5 hash can't authenticate against a scram-sha-256 line:

SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolcanlogin;

Every role with a password should show SCRAM-SHA-256$. Then check the privileges with \dn+ and \ddp in psql.

Step 5: A minimal pg_hba.conf with a lockout-safe reload

Rules are matched top to bottom. The first rule that matches is used, and nothing falls through to later lines. If no rule matches, the connection is refused. That makes a short file the safest file:

# TYPE  DATABASE  USER      ADDRESS        METHOD
local   all       postgres                 peer
hostssl appdb     app_rw    10.8.0.2/32    scram-sha-256
hostssl all       dbadmin   10.8.0.3/32    scram-sha-256

There are no host lines, so plaintext TCP is refused. There is no local all all, and no trust or md5 anywhere.

Warning: a broken pg_hba.conf can lock out every client, including you. Do it in this order: back up the file, open a second root terminal with sudo -u postgres psql and leave it open, and edit the file. Then check the rules before you reload. A reload doesn't close existing sessions, so the open psql is your way back.

cp -a /etc/postgresql/17/main/pg_hba.conf /root/pg_hba.conf.bak

The pg_hba_file_rules view reads the file on disk, not the rules that are loaded, so you can test a change before it goes live:

SELECT rule_number, line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules;

Every row needs an empty error, and the rule order must match what you intended. Only then reload:

SELECT pg_reload_conf();

Open a new local session to make sure you can still get in. To roll back, restore the backup and reload:

cp -a /root/pg_hba.conf.bak /etc/postgresql/17/main/pg_hba.conf
systemctl reload postgresql@17-main

Step 6: Verify TLS, scram-sha-256 and the negative tests

From the application host (10.8.0.2), connect with full verification:

psql 'host=db.example.net dbname=appdb user=app_rw sslmode=verify-full sslrootcert=system'

SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();
CREATE TABLE app.should_fail (id int);

The first query should report t and TLSv1.3. The CREATE TABLE must fail with a permission error, because app_rw can't change the schema.

sslmode=require is not enough. Without a root certificate it encrypts the connection but doesn't check who is on the other end. With sslrootcert=system, libpq uses the system CA store and requires verify-full.

Now the negative tests. If nftables only lets 10.8.0.2 and 10.8.0.3 reach port 5432, a connection from any other host times out. That tests the firewall, not pg_hba.conf. To test pg_hba itself, run these from the allowed app host:

psql 'host=db.example.net dbname=appdb user=dbadmin sslmode=verify-full sslrootcert=system'
psql 'host=db.example.net dbname=appdb user=app_rw sslmode=disable'
psql 'host=db.example.net dbname=postgres user=app_rw sslmode=verify-full sslrootcert=system'

All three must fail with no pg_hba.conf entry for host. The first uses the admin role from the wrong address, the second uses plaintext, and the third asks for the wrong database. A wrong password for app_rw must fail with password authentication failed. On the server, the refusals show up in the log:

grep -E 'no pg_hba.conf entry|password authentication failed' /var/log/postgresql/postgresql-17-main.log

Lines like these are also what you would feed into fail2ban or your alerting.

Step 7: pg_dump and a restore test into a scratch database

pg_dump dumps one database. Roles are cluster-wide, so they need pg_dumpall --globals-only. That file contains password hashes, so protect it like a secret.

install -d -o postgres -g postgres -m 0700 /var/backups/postgresql
sudo -u postgres pg_dump -Fc -f /var/backups/postgresql/appdb.dump appdb
sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals.sql
sudo -u postgres pg_restore --list /var/backups/postgresql/appdb.dump

A dump that hasn't been restored isn't proof of anything. Restore it into a scratch database created from template0 and compare the data:

sudo -u postgres createdb -T template0 appdb_restoretest
sudo -u postgres pg_restore -d appdb_restoretest /var/backups/postgresql/appdb.dump
sudo -u postgres psql -d appdb -Atc 'SELECT count(*) FROM app.orders'
sudo -u postgres psql -d appdb_restoretest -Atc 'SELECT count(*) FROM app.orders'

Replace app.orders with your largest or most important table. The counts must match. Because the roles exist in this cluster, object ownership and schema and table grants come back as well. Database-level grants (the REVOKE ALL and GRANT CONNECT on appdb) and per-database role settings such as the search_path of app_rw are not in globals.sql and are only restored with pg_restore -C (for this custom-format dump; pg_dump --create only applies to plain SQL dumps). On a fresh server, restore globals.sql first (or use --no-owner), and either restore with -C or reapply those database-level statements from Step 4 by hand. Otherwise PUBLIC can connect to appdb again and app_rw loses search_path = app.

Warning: dropdb can't be undone. Check the name twice before you clean up:

sudo -u postgres dropdb appdb_restoretest

Copy the dump files off the host. A restic repository in append-only mode works well, so a compromised server can't delete its own backups.

Common pitfalls

Editing pg_hba.conf in /var/lib/postgresql/17/main. On Debian the live file is in /etc/postgresql/17/main.
Writing hostssl lines while ssl is off. PostgreSQL ignores the lines, and remote clients get no match.
Switching to scram-sha-256 while some roles still have MD5 hashes. Those users are locked out until they set a new password.
Expecting a reload to apply listen_addresses or port. Both need a restart.
Starting PostgreSQL before the WireGuard address exists. The unit is still active, but it listens on localhost only; check the log for could not create listen socket.
Creating tables as dbadmin without SET ROLE appowner. The table belongs to dbadmin, the default privileges don't apply and app_rw gets permission denied for table. Default privileges only apply when an object is created, so after ALTER TABLE ... OWNER TO appowner you still have to grant app_rw its rights by hand.
Trusting pg_hba_file_rules as the live state. It shows the file on disk, which differs from the loaded rules until you reload.
Leaving clients on sslmode=prefer or require. Neither checks the server's identity without a root certificate.
Backing up with pg_dump alone. Without --globals-only you lose roles and their passwords.

Takeaways

pg_lsclusters shows the cluster, its config is in /etc/postgresql/17/main and its unit is postgresql@17-main.
ss -tlnp shows 5432 only on localhost and the WireGuard IP, and the firewall allows only the app host and the admin workstation.
All passwords are SCRAM-SHA-256$ hashes, and pg_hba.conf has only local peer and hostssl scram lines.
pg_hba_file_rules shows no errors before every reload, and a root psql session stays open while you reload.
Certificates are copied by a deploy hook with the key at 0600, and clients use verify-full.
The app role can't change the schema, and PUBLIC has no rights on public or the database.
The negative tests fail the way they should, and the restore test runs on a schedule, not just once.