Postgres pg_stat_ssl: Monitoring TLS Connections to Your Database

Every Postgres connection negotiates TLS at the transport layer. Most applications set sslmode=require and consider the matter closed. They are wrong. The configuration is a one-time decision. The ongoing state — which connections are actually encrypted, with what cipher suite, under what TLS version — is left unmonitored. pg_stat_ssl is the view that reveals this state, and most teams have never opened it.

PostgreSQL version9.5+Privilege requiredpg_monitor or superuserRisk of queryingNone — read-only catalog view

What the View Shows

One row per active backend connection, joined on pid:

SELECT pid, ssl, version, cipher, bits, client_dn, client_serial, issuer_dn
FROM pg_stat_ssl;

pid: Process ID. Join with pg_stat_activity for full context.

ssl: Boolean. true if this connection uses TLS. false if plaintext.

version: The TLS protocol version negotiated — TLSv1.2, TLSv1.3, or NULL for plaintext.

cipher: The cipher suite in use — e.g., TLS_AES_256_GCM_SHA384 for TLS 1.3 connections.

bits: Key length in bits. 256 for AES-256, 128 for AES-128.

client_dn: Distinguished name from the client certificate, if mutual TLS is in use. NULL otherwise.

client_serial: Serial number of the client certificate. Useful for auditing which specific cert connected.

issuer_dn: Distinguished name of the certificate authority that signed the client cert.

The Per-Connection TLS Audit Query

Join pg_stat_ssl with pg_stat_activity to get meaningful context:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.client_addr,
  s.ssl,
  s.version,
  s.cipher,
  s.bits,
  a.state,
  a.query_start
FROM pg_stat_activity a
LEFT JOIN pg_stat_ssl s ON a.pid = s.pid
WHERE a.pid <> pg_backend_pid()
ORDER BY s.ssl ASC, a.query_start DESC;

The ORDER BY s.ssl ASC puts plaintext connections at the top. You want to see zeros here.

Detecting Unencrypted Connections

The critical query is simple:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.client_addr,
  s.ssl,
  s.version
FROM pg_stat_activity a
LEFT JOIN pg_stat_ssl s ON a.pid = s.pid
WHERE (s.ssl IS NULL OR s.ssl = false)
  AND a.pid <> pg_backend_pid()
  AND a.client_addr IS NOT NULL;  -- exclude local socket connections

Local socket connections (client_addr IS NULL) bypass TLS — this is expected for connections from the same host. The threat model is network-level eavesdropping, which only applies to TCP connections.

If this query returns rows, you have application code connecting without TLS. Common causes:

An old application using a connection library that defaulted sslmode=disable
A monitoring tool or analytics pipeline with a legacy connection string
A migration script run by hand with a plaintext DSN
A third-party integration configured before your TLS policy was set

The sslmode Hierarchy

From weakest to strongest enforcement on the client side:

disable: Never use TLS. Plaintext only. Never use in production.

allow: Try plaintext first; fall back to TLS if required. Backwards for security.

prefer: Try TLS first; fall back to plaintext if unavailable. The default for most drivers. Still allows unencrypted connections.

require: Require TLS but do not verify the server certificate. Prevents passive eavesdropping but not active MITM attacks.

verify-ca: Require TLS and verify the server certificate was signed by a trusted CA. Prevents MITM if the CA is not compromised.

verify-full: Require TLS, verify the CA, and verify that the server hostname matches the certificate. The only mode that fully prevents MITM attacks.

Production connections should use verify-full with a properly provisioned CA certificate. Most teams running on managed databases (RDS, Cloud SQL, Supabase) have the CA cert available in their provider's documentation.

Cipher Suite Monitoring

TLS 1.0 and TLS 1.1 are deprecated. PCI DSS has required their retirement since 2018. Check whether any connections are still using deprecated versions:

SELECT
  s.version,
  COUNT(*) as connection_count,
  array_agg(DISTINCT a.application_name) as apps
FROM pg_stat_ssl s
JOIN pg_stat_activity a ON s.pid = a.pid
WHERE s.ssl = true
GROUP BY s.version
ORDER BY s.version;

If you see TLSv1 or TLSv1.1, you have client libraries that need updating. Modern OpenSSL versions (1.1.1+) disable TLS 1.0 and 1.1 by default, but older systems with legacy OpenSSL may still negotiate them.

Prefer TLS 1.3 connections. They use AEAD cipher suites exclusively and have a faster handshake. TLS 1.2 is acceptable but requires explicit cipher configuration to avoid weak suites.

pg_hba.conf Enforcement

The application's sslmode setting is client-side. The server enforces TLS policy through pg_hba.conf. Two relevant entry types:

# Require TLS for all connections from 10.0.0.0/8
hostssl  all  all  10.0.0.0/8  scram-sha-256

# Reject non-TLS connections from the same subnet
hostnossl  all  all  10.0.0.0/8  reject

hostssl accepts only TLS connections. hostnossl accepts only plaintext. Using hostnossl ... reject alongside hostssl ... scram-sha-256 enforces TLS at the server, regardless of client sslmode. This is belt-and-suspenders: even if an application connects with sslmode=prefer and falls back to plaintext, the server rejects the connection.

What pg_stat_ssl Does Not Show

The view has meaningful gaps:

Certificate expiry: pg_stat_ssl shows the client certificate's serial number and issuer, but not its expiry date. You need to check the server certificate expiry through OpenSSL directly: openssl s_client -connect your-db-host:5432 -starttls postgres 2>/dev/null | openssl x509 -noout -dates.

Handshake latency: The view captures steady-state connection state, not TLS handshake timing. Session establishment overhead is not visible here.

Client certificate validation failures: Connections that failed mutual TLS authentication never appear in the view — they were rejected before a backend was assigned.

Connection-establishment overhead: TLS adds ~1ms to connection setup time on a modern server. This is only visible through connection profiling, not through this view.

Operational Pattern: Auditing Before Enforcing

The safe path to enforcing TLS everywhere:

Query pg_stat_ssl during production hours to identify all applications and their current TLS status.
Identify any plaintext connections. Track down the application or tool responsible.
Update connection strings to sslmode=require (minimum) or sslmode=verify-full (preferred).
Add hostssl rules to pg_hba.conf for your network ranges.
Add hostnossl ... reject rules to make plaintext connections fail loudly rather than silently succeed.
Monitor pg_stat_ssl weekly. Set an alert on any connection with ssl = false and a non-null client_addr.

The cipher and version columns tell you what you have. The ssl = false rows tell you what you need to fix. Neither will tell you what an encrypted connection is doing or whether the data inside it is sensitive — but that is a problem for your application layer, not your database configuration.

Postgres does not enforce encryption by default. It does expose the tools to enforce it. Most teams deploy the former and skip the latter.

---

Built at builds.anethoth.com — public build dossiers for software projects in progress.