PostgreSQL SSL/TLS Configuration Guide¶

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

Table of Contents¶

Overview
SSL Modes
Certificate Management
Server Configuration
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¶

Multiple SSL Modes: Disable, Allow, Prefer, Require, VerifyCA, VerifyFull
Standard Protocol: Compatible with PostgreSQL SSL negotiation
Flexible Configuration: Support for self-signed and CA-signed certificates
Graceful Fallback: Optional support for non-SSL connections
Production Ready: Proper error handling and security measures

Protocol Flow¶

Client sends SSLRequest message (code: 80877103)
Server responds with 'S' (SSL supported) or 'N' (not supported)
If 'S', TLS handshake begins using tokio-rustls
After TLS handshake, normal startup message follows over encrypted connection

SSL Modes¶

HeliosDB-Lite supports six SSL modes, compatible with PostgreSQL client expectations:

Disable¶

SSL connections are completely disabled. All connection attempts must be non-encrypted.

use heliosdb_lite::protocol::postgres::SslMode;

let ssl_config = SslConfig::new(
    SslMode::Disable,
    "certs/server.crt",
    "certs/server.key",
);

Use Case: Development environments where SSL is not needed.

Allow¶

Server accepts both SSL and non-SSL connections. SSL is not preferred.

let ssl_config = SslConfig::new(
    SslMode::Allow,
    "certs/server.crt",
    "certs/server.key",
);

Use Case: Transitional environments supporting legacy clients.

Prefer¶

Server prefers SSL but allows non-SSL fallback.

let ssl_config = SslConfig::new(
    SslMode::Prefer,
    "certs/server.crt",
    "certs/server.key",
);

Use Case: Gradually migrating to SSL-only connections.

Require¶

Server requires SSL connections. Non-SSL connections are rejected.

let ssl_config = SslConfig::new(
    SslMode::Require,
    "certs/server.crt",
    "certs/server.key",
);

Use Case: Production environments requiring encrypted connections.

VerifyCA¶

Server requires SSL and verifies client certificates against a CA.

let ssl_config = SslConfig::new(
    SslMode::VerifyCA,
    "certs/server.crt",
    "certs/server.key",
).with_ca_cert("certs/ca.crt");

Use Case: Environments requiring client certificate authentication.

VerifyFull¶

Server requires SSL and verifies client certificates with hostname validation.

let ssl_config = SslConfig::new(
    SslMode::VerifyFull,
    "certs/server.crt",
    "certs/server.key",
).with_ca_cert("certs/ca.crt");

Use Case: Maximum security environments with strict certificate validation.

Certificate Management¶

Generating Self-Signed Certificates¶

For testing and development, use self-signed certificates:

Using OpenSSL Command Line¶

# 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

Using HeliosDB-Lite API¶

use heliosdb_lite::protocol::postgres::CertificateManager;

// Automatically setup test certificates
let (cert_path, key_path) = CertificateManager::setup_test_certs()?;

// Or generate manually
CertificateManager::generate_self_signed(
    "certs/server.crt",
    "certs/server.key",
    "localhost"
)?;

Production Certificates¶

For production environments, use CA-signed certificates:

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"

