Lock Down pg_hba.conf So Postgres Is Not Public

listen_addresses decides which sockets exist. pg_hba.conf decides who may speak on those sockets. A cluster bound to a private IP with a single line host all all 0.0.0.0/0 scram-sha-256 is still open to every host that can route to that NIC. Password auth is not an allowlist. The file is an ordered list of rules: first match wins. Your job is to make the first match a tight one, and to make the rest reject.

This is the pg_hba.conf you want on a two-tier VPS in Lucknow DC01: local superuser on the unix socket, the app role from the app host only, the owner role from the admin host only, replication only if you actually replica, and nothing from 0.0.0.0/0.

Read the default file before you replace it

Ubuntu's PGDG cluster ships a default that allows local peer and sometimes a wide host line for md5. Open it. Count every host line. If any CIDR is 0.0.0.0/0 or ::/0, that is the line that makes the database public to the routed network.

sudo cp -a /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.bak
sudo grep -n -v -E '^#|^$' /etc/postgresql/16/main/pg_hba.conf
sudo -u postgres psql -c "SHOW hba_file;"
sudo -u postgres psql -c "SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules;"

pg_hba_file_rules is the parsed view. It is what the postmaster actually uses after a reload. Edit the file, reload, query the view. If the view still shows 0.0.0.0/0, the reload did not pick up the file or you edited a copy.

Write rules that name hosts and roles

Use scram-sha-256 for every TCP role. md5 is still accepted for old clients and is weaker. peer for local unix is correct for the postgres OS user. Do not use trust, not even on localhost, not even for a minute of debugging you intend to revert.

# /etc/postgresql/16/main/pg_hba.conf
local   all             postgres                                peer
local   all             all                                     peer
host    appdb           app_runtime     10.0.0.12/32            scram-sha-256
host    appdb           app_owner       10.0.0.20/32            scram-sha-256
host    appdb           app_readonly    10.0.0.20/32            scram-sha-256
host    replication     replicator      10.0.0.30/32            scram-sha-256
host    all             all             0.0.0.0/0               reject
host    all             all             ::/0                    reject

The reject lines are documentation that matches first-match behaviour. A connection from 8.8.8.8 never hits a scram line, so it never gets a password prompt that proves the role exists. That is what you want. A password prompt is already information.

Reload, do not only restart:

sudo systemctl reload postgresql@16-main
sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules;"
psql "host=10.0.0.4 dbname=appdb user=app_runtime sslmode=require" -c "SELECT current_user;"

From 10.0.0.12 the runtime login should work. From any other host it should fail with no pg_hba.conf entry. If it works from your laptop on the public IP, listen_addresses is still a star or UFW is not filtering. pg_hba cannot save a socket you published.

SSL and scram together

scram-sha-256 over a cleartext TCP session still exposes the session after login. On a private NIC inside one VPS network the risk is lower; it is not zero. Turn on SSL in postgresql.conf and require it in the connection URI. HostSSL lines in pg_hba.conf refuse non-TLS clients.

# postgresql.conf
ssl = on
ssl_min_protocol_version = 'TLSv1.2'
# pg_hba.conf example line
hostssl appdb app_runtime 10.0.0.12/32 scram-sha-256

Generate a server cert if you do not already have one. A self-signed cert on the private NIC is better than no TLS, as long as the client pins sslmode=verify-full with a CA you control. sslmode=require encrypts and does not verify; it is a step, not the end.

IPv6 deserves an explicit reject. Ubuntu dual-stacks. A host all all ::/0 scram line is the IPv6 version of opening the world. If you do not use IPv6 for the app, reject it. If you do, write a /128 just like the IPv4 /32.

Do not put the same role on two networks "for convenience". Admin migrations come from 10.0.0.20. The app comes from 10.0.0.12. If you work from a laptop, open an SSH tunnel to 5432 on localhost and use the local peer or a local scram line. That keeps 5432 off the public interface even while you debug.

Netbay's L3/L4 DDoS filtering will not parse pg_hba.conf. It will not stop a host that is already allowed. The allowlist is this file plus UFW plus the bind. Three layers, same CIDR.

After every role change, grep the file for that role name. A leftover host all all line under your tight rules will never run if it is below the first match, but a leftover line above them will. Order is the entire language. Put local peer first, named host rules next, reject last. Comment why a line exists, with the host that owns the CIDR, so the next edit does not "simplify" it back to 0.0.0.0/0.

Takeaway

Name the database, the role, and a /32. Use scram-sha-256, reject the rest, and verify with pg_hba_file_rules plus a failed connection from a host you did not list. Bring up an Ubuntu VPS on Netbay in Lucknow DC01 in under 60 seconds and lock pg_hba.conf before you load production data — netbayhosts.in.