Tested on: Ubuntu 24.04 LTS, PostgreSQL 16.x (apt package from pgdg repository). Commands assume the default data directory at /etc/postgresql/16/main/.

Why this matters

PostgreSQL ships with defaults that are reasonable for a development laptop and wrong for an internet-exposed server. Three settings in particular cause most real-world incidents:

  1. listen_addresses = 'localhost' is fine — until someone changes it to '*' for “convenience” and the database is suddenly reachable from anywhere the firewall lets through.
  2. pg_hba.conf defaults still allow trust and md5 in some packaging. trust is no authentication. md5 is no longer considered safe and should be replaced with scram-sha-256.
  3. TLS is off by default. Application traffic to the database — including passwords and query results — travels in cleartext on the loopback or on the LAN.

This guide fixes those three problems and tightens a handful of related items. It does not cover row-level security, backup hardening, or replication — those get their own guides.

1. Bind only where you need to

Edit /etc/postgresql/16/main/postgresql.conf:

# Bind to loopback only unless an app server needs network access.
# If you need network access, name the specific interface, not '*'.
listen_addresses = 'localhost'
port = 5432

If application servers run on separate hosts, bind to the private interface explicitly (e.g. listen_addresses = '10.0.0.5'), never '*'. Pair this with a host firewall rule that only permits the app server’s IP.

2. Replace md5 and trust with scram-sha-256

Set the default password hashing algorithm in postgresql.conf:

password_encryption = scram-sha-256

Then edit /etc/postgresql/16/main/pg_hba.conf. A safe baseline:

# TYPE   DATABASE   USER          ADDRESS         METHOD
local    all        postgres                      peer
local    all        all                           scram-sha-256
host     all        all           127.0.0.1/32    scram-sha-256
host     all        all           ::1/128         scram-sha-256
# Remote app server — adjust to your topology:
hostssl  appdb      appuser       10.0.0.0/24     scram-sha-256

Notes:

  • No trust. Anywhere.
  • Remote rules use hostssl, not host — this refuses cleartext connections even if TLS negotiation fails.
  • Rotate existing passwords after switching algorithms; old md5 hashes remain until each user’s password is reset.

3. Turn on TLS

Generate a certificate (use Let’s Encrypt or your internal CA — self-signed is acceptable only for short-lived dev environments):

1# Adjust paths to where your cert and key actually live.
2sudo chown postgres:postgres /etc/postgresql/16/main/server.crt \
3                             /etc/postgresql/16/main/server.key
4sudo chmod 600 /etc/postgresql/16/main/server.key

In postgresql.conf:

ssl = on
ssl_cert_file = '/etc/postgresql/16/main/server.crt'
ssl_key_file  = '/etc/postgresql/16/main/server.key'
ssl_min_protocol_version = 'TLSv1.2'
ssl_prefer_server_ciphers = on

4. Log enough to investigate, not enough to leak

log_connections = on
log_disconnections = on
log_hostname = off                 # avoids reverse-DNS overhead and PII
log_line_prefix = '%m [%p] %q%u@%d '
log_statement = 'ddl'              # 'all' is noisy and may capture sensitive data
log_min_duration_statement = 500   # ms — flag slow queries without logging everything

log_statement = 'all' is tempting but logs every query, including ones that embed personal data in literals. Use 'ddl' as a default; switch to 'mod' or 'all' deliberately and only on specific roles.

5. Separate the application role from the superuser

Never connect an application as postgres. Create a role per application, owning its own database:

1CREATE ROLE appuser WITH LOGIN PASSWORD '<generated, stored in secrets manager>';
2CREATE DATABASE appdb OWNER appuser;
3REVOKE ALL ON DATABASE appdb FROM PUBLIC;

On PostgreSQL 15+ the public schema is no longer world-writable by default. On older versions, revoke it explicitly:

1REVOKE CREATE ON SCHEMA public FROM PUBLIC;

6. Apply and verify

1sudo systemctl restart postgresql@16-main
2sudo systemctl status  postgresql@16-main
3
4# Verify it's only listening where you expect.
5sudo ss -ltnp | grep postgres
6
7# Verify TLS is offered.
8psql "host=127.0.0.1 dbname=postgres user=postgres sslmode=require" -c '\conninfo'

Companion script

A copy-paste audit script that reports on each of the above lives at scripts/postgres-audit (drafting in Phase 2).

What this guide deliberately does not cover

  • Row-level security (RLS) — application-dependent, covered separately.
  • Backups and PITR — covered in guides/postgres-backups (planned).
  • Replication TLS and replica permissions — covered in guides/postgres-replication (planned).
  • Database firewalling at the network layer — see guides/ubuntu-baseline for nftables/ufw.