---
title: "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](#overview)
- [SSL Modes](#ssl-modes)
- [Certificate Management](#certificate-management)
- [Server Configuration](#server-configuration)
- [Client Connection](#client-connection)
- [Testing](#testing)
- [Troubleshooting](#troubleshooting)
- [Security Best Practices](#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
- **operationally hardened**: 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.

```rust
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.

```rust
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.

```rust
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.

```rust
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.

```rust
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.

```rust
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

```bash
# 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

```rust
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)**:

```bash
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**:

```bash
# 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:

```rust
use heliosdb_nano::protocol::postgres::CertificateManager;

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

## Server Configuration

### Basic SSL Configuration

```rust
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

```rust
// 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

```rust
// 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:

```rust
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

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

#### Prefer SSL (fallback to non-SSL)

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

#### Disable SSL

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

#### With Custom Root Certificate

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

### Using Connection String

```bash
# 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)

```rust
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)

```python
import psycopg2

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

#### Node.js (pg)

```javascript
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

```bash
# 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**:

```bash
cargo run --example postgres_server_ssl
```

2. **Test SSL connection**:

```bash
# 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):

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

### Integration Testing

```rust
#[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:

```bash
# 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:

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

#### TLS Handshake Failed

**Error**: `TLS handshake failed: InvalidCertificate`

**Solutions**:

1. **Verify certificate validity**:

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

2. **Check certificate dates**:

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

3. **Regenerate expired certificate**:

```bash
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`:

```bash
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**:

```rust
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**:

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

### Debugging

Enable debug logging for SSL troubleshooting:

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

Or set environment variable:

```bash
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**:
   ```bash
   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

```rust
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](https://www.postgresql.org/docs/current/ssl-tcp.html)
- [PostgreSQL Wire Protocol](https://www.postgresql.org/docs/current/protocol.html)
- [rustls Documentation](https://docs.rs/rustls/)
- [OpenSSL Certificate Management](https://www.openssl.org/docs/man1.1.1/man1/openssl-req.html)