Setting Up PostgreSQL SSL/TLS for Connection Encryption

Why PostgreSQL traffic goes out in the clear

By default, the server comes up with encryption disabled: in pg_hba.conf, most networks have a host rule that equally allows both plaintext and secured channels.

A client with sslmode=prefer will silently agree to the unencrypted option if the handshake fails for any reason — and no one will know that the data went in plaintext.

The picture that follows is typical. User passwords, query text, result sets, the change stream to a replica, and exports to the analytics warehouse travel over the network in readable form.

Access to any intermediate node — a switch, a load balancer, a container network overlay — is enough to see both credentials and the data itself.

Inside the perimeter, this seems harmless for years: the network is ours, access is restricted. The bottleneck becomes everything that goes beyond a single rack — a replica in another segment, network backup, analytics and developers on staging environments.

Data security requirements in such setups are not met, and an audit records this as an open finding.

Enabling encryption for database connections itself costs almost nothing: the increase in load per connection is lost against the backdrop of the queries themselves.

We break down how this is closed — from access rules and certificate issuance to verifying that the plaintext channel is truly no longer used.

Configuring SSL/TLS for PostgreSQL: what it closes

Before encryption is enabled, the exchange between clients and PostgreSQL goes in plaintext: credentials at authentication time and the data itself are visible to anyone who gains access to the traffic — port mirroring, an intermediate proxy, or a packet capture.

SSL/TLS turns the channel into an encrypted stream, and this class of failures goes away entirely rather than being partially mitigated.

The mode is set on both sides. In pg_hba.conf, the host line changes to hostssl — unencrypted connections are no longer administratively accepted, and the “forgotten” default configuration does not leave an open door.

On the client, sslmode=require guarantees only encryption, whereas verify-ca and verify-full additionally check the certificate chain and hostname match — these are what close the substitution of the server by an intermediary.

Encryption affects response time less than is commonly thought: the overhead falls on the handshake when a connection is established, not on data transfer.

The pool reuses already established sessions, so a series of queries is served at the same speed, and session resumption eliminates repeated handshakes for clients that reconnect frequently.

A separate topic is channel interruption. An encrypted session breaks explicitly: the client immediately receives an error and goes to reconnect, instead of waiting for a timeout with a “stuck” transaction and a silently lost response.

For security checks, encryption in transit is a mandatory item: scanning and audit look at the protocol version, cipher suites, and certificate validity period.

Deprecated protocols are disabled, certificates are renewed on schedule, and the compliance report is assembled without manual caveats.

Connection protection modes: from certificate verification to a tunnel

In PostgreSQL, channel encryption is enabled by the ssl parameters in postgresql.conf, but the mode is chosen by the client — via sslmode in the connection string and pg_hba.conf rules.

The difference between modes is not in cipher strength, but in whether the server certificate is verified, whether a client certificate is required, and where the encrypted segment terminates.

The combination is chosen based on trust boundaries, not on the principle of “the stricter, the better”: mutual authentication inside the perimeter adds only certificate rotation, while require on a public address leaves server spoofing possible.

The limitations of the modes should be documented in writing — otherwise, during an incident review, it turns out that only the fact of encryption was checked.

Implementation procedure: from schema audit to handover of policies

The work proceeds sequentially: each next stage relies on the confirmed result of the previous one, so switching to encryption does not cut off already running services. Below is the procedure by which we run the project.

1. Connection schema audit. We build a map of consumers: which services connect to the database, from which addresses, which drivers support encryption, and where the connection string is hardcoded in an image or build pipeline. Without this map, any configuration is done blindly and cuts off some clients.
2. Trust model design. We determine the strictness: channel encryption only or mutual verification of client certificates, who signs, what lifetime, how a certificate is revoked. We document the decision in writing — the remaining steps depend on it.
3. Certificate issuance. We generate on a secure node, private keys never leave the server, set file permissions, and prepare the trust chain for verification.
4. Server configuration. We enable encryption, define which connections are allowed, and reload the configuration without stopping service. The first run is in soft mode: first warnings in the log, then a ban on unencrypted sessions.
5. Encryption verification. We connect forcibly with and without encryption, review logs, and make sure the policy is enforced, not merely declared. We separately test behavior with an expired and a spoofed certificate.
6. Handover of policies. We deliver rotation instructions, control points, the procedure for restoring access if a key is lost, and the rollback scenario.

Delivery package: configurations, trust chains, rotation policies

At the output, the customer receives not a set of disparate commands, but a reproducible package for PostgreSQL: configurations, a certificate trust model, and the policies by which the environment lives on.

Each item is accepted based on fact — an applied setting, a verified connection, a rollback rehearsed on a stand.

• Server configurations: enabled SSL, paths to the certificate, key, and chain, minimum allowed protocol version — via managed templates, not edits on a live database.
• Access rules: pg_hba.conf, where application hosts are allowed only via hostssl, and unencrypted connections from outside are explicitly prohibited.
• Trust chains: root and intermediate certificates, the order in which they are deployed, and verification that the key matches the certificate.
• Key rotation: reissuance schedule, the procedure for changing without breaking active connections, and the revocation method via a revocation list.
• Rollback policy: where the previous pair is stored, which steps return the environment to operation, and in what time this is verified on a stand.
• Connection documentation: connection strings, client requirements, a ready checklist for new services and contractors.
• Expiry monitoring: alerts for certificate dates and handshake errors — a warning before expiry, not after.
• Responsibility matrix: who issues, who approves, who revokes.

