Fix PostgreSQL SSL Connection Required Error: Root Causes and Minimal Checks

Quick Answer

• The "SSL connection required" error occurs when a PostgreSQL server is configured to require SSL but the client attempts a non-SSL connection, or when SSL certificate validation fails.
• First check: verify the server's ssl setting is on in postgresql.conf, and confirm the pg_hba.conf entry for your client uses hostssl instead of host.
• Minimal fix: set the client connection parameter sslmode=require (or verify-full for production) in your connection string or environment variable PGSSLMODE.
• For cloud databases (AWS RDS, Google Cloud SQL, Azure DB), download the provider's CA certificate and use sslmode=verify-full with the correct sslrootcert path.
• This guidance applies to PostgreSQL 9.6+ with SSL support compiled in; verify with SHOW ssl; on the server.

What Problem It Solves

PostgreSQL supports SSL/TLS encryption for client-server connections to protect data in transit. When a server enforces SSL (via ssl=on in postgresql.conf and hostssl entries in pg_hba.conf), clients that attempt non-SSL connections receive a fatal error. This document provides the exact configuration steps, client code examples, and troubleshooting procedures to resolve SSL connection failures across development, testing, and production environments.

When This Error or Setup Appears

The "SSL connection required" error typically appears in these scenarios:

• Connecting to a cloud-managed PostgreSQL instance (AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL) that enforces SSL by default
• Migrating from a local development database to a production database with SSL requirements
• Configuring a new PostgreSQL server with SSL enabled for the first time
• Using client libraries (psycopg2, node-postgres, JDBC, lib/pq) that default to sslmode=prefer or sslmode=disable

Root Cause Analysis

The error has three primary root causes:

1. Server-side SSL not enabled: The ssl parameter in postgresql.conf is off or missing, or the required certificate/key files (ssl_cert_file, ssl_key_file) are not properly configured.

2. pg_hba.conf mismatch: The client's connection attempt matches a host (non-SSL) rule instead of a hostssl (SSL-required) rule. PostgreSQL evaluates pg_hba.conf records in order and uses the first match.

3. Client-side SSL mode too permissive: The client uses sslmode=disable or sslmode=prefer (the default in many drivers) when the server requires SSL. The prefer mode tries SSL first but falls back to non-SSL, which fails if the server rejects non-SSL connections.

Minimal Working Configuration

Server-Side Setup

Edit postgresql.conf:

INI
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
# Optional but recommended:
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_min_protocol_version = 'TLSv1.2'

Edit pg_hba.conf to require SSL for specific clients:

# Require SSL for all remote connections
hostssl all all 0.0.0.0/0 scram-sha-256

# Allow non-SSL only on local socket (not network)
local  all all scram-sha-256

Reload configuration:

BASH
pg_ctl reload
# or
SELECT pg_reload_conf();

Client-Side Connection Strings

Python (psycopg2):

PYTHON
import psycopg2

# Development (encrypt only, no certificate validation)
conn = psycopg2.connect("postgresql://user:pass@host:5432/db?sslmode=require")

# Production (encrypt + validate CA + verify hostname)
conn = psycopg2.connect(
    "postgresql://user:pass@host:5432/db?sslmode=verify-full&sslrootcert=/path/to/ca.pem"
)

Node.js (node-postgres):

JAVASCRIPT
const { Client } = require('pg');

// Development
const client = new Client({
  connectionString: 'postgresql://user:pass@host:5432/db',
  ssl: { rejectUnauthorized: false }
});

// Production
const client = new Client({
  connectionString: 'postgresql://user:pass@host:5432/db',
  ssl: {
    ca: fs.readFileSync('/path/to/ca.pem').toString(),
    rejectUnauthorized: true
  }
});

Java (JDBC):

JAVA
// Development
String url = "jdbc:postgresql://host:5432/db?sslmode=require";

// Production
String url = "jdbc:postgresql://host:5432/db?sslmode=verify-full&sslrootcert=/path/to/ca.pem";

Go (lib/pq):

GO
import "database/sql"
import _ "github.com/lib/pq"

// Development
connStr := "postgres://user:pass@host:5432/db?sslmode=require"

