Client authentication and TLS

conceptThe concept

A new connection passes three gates:

1. listen_addresses and the port decide whether the server accepts the TCP connection at all.
2. pg_hba.conf picks the authentication method. Each line is type database user address method [options]. The server checks lines from top to bottom and uses the first line whose type, database, user and address all match. If that method fails, there is no fallback to later lines.
3. The method runs, then the role must have LOGIN and CONNECT on the database.

TLS is separate from authentication. ssl = on plus a certificate and key lets clients encrypt; a hostssl line forces it. The client decides how much to verify with sslmode: require encrypts but trusts any certificate, verify-ca checks the chain, verify-full also checks the host name. Only verify-full stops a man-in-the-middle.

whyWhy it matters

The most common incident pattern is a host all all 0.0.0.0/0 trust line added "temporarily" during a migration. The second is an application that connects with sslmode=prefer (the libpq default) and silently falls back to plaintext. Both are configuration, not code, so they survive every code review.

Use caseReal-world scenario

A payments database allows the application subnet with SCRAM over TLS, the DBA team only through a bastion with client certificates, and the postgres OS account through the local socket with peer. Everything else hits a final reject line, and the hba file is in version control with a CI check against pg_hba_file_rules.

Try itExamples

Run and edit these in the interactive lesson.

SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
ORDER BY line_number;

BEGIN;
CREATE ROLE app_login LOGIN PASSWORD 'correct horse battery';
SELECT rolname,
       split_part(rolpassword, '$', 1) AS scheme,
       split_part(split_part(rolpassword, '$', 2), ':', 1) AS iterations
FROM pg_authid WHERE rolname = 'app_login';
SET LOCAL password_encryption = 'md5';
CREATE ROLE old_login LOGIN PASSWORD 'secret';
SELECT rolname, left(rolpassword, 3) AS prefix FROM pg_authid WHERE rolname = 'old_login';
ROLLBACK;

SELECT name, setting
FROM pg_settings
WHERE name IN ('listen_addresses', 'password_encryption', 'scram_iterations', 'authentication_timeout',
               'ssl', 'ssl_min_protocol_version', 'md5_password_warnings')
ORDER BY name;

SELECT a.pid, a.usename, a.client_addr, s.ssl, s.version, s.cipher
FROM pg_stat_activity a
LEFT JOIN pg_stat_ssl s ON s.pid = a.pid
WHERE a.pid = pg_backend_pid();

Specific lines first, a catch-all reject last. +dba means members of the dba role. After editing: SELECT pg_reload_conf(); then check pg_hba_file_rules for errors.

# TYPE    DATABASE   USER        ADDRESS          METHOD
local     all        postgres                     peer
local     all        all                          scram-sha-256
hostssl   payments   app_rw      10.20.0.0/16     scram-sha-256
hostssl   all        +dba        10.99.1.10/32    cert clientcert=verify-full
hostssl   replication replicator 10.20.5.0/24     scram-sha-256
host      all        all         0.0.0.0/0        reject
host      all        all         ::/0             reject

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/tls/server.crt'
ssl_key_file  = '/etc/postgresql/tls/server.key'   # mode 0600, owned by postgres
ssl_ca_file   = '/etc/postgresql/tls/ca.crt'        # to verify client certificates
ssl_min_protocol_version = 'TLSv1.2'

# client connection string
postgresql://app_rw@db1.payments.internal:5432/payments?sslmode=verify-full&sslrootcert=/etc/ssl/ca.crt

internalsUnder the hood

The postmaster parses pg_hba.conf at start and on reload (SELECT pg_reload_conf() or SIGHUP). A file with an error is not loaded: the previous rules stay in force and the error appears in the log and in the error column of pg_hba_file_rules, so always check that view after editing. A SCRAM verifier in pg_authid.rolpassword looks like SCRAM-SHA-256$iterations:salt$StoredKey:ServerKey; the server can check a proof without ever knowing the password. scram_iterations (PostgreSQL 16+) raises the cost for new passwords.

Avoid / preferDo this, not that

host  all  all  0.0.0.0/0  trust

hostssl  payments  app_rw  10.20.0.0/16  scram-sha-256
host     all       all     0.0.0.0/0     reject

postgresql://app@db1/payments?sslmode=require

postgresql://app@db1/payments?sslmode=verify-full&sslrootcert=ca.crt

PitfallsCommon mistakes

• Adding a broad rule above a narrow one and wondering why the narrow one never applies: first match wins.
• Editing pg_hba.conf and forgetting to reload, or reloading a file with a typo so the old rules stay active.
• Switching password_encryption to scram-sha-256 and expecting existing MD5 hashes to change: each role must set its password again.
• Relying on the libpq default sslmode=prefer, which silently falls back to an unencrypted connection.

LimitsWhen not to use it

Do not build your own login layer with a shared database role and an application-side password table for human users; use per-person roles with central identity (LDAP, Kerberos, certificates or OAuth) so access can be revoked in one place. Client certificates are also overkill for short-lived local development databases.

SecuritySecurity notes

Keep pg_hba.conf, server keys and certificates readable only by the postgres OS user. Rotate passwords to SCRAM before PostgreSQL removes MD5 support, monitor pg_stat_ssl for unencrypted sessions, and log failed connection attempts (they appear as FATAL lines) so password spraying is visible.

ChallengeTry it yourself

SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE auth_method = 'trust' AND type LIKE 'host%'
ORDER BY line_number;

InterviewQuestions you should be able to answer

QuizCheck your understanding

5 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Privileges
• Row security policies
• Predefined roles