Case study: closed plaintext traffic between services and replicas

What was there

A fleet of forty services connected to PostgreSQL and replicas in plaintext — access was limited only by network segmentation.

During incident review, it turned out that any machine inside the perimeter could listen to traffic between the application and the database and see the data and authentication strings in full.

For TransLine, the audit recorded this as a category-one risk: database access rested on trust, not on certificate verification.

How we proceeded without downtime

First, we set up an internal certificate authority and issued a separate certificate for each service and each replica. The server was switched to a mode where secured and regular connections are accepted simultaneously — this provided a window for gradual migration.

Then we changed clients one by one: a connection string with mandatory certificate verification (sslmode=verify-full), restarting one instance at a time, checking reads from the replica.

Replication was switched to streaming mode with certificate verification, the base backup was already taken over a secured channel, and regular connections were disabled at the end — as a separate change.

What changed

• share of connections over a secured channel: 0 → 100%, including replication;
• downtime during migration: zero, rollback to any step took minutes;
• p99 response latency did not increase noticeably;
• certificate rotation instead of manual edits on forty services — automatic.

How does termination at a load balancer differ from end-to-end encryption?

Termination at a load balancer means that the secured segment ends at the entry node: the client establishes a TLS session with the load balancer, which decrypts the stream and passes it to the database server in plaintext.

From the outside, the traffic is unreadable; inside the network, it is an ordinary text protocol available to anyone on the same segment.

Here, the intermediate node sees everything: query text, table names, parameter values, and credentials during authentication.

As long as the servers are in one trusted perimeter, this is an acceptable trade-off for simplicity — only one certificate is needed, maintenance comes down to renewal, and the load balancer distributes load based on query content.

End-to-end encryption moves the protection boundary all the way to the PostgreSQL instance itself: the TLS session runs between the client and the server, and the intermediate node sees only addresses, ports, and the volume transferred.

The requirements for configuring ssl postgresql are stricter: every server needs a certificate and key, and clients move to chain and hostname verification — otherwise the connection is encrypted, but node spoofing remains possible.

The cost is loss of content awareness: routing by query type and read/write splitting at the proxy level stop working, and server health checks move to a service connection.

The choice is determined by the threat model: if the internal network is isolated and access is restricted, termination is sufficient; if traffic goes between sites, through third-party segments, or cloud channels, end-to-end encryption is the only option in which an intermediate node does not become a leakage point.

Technical concerns: compatibility, certificates, rollback

Concerns during encryption implementation are usually the same: will an old client fall off, what happens when a certificate is reissued on a live system, how to make sure encryption actually works, and how to roll back if something goes wrong. Below are the answers we give before work begins so that the picture is complete.

How do you reissue certificates without stopping the service?

We place the new certificate next to the old one, and the server reads the updated files on a configuration reload signal, without restarting the process — active connections are not broken.

We keep both certificates in the chain during the transition so that clients with the old root certificate also pass verification. After that — automatic renewal with a margin before expiry and an advance reminder.

Can everything be rolled back?

Yes. We store configuration files and certificates in a version control system, so rollback is switching to the previous set and the same configuration reload.

Administrative access via the local socket does not depend on network settings, so even with a mistake in the rules, we do not lose control of the server and roll back within minutes.

How do you verify that encryption actually works?

We look at connection statistics: the share of secured sessions should be one hundred percent after regular access is closed, and a separate query shows the current connection mode.

We set up continuous monitoring and alerts for attempts to connect without encryption — otherwise a weakened rule will quietly return after someone else edits the configuration.

Let's discuss the task: we will capture the current schema and come back with a plan

A snapshot of the current connection schema is enough — a deep dive before assessment is not needed. Gather what you already have at hand: from it we will reconstruct the topology, see weak points, and estimate the scope of work without extra iterations.

• Output of SHOW ssl; and the ssl_cert_file, ssl_key_file, ssl_ca_file parameters from postgresql.conf (or a note that they are not set) — this is the core of the assessment.
• List of clients that connect to the database: the application, analytics services, replication, admin consoles — with a note on which of them support TLS and which do not.
• How certificates are issued and where they are stored: internal certificate authority, external, or self-signed; who rotates them and how often.
• Access rules in pg_hba.conf line by line — at least a fragment showing authentication methods, to understand what will have to be changed when moving to encryption.
• Network diagram: where load balancers are, where connection proxies are, whether there are intermediate nodes that also need to pass encrypted traffic.
• Current fault-tolerance mode: replicas, backups, maintenance windows — implementation timelines depend on them.

Using this data, we will capture the actual connection picture and record which nodes will have to be updated and in what order so as not to take down running services.

Then we will come back with a work plan: stages, switching order, rollback points, requirements for handshake and certificate renewal monitoring, and realistic implementation timelines.

Describe the task — we will estimate the scope and propose an encryption enablement scheme without downtime.