// Production
connStr := "postgres://user:pass@host:5432/db?sslmode=verify-full&sslrootcert=/path/to/ca.pem"
db, err := sql.Open("postgres", connStr)

Parameters and Environment Variables

Connection String Parameters

Environment Variables

Common Errors and Fixes

Error: FATAL: no pg_hba.conf entry for host "192.168.1.100", user "myuser", database "mydb", SSL off

Cause: The client matches a host (non-SSL) rule that doesn't exist, or the existing rule requires SSL but the client connects without it.

Fix: Add a hostssl entry in pg_hba.conf:

hostssl mydb myuser 192.168.1.100/32 scram-sha-256

Then reload: pg_ctl reload

Error: SSL error: certificate verify failed

Cause: The client's CA certificate (sslrootcert) does not match the CA that signed the server certificate.

Fix: Verify the certificate chain:

BASH
openssl verify -CAfile ca.crt server.crt

If using self-signed certificates, temporarily use sslmode=require for testing only.

Error: SSL error: hostname mismatch

Cause: The server certificate's Common Name (CN) or Subject Alternative Name (SAN) does not match the hostname used in the connection string.

Fix: Check the certificate:

BASH
openssl x509 -in server.crt -noout -text | grep -E 'Subject:|DNS:'

If the certificate cannot be regenerated, use sslmode=verify-ca (validates CA but not hostname).

Error: sslmode value "require" invalid when SSL support is not compiled in

Cause: The PostgreSQL client library was compiled without SSL support.

Fix: Reinstall the client library with SSL support:

BASH
# Ubuntu/Debian
sudo apt-get install libpq-dev

# macOS
brew install libpq

# Python (use binary package with SSL)
pip install psycopg2-binary

Production Notes and Security Checks

1. Never use rejectUnauthorized: false or sslmode=require in production. These settings disable certificate validation and expose you to man-in-the-middle attacks. Always use sslmode=verify-full with a trusted CA certificate.

2. Certificate rotation: Set up automated renewal for SSL certificates. Cloud providers typically provide updated CA bundles; download and deploy them before expiration.

3. Performance impact: SSL encryption adds CPU overhead and increases latency. For high-throughput applications, consider connection pooling to amortize the SSL handshake cost.

4. Firewall considerations: Ensure port 5432 (or your custom port) is open for SSL traffic. Some firewalls inspect or block SSL traffic; you may need to configure SSL inspection exceptions.

5. Client library compatibility: Older PostgreSQL client libraries may not support TLSv1.2+. Verify your client version supports ssl_min_protocol_version = 'TLSv1.2' if you enforce it server-side.

6. Multi-environment consistency: Use the same SSL configuration approach across development, staging, and production. In development, use self-signed certificates with sslmode=require; in production, use CA-signed certificates with sslmode=verify-full.

FAQ

Q: How do I configure PostgreSQL SSL in an MCP server?

A: In your MCP server configuration, set the connection string's sslmode parameter to require or verify-full. For example:

JSON
{
  "mcpServers": {
    "postgres-ssl-fix": {
      "command": "python",
      "args": [
        "-c",
        "import psycopg2; conn = psycopg2.connect('postgresql://user:pass@host:5432/db?sslmode=require'); print('SSL connection successful')"
      ],
      "env": {
        "PGSSLMODE": "require",
        "PGSSLROOTCERT": "/path/to/ca-certificate.crt"
      }
    }
  }
}

Ensure the MCP server runtime has read permissions for the certificate file.

Q: How do I get the correct SSL certificate for cloud databases?

A: For AWS RDS, download the global-bundle.pem from the AWS documentation. For Google Cloud SQL, download the server CA certificate from the Cloud Console. For Azure Database for PostgreSQL, download the DigiCert Global Root G2 certificate. Always use the latest version and update before expiration.

Q: How can I test SSL connections in development without real certificates?

A: Generate self-signed certificates using OpenSSL:

BASH
# Generate CA key and certificate
openssl req -new -x509 -days 365 -nodes -text -out ca.crt -keyout ca.key -subj "/CN=MyCA"

# Generate server key and certificate signed by CA
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=localhost"
openssl x509 -req -in server.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt

Then connect with sslmode=require (encryption only) or sslmode=verify-ca with sslrootcert=ca.crt. Never use self-signed certificates in production.