database hardening basics: Postgres edition

database hardening basics: Postgres edition

TL;DR

Postgres ships open for convenience, not for production. Switch pg_hba off trust and onto scram-sha-256, bind only to the interfaces you need, give the app a least-privilege role instead of a superuser, turn on TLS, and revoke the default public permissions. Fifteen minutes of config now beats explaining an open database later.

database hardening basics: Postgres edition

Use this when

• You are putting a new Postgres instance into production
• You inherited a database and suspect default settings
• A security review flagged the database layer
• You need a Postgres hardening checklist with verification steps

Not for this skill when

• You run MySQL, MongoDB, or another database (concepts overlap, commands dont)
• The task is query performance or indexing
• You only need backups (thats necessary but not hardening)

Steps

1. Check what the server is listening on

Find the config file location, then confirm listen_addresses is scoped, not a wildcard facing the world.

psql -U postgres -c "SHOW config_file;" && psql -U postgres -c "SHOW listen_addresses;"

Expected: the config path, and listen_addresses showing specific interfaces or the private address, not everything. If the database doesnt need remote connections at all, the socket alone is the safest binding.

2. Kill trust auth in pg_hba.conf

Trust means anyone who can reach the port is whoever they claim to be. Replace every trust line with scram-sha-256, reload, and verify.

sudo grep -v "^#" /etc/postgresql/[version]/main/pg_hba.conf | grep -v "^$"

Expected: a short list of active rules with no trust entries. Edit the file to use scram-sha-256 for host connections, then SELECT pg_reload_conf(); from psql. Test that the app still connects before you call it done.

3. Stop giving the app a superuser

Create a dedicated role that owns nothing it doesnt need: connect and usage on the database and schema, CRUD on its tables, nothing else. No superuser, no createdb, no replication for app roles.

psql -U postgres -c "CREATE ROLE app_svc WITH LOGIN PASSWORD '[strong-password]'; GRANT CONNECT ON DATABASE myapp TO app_svc;"

Expected: the role exists and the app connects with it. If the app breaks, grant the missing privilege narrowly (one table, one schema) instead of reaching for superuser.

4. Revoke the defaults nobody remembers

Fresh Postgres lets every role create objects in the public schema and read some system views. Tighten that: revoke create on schema public from public, and audit what the public pseudo-role can still touch.

psql -U postgres -d myapp -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC;"

Expected: REVOKE confirmation. Your migrations should run as a separate owner role, not the app role, so the app never needs DDL in production.

5. Turn on TLS and logging

Set ssl to on with a real certificate, and enable connection plus statement logging so you can see who connected and what ran. Logs are your only witness after an incident.

psql -U postgres -c "ALTER SYSTEM SET ssl = 'on';" && psql -U postgres -c "ALTER SYSTEM SET log_connections = 'on';" && psql -U postgres -c "SELECT pg_reload_conf();"

Expected: settings applied after reload; verify with SHOW ssl; returning on. Point the app at the TLS endpoint and confirm it negotiates encryption before you consider this closed.

Variant: postgres security best practices

The wider list: keep Postgres patched (minor releases carry security fixes), separate backup credentials from app credentials, encrypt backups, restrict superuser to local socket peer auth, and review pg_hba after every major upgrade since installers can reset it.

Variant: secure postgresql on a cloud managed service

RDS, Cloud SQL, and friends handle the OS and TLS certs; your job is the same pg_hba-equivalent (security groups and auth rules), least-privilege roles, no public IP on the instance, and storage encryption turned on. Dont assume "managed" means "hardened".

Variant: pg_hba.conf explained

Each line is type, database, user, address, method. Order matters: first match wins, so specific rules go before general ones. When auth mysteriously fails after your edit, you almost certainly have an earlier line matching first.

Why this happens

Postgres defaults optimize for a developer getting started in five minutes: trust auth on local sockets, a superuser named postgres, public schema open to everyone. Those defaults are fine on a laptop and dangerous on a network. Hardening is just walking back each convenience default to the narrowest setting your app still works with.

Edge cases and pitfalls

• Reload vs restart: pghba changes need only a reload, but listenaddresses and ssl need a restart; check which you changed.
• Peer auth surprises: local socket connections may use peer auth, mapping OS users to DB roles; service accounts need entries too.
• Password in shell history: passing the password on the psql command line writes it to history; use a .pgpass file with 600 perms or a prompt instead.
• Upgrades resetting pg_hba: major-version upgrades can install a fresh default file; diff yours against the new one after upgrading.
• App role creep: every "just grant superuser to unblock the deploy" becomes permanent; make the migration role separate so nobody needs it.
• Logging volume: statement logging on a busy database is heavy; start with connections and DDL, add more only if you can store it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_s5WYfS8fp0yzOuqnGG-5Gw