Postgres pg_stat_ssl: TLS Observability for Production Databases

TLS configuration on Postgres tends to be a one-time setup task. The DBA edits postgresql.conf, sets ssl=on, points to a certificate file, restarts the cluster, updates pg_hba.conf to require hostssl for the relevant entries, and considers the work done. The connections then either succeed or fail based on whether the clients negotiate TLS correctly, and the failure mode is loud enough that misconfiguration is usually caught at deployment. What is less commonly checked is the state of TLS on connections that succeed: which TLS version was negotiated, which cipher suite is in use, whether the connection is using a modern key exchange, and whether any client connections are quietly falling through to unencrypted hostssl-not-required fallback rules.

pg_stat_ssl is the system view that exposes per-connection TLS state. It joins to pg_stat_activity by PID and exposes columns for whether the connection is using SSL, which version was negotiated, which cipher suite is in use, the bit count of the key exchange, the DN of the client certificate if one was presented, and the issuer DN. The view is one of the small set of system views that look at what is actually happening on live connections rather than at configuration: it answers the question of what TLS is doing in the present moment, which is different from the question of what TLS is configured to do.

What the view exposes

The view has eight columns. pid joins to pg_stat_activity for context. ssl is a boolean indicating whether the connection is using SSL. version is the negotiated TLS protocol version as a text string like 'TLSv1.3' or 'TLSv1.2'. cipher is the cipher suite name in the OpenSSL convention like 'TLS_AES_256_GCM_SHA384'. bits is the symmetric key length in bits. client_dn, client_serial, and issuer_dn describe the client certificate if one was presented during the handshake, which matters for cert-authenticated environments and is usually null for password or SCRAM-authenticated connections.

The view returns one row per backend, including system backends like autovacuum workers and walsenders. The non-application backends usually do not use SSL because they are not listening on network sockets. Filtering by joining to pg_stat_activity with a condition on backend_type or by excluding null client_addr values produces the rows that matter for diagnosing client connection security. The cumulative view of a long-running cluster shows the SSL state of every connection currently open, which is the right grain for security audit and the wrong grain for historical analysis. Trend data requires periodic sampling into a separate table.

The diagnostic queries

The first diagnostic query is the SSL-vs-non-SSL count by client IP, which identifies whether any connections are slipping through to unencrypted state. The query joins pg_stat_ssl to pg_stat_activity, groups by client_addr and ssl, and counts. The expected output for a well-configured cluster is all rows with ssl=true except for the system backends. Any rows with ssl=false from non-loopback client addresses indicate a configuration gap that should be investigated. The diagnostic is more useful than checking pg_hba.conf alone because it sees the state that actually applied to live connections rather than the state that would apply hypothetically.

The second diagnostic query is the TLS version distribution, which identifies whether any clients are negotiating older protocol versions. The query groups by version and counts. Modern Postgres builds default to a minimum of TLSv1.2, which is the right floor for 2026 and beyond. The presence of TLSv1.2 connections is acceptable; the absence of TLSv1.3 connections is suspicious because most modern client libraries negotiate the newer version automatically. The distribution between TLSv1.2 and TLSv1.3 is partly a function of client library age and is worth knowing as a baseline before tightening the minimum version.

The third diagnostic query is the cipher suite distribution, which identifies whether any connections are negotiating weak or deprecated cipher suites. The query groups by cipher and counts. The cipher names that matter are the AEAD variants like TLS_AES_256_GCM_SHA384 and TLS_CHACHA20_POLY1305_SHA256, which are the TLS 1.3 defaults and the strongest 1.2 options. Any cipher containing CBC or RC4 or MD5 or NULL indicates a weak negotiation that should be removed from the allowed cipher list via ssl_ciphers configuration.

Configuration knobs that matter

