---
title: "PostgreSQL SSL/TLS Configuration Guide"
---

# PostgreSQL SSL/TLS Configuration Guide

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

## Table of Contents

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

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

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

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

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

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

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

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

```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 only
  }
});

await client.connect();
```

## Testing

1. **Start an SSL-enabled server** (see [Server Configuration](#server-configuration)).

2. **Test the SSL connection**:

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

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

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

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

#### TLS Handshake Failed

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. **Check the client trusts the issuing CA** (`sslrootcert` for `verify-ca` / `verify-full`).

### Debugging

```bash
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**:
   ```bash
   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](/docs/lite/guides/encryption_quickstart/))
4. **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)
- [OpenSSL Certificate Management](https://www.openssl.org/docs/man1.1.1/man1/openssl-req.html)