Authentication and Encryption

pg_hba.conf is a list of rules read from the top, and the first line whose connection type, client address, database and user all match is the one that decides the connection. There is no fall-through. If that line's method rejects the credentials the lines below it are never consulted, and if nothing matches at all the connection is refused. The order of the lines in this file is the security model.

Cartwheel's copy still carries the line the original install wrote in 2019, and it sits above everything Nadia has added since. Sixteen lines further down there is a careful hostssl rule requiring scram-sha-256 from the application subnet, and it has never once been evaluated for a connection from app-01, because host all all 10.0.0.0/8 trust matched first. Encryption is a separate axis with its own decisions: TLS protects the connection while it is in flight, pgcrypto protects a column, and nothing in core Postgres encrypts the data files.

Reading pg_hba.conf

Each record is five fields: connection type, database, user, client address, method. local covers Unix-domain socket connections and takes no address. host matches TCP connections whether or not they are encrypted, hostssl matches only encrypted ones and hostnossl only unencrypted ones; hostgssenc and hostnogssenc do the same split for GSSAPI-encrypted sessions. Everything else about this file follows from the fact that it is scanned in order.

# TYPE  DATABASE   USER                 ADDRESS       METHOD
host    all        all                  10.0.0.0/8    trust   # 2019. delete.
local   all        postgres                           peer
hostssl cartwheel  cartwheel_app        10.4.1.0/24   scram-sha-256
hostssl cartwheel  cartwheel_analytics  10.4.2.0/24   scram-sha-256
host    all        all                  0.0.0.0/0     reject

Read it the way the server does and the problem is one line deep. Anything inside 10.0.0.0/8, which is the entire private network with the office VPN in it, authenticates as whatever role it names, cartwheel_admin among them, with no credential at all. Delete that line and the file starts doing what it looks like it does: postgres gets in over the socket by peer, the two service roles get in over TLS from their own subnets with a password, everything else is refused. The closing reject is not required, since an unmatched connection is denied anyway, but it makes the intent explicit.

Changes take a reload rather than a restart, either pg_ctl reload or SELECT pg_reload_conf(), and they apply to new connections only, which is what makes this file easy to fix and equally easy to break without noticing. Two habits are worth the seconds they cost: keep the file in version control alongside the rest of the deployment, and check pg_hba_file_rules after every reload, because a line the parser rejected leaves an error there and simply does not exist as far as authentication is concerned.

The Methods That Matter

scram-sha-256 is the correct choice for password authentication and the default for newly set passwords. It is a challenge-response scheme that never puts the password on the wire and stores it server-side in a form that is not directly replayable. md5 is the one being retired: 18 formally deprecates it, CREATE ROLE and ALTER ROLE now warn when an MD5 password is set, and support will be removed in a future major release. The rest each answer a different question. peer maps the operating-system user on local socket connections, which is how postgres logs in on pg-primary; cert authenticates from the client's TLS certificate; ldap, gss for Kerberos, radius, pam and, since 18, oauth push the decision to an external identity system. trust performs no authentication at all, and password sends the secret in clear text.

SHOW password_encryption;                    -- scram-sha-256

SELECT rolname FROM pg_authid
 WHERE rolpassword LIKE 'md5%';             -- the survivors

\password cartwheel_analytics                -- re-hash with the current setting

A password is stored in whatever form it was hashed in when it was set, so changing password_encryption does nothing at all to the passwords already in the cluster — each one has to be set again. Query the catalogue for the survivors, reset every one of them, and only then change the method in pg_hba.conf. Doing it in the other order locks out every role whose stored secret is still an MD5 hash, usually at the moment the reload lands. On 18 the server helps with the audit: setting an MD5 password now raises a deprecation warning, which is how the last few stragglers in deployment scripts get found.

TLS, and What require Does Not Do

The server half is straightforward: ssl = on, a certificate and key the clients' CA will vouch for, and hostssl lines so an unencrypted connection is not merely discouraged but impossible. The client half is where the mistakes live, starting with the default. sslmode defaults to prefer, which tries TLS and falls back to a plaintext connection if the server does not offer it, reporting success either way.

host=pg-primary.cartwheel.example port=5432 dbname=cartwheel
user=cartwheel_app
sslmode=verify-full
sslrootcert=/etc/ssl/certs/cartwheel-ca.crt

verify-full is the only value that checks both that the server's certificate chains to a CA you trust and that the host name you asked for is the one the certificate names. require guarantees only that TLS was used: unless a root certificate file happens to be present it validates nothing, so it stops passive capture on the wire and does not stop a machine in the middle presenting its own certificate and relaying everything. verify-ca checks the chain and not the name, leaving any host with a certificate from the same CA free to impersonate pg-primary. The whole cost of the strongest setting is one CA file distributed to app-01, app-02 and pgbouncer-01.

Encryption at Rest

Core Postgres has no transparent data encryption. The heap files, the indexes and the WAL segments under /var/lib/postgresql/18/main are written in the clear, and no configuration parameter changes that. What exists instead is full-disk or filesystem encryption underneath the database — dm-crypt with LUKS on Linux, or the volume encryption a cloud provider applies by default. That protects against a drive or a machine being physically taken. A mounted filesystem presents the operating system an unencrypted view, so a compromised database account or a successful injection sees plaintext.

Column encryption with pgcrypto is the other answer, and it is narrower than it looks. Cartwheel considered encrypting customers.email and stopped as soon as the login path was priced: every lookup by email becomes a sequential scan with a decrypt per row, because an index on ciphertext cannot answer a query about the plaintext. Ordering, prefix matching and range predicates go with it. The extension is the right tool for a handful of genuinely sensitive columns that are only ever read by primary key, and the extensions topic later in this chapter takes up the question of where the key has to live for any of it to mean something.

Network Position Is Half of It

The database should not be reachable from the internet, and no amount of care in pg_hba.conf substitutes for that. A private subnet, a security group admitting only app-01, app-02, pgbouncer-01 and the two replicas on port 5432, and humans arriving through a bastion: those three are worth more than every line in this topic, and they are the first thing an audit should ask about. listen_addresses is the local half of the same rule, and it should name the interface the cluster actually serves rather than sit wider than the firewall.

Position also changes what the other controls are for. Behind a private subnet, TLS is defending against a compromised neighbour rather than the open internet, and lateral movement is exactly the attack a private network does not stop. That is the case for verify-full restated from the network's side rather than the client's.

Secrets and Rotation

The application's password belongs in a secret manager that the deployment reads at startup, not in the repository, not baked into a container image, and not in a Slack channel. The test of whether that is true is not where the string is stored but whether it can be changed: a credential that needs a coordinated deploy to rotate does not get rotated, and Cartwheel's has not been since 2022.

Rotation has a mechanical problem worth naming. A role has exactly one password, so changing it invalidates the old one instantly and every process still holding the old value fails until it is restarted. The workable patterns are a brief overlap using two roles that hold the same group membership and are swapped a deployment apart, or moving off passwords entirely to certificates or an identity provider that owns the credential's lifetime. That is the same conclusion the previous topic reached from the other direction.

Deleting the trust line, resetting the MD5 passwords and pinning verify-full closes the three findings this topic opened. It does not touch the fourth: cartwheel_analytics now authenticates properly and still reads every row of customers, including the ones the analytics contract says it may not see. Narrowing that without writing a filter into every query is what the next topic does.