Submit CSR to Certificate Authority (e.g., Let's Encrypt, DigiCert)
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:

use heliosdb_lite::protocol::postgres::CertificateManager;

CertificateManager::verify_cert_files(
    "certs/server.crt",
    "certs/server.key"
)?;

Server Configuration¶

Basic SSL Configuration¶

use heliosdb_lite::{EmbeddedDatabase, Result};
use heliosdb_lite::protocol::postgres::{
    PgServerBuilder, SslConfig, SslMode, AuthMethod
};
use std::sync::Arc;

#[tokio::main]
async fn main() -> Result<()> {
    // Create database
    let db = Arc::new(EmbeddedDatabase::new_in_memory()?);

    // Configure SSL
    let ssl_config = SslConfig::new(
        SslMode::Require,
        "certs/server.crt",
        "certs/server.key",
    );

    // Build server
    let server = PgServerBuilder::new()
        .address("127.0.0.1:5432".parse()?)
        .auth_method(AuthMethod::Trust)
        .ssl_config(ssl_config)
        .build(db)?;

    // Start server
    server.serve().await
}

Advanced Configuration¶

// SSL with client certificate verification
let ssl_config = SslConfig::new(
    SslMode::VerifyCA,
    "certs/server.crt",
    "certs/server.key",
)
.with_ca_cert("certs/ca.crt");

let server = PgServerBuilder::new()
    .address("0.0.0.0:5432".parse()?)
    .auth_method(AuthMethod::ScramSha256)
    .ssl_config(ssl_config)
    .max_connections(100)
    .build(db)?;

Using Builder Shortcuts¶

// Quick SSL setup for testing
let server = PgServerBuilder::new()
    .address("127.0.0.1:5432".parse()?)
    .ssl_test()  // Uses default test certificates
    .build(db)?;

Configuration Validation¶

The server validates SSL configuration on startup:

let ssl_config = SslConfig::new(
    SslMode::Require,
    "certs/server.crt",
    "certs/server.key",
);

// Manually validate before server creation
ssl_config.validate()?;

// Or let server builder validate
let server = PgServerBuilder::new()
    .ssl_config(ssl_config)
    .build(db)?;  // Returns error if invalid

Client Connection¶

Using psql¶

Require SSL Connection¶

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

Prefer SSL (fallback to non-SSL)¶

psql "sslmode=prefer host=127.0.0.1 port=5432 user=postgres dbname=heliosdb"

Disable SSL¶

psql "sslmode=disable host=127.0.0.1 port=5432 user=postgres dbname=heliosdb"

With Custom Root Certificate¶

psql "sslmode=verify-ca sslrootcert=/path/to/ca.crt host=127.0.0.1 port=5432 user=postgres"

Using Connection String¶

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

# Connection URI with SSL parameters
psql "postgresql://postgres@127.0.0.1:5432/heliosdb?sslmode=require&sslcert=client.crt&sslkey=client.key"

Using PostgreSQL Drivers¶

Rust (tokio-postgres)¶

use tokio_postgres::{NoTls, Config};

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

let (client, connection) = config.connect(NoTls).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
  }
});

await client.connect();

Testing¶

Running SSL Tests¶

# Run all SSL tests
cargo test postgres_ssl

# Run specific test
cargo test test_ssl_mode_require

# Run with verbose output
cargo test postgres_ssl -- --nocapture

Manual Testing¶

Start SSL-enabled server:

cargo run --example postgres_server_ssl

Test SSL connection:

# Test SSL requirement
psql "sslmode=require host=127.0.0.1 port=5432 user=postgres"

# Verify SSL connection in psql
\conninfo

Test non-SSL rejection (if mode is Require):

# Should fail with SSL required error
psql "sslmode=disable host=127.0.0.1 port=5432 user=postgres"

Integration Testing¶

#[tokio::test]
async fn test_ssl_connection() -> Result<()> {
    // Setup test certificates
    CertificateManager::setup_test_certs()?;

    // Create server
    let db = Arc::new(EmbeddedDatabase::new_in_memory()?);
    let ssl_config = SslConfig::new(
        SslMode::Require,
        "certs/server.crt",
        "certs/server.key",
    );

    let server = PgServerBuilder::new()
        .address("127.0.0.1:15432".parse()?)
        .ssl_config(ssl_config)
        .build(db)?;

    // Start server in background
    tokio::spawn(async move {
        server.serve().await
    });

    // Connect and verify
    // ... test connection logic ...

    Ok(())
}

Troubleshooting¶

Common Issues¶

Certificate Not Found¶

Error: SSL certificate not found: certs/server.crt

Solution: Generate certificates or verify path:

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

# Or use API
cargo run --example postgres_server_ssl

Permission Denied¶

Error: Failed to open private key: Permission denied

Solution: Set correct file permissions:

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

TLS Handshake Failed¶

