PostgreSQL SSL/TLS Configuration Guide

This guide covers SSL/TLS encryption for PostgreSQL wire protocol connections in HeliosDB-Lite.

Table of Contents

• Overview
• Certificate Management
• Server Configuration
• Client SSL Modes
• Client Connection
• Testing
• Troubleshooting
• Security Best Practices

Overview

HeliosDB-Lite supports SSL/TLS encryption for PostgreSQL protocol connections, providing secure communication between clients and the database server. The implementation follows the PostgreSQL wire protocol SSL/TLS negotiation standard.

Features

• Standard Protocol: Compatible with PostgreSQL SSL negotiation and every sslmode
• Flexible Certificates: Self-signed and CA-signed certificates (PEM)
• Client-Enforced TLS: The server offers TLS; clients that require it use sslmode=require or stricter

Protocol Flow

1. Client sends SSLRequest message (code: 80877103)
2. Server responds with ‘S’ (SSL supported) or ‘N’ (not supported)
3. If ‘S’, the TLS handshake begins
4. After TLS handshake, normal startup message follows over encrypted connection

Certificate Management

Generating Self-Signed Certificates

For testing and development, use self-signed certificates:

# Create certs directory
mkdir -p certs

# Generate self-signed certificate and private key
openssl req -x509 -newkey rsa:2048 -nodes \
  -keyout certs/server.key \
  -out certs/server.crt \
  -days 365 \
  -subj "/CN=localhost"

# Set appropriate permissions
chmod 600 certs/server.key
chmod 644 certs/server.crt

Production Certificates

For production environments, use CA-signed certificates:

1. Generate Certificate Signing Request (CSR):

openssl req -new -newkey rsa:2048 -nodes \
  -keyout certs/server.key \
  -out certs/server.csr \
  -subj "/CN=your-domain.com"

2. Submit CSR to Certificate Authority (e.g., Let’s Encrypt, DigiCert)

3. Install Certificate:

# Place certificate and key in certs directory
cp server.crt certs/
cp server.key certs/
chmod 600 certs/server.key
chmod 644 certs/server.crt

Certificate Verification

Verify certificate files before using:

openssl x509 -in certs/server.crt -noout -subject -dates
openssl rsa -in certs/server.key -check -noout

Server Configuration

Start the server with a certificate and its private key (PEM). Both flags are required together:

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir /var/lib/heliosdb \
  --tls-cert certs/server.crt --tls-key certs/server.key

Production example with password authentication on all interfaces:

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir /var/lib/heliosdb --listen 0.0.0.0 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --tls-cert /etc/heliosdb/certs/server.crt --tls-key /etc/heliosdb/certs/server.key

With TLS configured, the server accepts TLS from every client that requests it and also accepts clients that do not. Enforce TLS in the clients with sslmode=require or stricter. WAL replication traffic between nodes is encrypted automatically when --tls-cert is set.

Client SSL Modes

sslmode	Behaviour	Use Case
disable	Never use TLS	Local development only
allow	Plaintext first, TLS if the server insists	Transitional
prefer	TLS when available (libpq default)	Migration
require	Fail if TLS is not available	Production
verify-ca	require + verify the server certificate chain	High security
verify-full	verify-ca + verify the host name	Maximum security

Client-certificate authentication (the server verifying client certificates) is planned.

Client Connection

Using psql

# Require SSL
psql "sslmode=require host=127.0.0.1 port=5432 user=postgres dbname=heliosdb"

# Verify the server certificate against your CA
psql "sslmode=verify-ca sslrootcert=/path/to/ca.crt host=127.0.0.1 port=5432 user=postgres"

# Full connection string
psql "postgresql://postgres@127.0.0.1:5432/heliosdb?sslmode=require"

Using PostgreSQL Drivers

Rust (tokio-postgres with postgres-native-tls)

use native_tls::TlsConnector;
use postgres_native_tls::MakeTlsConnector;
use tokio_postgres::config::{Config, SslMode};

let connector = MakeTlsConnector::new(TlsConnector::builder().build()?);

let mut config = Config::new();
config.host("127.0.0.1");
config.port(5432);
config.user("postgres");
config.dbname("heliosdb");
config.ssl_mode(SslMode::Require);

let (client, connection) = config.connect(connector).await?;

Python (psycopg2)

import psycopg2

conn = psycopg2.connect(
    host="127.0.0.1",
    port=5432,
    user="postgres",
    dbname="heliosdb",
    sslmode="require"
)

Node.js (pg)

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

const client = new Client({
  host: '127.0.0.1',
  port: 5432,
  user: 'postgres',
  database: 'heliosdb',
  ssl: {
    rejectUnauthorized: false  // For self-signed certs only
  }
});

await client.connect();

Testing

1. Start an SSL-enabled server (see Server Configuration).

2. Test the SSL connection:

psql "sslmode=require host=127.0.0.1 port=5432 user=postgres"

# Inside psql
\conninfo
# SSL connection (protocol: TLSv1.3, cipher: ...)

3. Inspect the certificate the server presents:

openssl s_client -starttls postgres -connect 127.0.0.1:5432 -showcerts </dev/null

Troubleshooting

Common Issues

”Both —tls-cert and —tls-key must be specified together for TLS.”

Pass both flags, or neither.

Certificate Not Found

Check the paths passed to --tls-cert and --tls-key, or regenerate the files:

mkdir -p certs
openssl req -x509 -newkey rsa:2048 -nodes \
  -keyout certs/server.key \
  -out certs/server.crt \
  -days 365 \
  -subj "/CN=localhost"

Permission Denied

The server user cannot read the key. Set correct file permissions and ownership:

chmod 600 certs/server.key
chmod 644 certs/server.crt

TLS Handshake Failed

1. Verify certificate validity:

openssl x509 -in certs/server.crt -text -noout

2. Check certificate dates:

openssl x509 -in certs/server.crt -noout -dates

3. Check the client trusts the issuing CA (sslrootcert for verify-ca / verify-full).

Debugging

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
RUST_LOG=debug heliosdb-lite start --data-dir ./mydb \
  --tls-cert certs/server.crt --tls-key certs/server.key

Security Best Practices

Production Deployment

1. Use CA-signed certificates (not self-signed)
2. Require TLS in every client (sslmode=require or stricter)
3. Restrict file permissions:

   chmod 600 certs/server.key
   chmod 644 certs/server.crt

4. Rotate certificates regularly (before expiration); restart the server to load new files
5. Use strong authentication (--auth scram-sha-256, not trust)

Certificate Management

1. Store keys securely (encrypted storage, key management systems)
2. Limit key access (Unix permissions, access control lists)
3. Monitor certificate expiration
4. Use separate certificates for each environment

Network Security

1. Use firewall rules to restrict access
2. Use VPN or private networks for additional security
3. Monitor connection attempts in the server log

Compliance

For regulated environments (HIPAA, PCI-DSS, etc.):

1. Require TLS in every client and verify the server certificate (verify-full)
2. Use SCRAM-SHA-256 with per-application roles
3. Encrypt data at rest with TDE (see the Encryption Quick Start)
4. Maintain security documentation

References

• PostgreSQL SSL Documentation
• PostgreSQL Wire Protocol
• OpenSSL Certificate Management