The ssl parameter is the master switch and requires a restart to change. The ssl_min_protocol_version and ssl_max_protocol_version parameters control the negotiable range; setting min to TLSv1.2 or TLSv1.3 is the appropriate floor for current deployments. The ssl_ciphers parameter controls the allowed cipher list for TLS 1.2, with HIGH:!aNULL:!MD5:!3DES as a reasonable baseline and the more restrictive list excluding all non-AEAD suites as the right choice for new deployments. TLS 1.3 cipher suite selection is governed by ssl_ciphers on builds linked against OpenSSL 1.1.1 and later.

The ssl_prefer_server_ciphers parameter controls cipher suite priority during negotiation. The right value is on, which gives the server control over which cipher is negotiated when client and server have multiple options. The ssl_passphrase_command parameter handles encrypted private keys and matters mostly for environments where the key file cannot be stored unencrypted. The ssl_dh_params_file parameter is largely obsolete on modern builds that use elliptic curve key exchange by default.

The client certificate authentication path

The client_dn columns are populated when the connection used client certificate authentication, which is governed by the cert method in pg_hba.conf rather than the trust or scram-sha-256 methods. The pattern matters for environments where mutual TLS is a deployment requirement, particularly in regulated industries and in deployments using service mesh certificate distribution. The view provides verification that the right certificates are being presented and that the certificate distinguished names match the expected client identities.

The client certificate path is operationally heavier than password or SCRAM authentication because the certificate lifecycle has to be managed: certificates have to be issued, distributed, rotated, and revoked. The certificate revocation story is the hardest part because Postgres does not support OCSP stapling and relies on a CRL file that has to be updated separately. The cert authentication path is right for some deployments but not for most, and the choice usually depends on whether the team has existing certificate management infrastructure or has to build it.

What the view does not show

The view does not show certificate validity, expiration, or chain trust state. The TLS handshake validated those properties before the connection completed, but the view does not expose them. Verifying certificate state requires reading the certificate files directly or using openssl s_client to test the negotiation. The view also does not show historical TLS state; once a connection closes, the row disappears, and there is no log of past TLS negotiations unless application-side instrumentation captures it. The trend data requires periodic sampling into a separate table.

The view does not show what TLS would be negotiated for hypothetical future connections, which means it cannot detect misconfigurations that will only manifest when a new client connects. The combination with pg_hba.conf inspection and openssl s_client testing is the right pattern: pg_stat_ssl tells you what is actually happening on live connections, pg_hba.conf tells you what policy will apply to future connections, and openssl s_client lets you simulate a future connection to test the policy.

The application across our four products

Our four products run SQLite, which uses local file access rather than TCP connections and has no analog to pg_stat_ssl. The TLS termination for client connections to our APIs happens at Caddy in front of the application processes, so the application-to-database hop is over a local socket and TLS does not apply. The pattern we use to keep visibility on the equivalent surface is Caddy access log inspection for the protocol field, which records the TLS version negotiated for each request. The output is the same kind of distribution data pg_stat_ssl produces, just for HTTPS rather than for Postgres connections.

The Postgres migration plan for the four products preserves the TLS termination at Caddy for the API surface and adds a separate consideration for the Postgres connection between application and database. The right pattern is hostssl with cert authentication for the application-to-database connection, pg_stat_ssl monitoring as a baseline observability investment, and periodic sampling into a stats table for trend analysis. The investment is small but pays back the first time a TLS configuration change has to be diagnosed against actual connection state rather than against configuration intent.

The deeper observation is that observability views for security configuration are easy to overlook because security configuration looks like a one-time setup task. The TLS configuration that worked at deployment usually keeps working, but the small drift cases like a client library that negotiates an older version, a cipher suite that becomes weak in retrospect, a certificate that approaches expiration, or a pg_hba.conf rule that lets a connection through unencrypted are the cases that pg_stat_ssl makes visible. The discipline of looking at the view periodically is cheap and the absence of the discipline is the kind of gap that does not get noticed until an audit or an incident.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.