Error: TLS handshake failed: InvalidCertificate

Solutions:

Verify certificate validity:

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

Check certificate dates:

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

Regenerate expired certificate:

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

SSL Required Error¶

Error: SSL is required but no SSL request was received

Solution: Ensure client uses sslmode=require:

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

Client Certificate Verification Failed¶

Error: Client certificate verification failed

Solutions:

Check CA certificate path:

let ssl_config = SslConfig::new(
    SslMode::VerifyCA,
    "certs/server.crt",
    "certs/server.key",
).with_ca_cert("certs/ca.crt");  // Verify path

Verify client certificate:

openssl verify -CAfile certs/ca.crt client.crt

Debugging¶

Enable debug logging for SSL troubleshooting:

tracing_subscriber::fmt()
    .with_env_filter("debug,heliosdb_lite::protocol::postgres::ssl=trace")
    .init();

Or set environment variable:

RUST_LOG=debug,heliosdb_lite::protocol::postgres::ssl=trace cargo run

Security Best Practices¶

Production Deployment¶

Use CA-signed certificates (not self-signed)
Set appropriate SSL mode (Require or higher)

Restrict file permissions:

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

Rotate certificates regularly (before expiration)
Use strong authentication (SCRAM-SHA-256, not Trust)
Enable client certificate verification for sensitive environments

Configuration Example¶

use heliosdb_lite::protocol::postgres::{
    PgServerBuilder, SslConfig, SslMode, AuthMethod
};

// Production configuration
let ssl_config = SslConfig::new(
    SslMode::VerifyCA,  // Require client certificates
    "/etc/heliosdb/certs/server.crt",
    "/etc/heliosdb/certs/server.key",
).with_ca_cert("/etc/heliosdb/certs/ca.crt");

let server = PgServerBuilder::new()
    .address("0.0.0.0:5432".parse()?)
    .auth_method(AuthMethod::ScramSha256)
    .ssl_config(ssl_config)
    .max_connections(100)
    .build(db)?;

Certificate Management¶

Store keys securely (encrypted storage, key management systems)
Limit key access (Unix permissions, access control lists)
Monitor certificate expiration
Maintain certificate revocation lists (CRLs)
Use separate certificates for each environment

Network Security¶

Use firewall rules to restrict access
Enable TLS 1.2 or higher (handled by rustls)
Disable weak cipher suites (default: strong ciphers only)
Use VPN or private networks for additional security
Monitor SSL connection attempts in logs

Compliance¶

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

Enforce SSL mode Require or higher
Use client certificate authentication (VerifyCA or VerifyFull)
Enable audit logging for all connections
Implement key rotation policies
Maintain security documentation

PostgreSQL SSL/TLS Configuration Guide¶

Table of Contents¶

Overview¶

Features¶

Protocol Flow¶

SSL Modes¶

Disable¶

Allow¶

Prefer¶

Require¶

VerifyCA¶

VerifyFull¶

Certificate Management¶

Generating Self-Signed Certificates¶

Using OpenSSL Command Line¶

Using HeliosDB-Lite API¶

Production Certificates¶

Certificate Verification¶

Server Configuration¶

Basic SSL Configuration¶

Advanced Configuration¶

Using Builder Shortcuts¶

Configuration Validation¶

Client Connection¶

Using psql¶

Require SSL Connection¶

Prefer SSL (fallback to non-SSL)¶

Disable SSL¶

With Custom Root Certificate¶

Using Connection String¶

Using PostgreSQL Drivers¶

Rust (tokio-postgres)¶

Python (psycopg2)¶

Node.js (pg)¶

Testing¶

Running SSL Tests¶

Manual Testing¶

Integration Testing¶

Troubleshooting¶

Common Issues¶

Certificate Not Found¶

Permission Denied¶

TLS Handshake Failed¶

SSL Required Error¶

Client Certificate Verification Failed¶

Debugging¶

Security Best Practices¶

Production Deployment¶

Configuration Example¶

Certificate Management¶

Network Security¶

Compliance¶

References¶