Hardening PostgreSQL

PostgreSQL ships more carefully configured than most services on this site. Out of the box it listens only on loopback, defaults password_encryption to scram-sha-256, and — since version 15 — no longer lets every user create objects in the public schema.

So this cluster is mostly not about changing defaults. It’s about the three ways a real cluster ends up weaker than a fresh one:

• pg_hba.conf was edited to get something working, and trust or a wide CIDR was the thing that made it work.
• The cluster was upgraded, and pg_upgrade faithfully preserved settings and privileges that a fresh initdb would no longer give you.
• A packager or image changed the defaults — the official Docker image sets listen_addresses to *, because a container that only accepts loopback is useless.

Each of those is invisible if you only read the documentation’s defaults.

8 controls

• Remove trust authentication from pg_hba.conftrust lets anyone who reaches the port log in as any userSeverity: critical
• Use scram-sha-256, not md5md5 hashes are weak and the method is being removed outrightSeverity: high
• Scope pg_hba.conf rules to real clientsA wide rule grants every database to every role from anywhereSeverity: high
• Check listen_addresses before you change itAn image or packager widened the listener without telling youSeverity: medium
• Enable TLS for PostgreSQLQueries and results cross the network in plaintextSeverity: high
• Lock down the public schemaAny user can create objects in a schema everyone's search_path trustsSeverity: medium
• Grant least privilege to PostgreSQL rolesThe app role can drop what it only needs to readSeverity: medium
• Log PostgreSQL connections and authenticationWithout connection logs there is no record of who authenticatedSeverity: low

Where the config lives

Two files, and which directory depends on how it was installed:

	Debian / Ubuntu	RHEL / Rocky / Alma
Config	/etc/postgresql/18/main/postgresql.conf	/var/lib/pgsql/18/data/postgresql.conf
Host rules	/etc/postgresql/18/main/pg_hba.conf	/var/lib/pgsql/18/data/pg_hba.conf

Substitute your major version. Don’t guess — ask the server:

SHOW config_file;
SHOW hba_file;

Since PostgreSQL 16, pg_hba.conf also supports include, include_if_exists and include_dir, so the file you’re reading may not be the whole ruleset.

Reloading

Most of what’s here takes effect on reload, without dropping connections:

SELECT pg_reload_conf();

sudo systemctl reload postgresql

listen_addresses, ssl (turning it on for the first time) and port need a restart. Everything in pg_hba.conf only needs a reload.

The verification advantage

Postgres is the easiest service here to verify properly, because it will tell you what it actually parsed rather than making you infer it:

SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;

Any row returned is a rule the server rejected. An empty result is the goal, and checking this before a reload is how you avoid discovering a typo when the rules are already live.

Before you start

No lockout risk in the SSH sense — but pg_hba.conf mistakes lock out applications, and a bad rule can lock out you. Keep an open psql session while you work: an established connection is unaffected by a reload, so it stays usable as a way back in.