PostgreSQL SSL/TLS Configuration Guide

PostgreSQL SSL/TLS Configuration Guide

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

Table of Contents

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

Overview

HeliosDB Nano 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

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

SSL Modes

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

Disable

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

use heliosdb_nano::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 Nano API

use heliosdb_nano::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:

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:

use heliosdb_nano::protocol::postgres::CertificateManager;

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

Server Configuration

Basic SSL Configuration

use heliosdb_nano::{EmbeddedDatabase, Result};
use heliosdb_nano::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

1. Start SSL-enabled server:

cargo run --example postgres_server_ssl

2. Test SSL connection:

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

# Verify SSL connection in psql
\conninfo

3. 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:

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. 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:

1. 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

2. 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_nano::protocol::postgres::ssl=trace")
    .init();

Or set environment variable:

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

Security Best Practices

Production Deployment

1. Use CA-signed certificates (not self-signed)
2. Set appropriate SSL mode (Require or higher)
3. Restrict file permissions:

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

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

Configuration Example

use heliosdb_nano::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

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

Network Security

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

Compliance

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

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

References

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