PostgreSQL SSL/TLS Quick Reference

Quick reference for PostgreSQL SSL/TLS implementation in HeliosDB-Lite.

Quick Start

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

2. Start the Server with TLS

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

--tls-cert and --tls-key must be given together. The server then offers TLS to every client that asks for it.

3. Connect with psql

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

Client SSL Modes

The server accepts TLS when a client requests it. Enforce TLS from the client side with sslmode:

sslmode	Behaviour	Use Case
disable	Never use TLS	Local development only
prefer	Use TLS when offered (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 is planned.

Protocol Flow

SSL Negotiation

1. Client → Server: SSLRequest (code: 80877103)
2. Server → Client: ‘S’ (accepted) or ‘N’ (rejected)
3. Both: TLS handshake (if accepted)
4. Client → Server: Startup message (over TLS)

Message Structure

SSLRequest Message:
┌─────────────┬─────────────────┐
│ Length (4B) │ SSL Code (4B)   │
│ 0x00000008  │ 0x04D2162F      │
└─────────────┴─────────────────┘

Server Response:
┌──────────┐
│ 'S' or 'N'│
└──────────┘

Testing

Test with psql

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

# Verify SSL in psql
\conninfo

# Should show: SSL connection (protocol: TLSv1.3, cipher: ...)

Security Checklist

Production deployment checklist:

• Use CA-signed certificates (not self-signed)
• Set sslmode=require or stricter in every client
• Restrict file permissions (600 for keys, 644 for certs)
• Use strong authentication (--auth scram-sha-256)
• Rotate certificates before expiration
• Test certificate chain validity
• Configure firewall rules

Common Commands

Certificate Management

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

# View certificate details
openssl x509 -in server.crt -text -noout

# Check certificate expiration
openssl x509 -in server.crt -noout -dates

# Verify certificate
openssl verify -CAfile ca.crt server.crt

# Set permissions
chmod 600 server.key
chmod 644 server.crt

Testing Commands

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

# Test without SSL (should fail if required)
psql "sslmode=disable host=127.0.0.1 port=5432 user=postgres"

# Test with certificate verification
psql "sslmode=verify-ca sslrootcert=ca.crt host=127.0.0.1 port=5432 user=postgres"

# Connection string format
psql "postgresql://postgres@127.0.0.1:5432/db?sslmode=require"

Performance Considerations

• SSL/TLS adds ~2-5ms latency per connection establishment
• Minimal overhead for data transfer (AES-GCM hardware acceleration)
• Connection pooling recommended for high-throughput applications

Troubleshooting

Certificate Not Found

# Check file exists
ls -la certs/

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

Permission Denied

# Fix permissions
chmod 600 certs/server.key
chmod 644 certs/server.crt

TLS Handshake Failed

# Check certificate validity
openssl x509 -in certs/server.crt -noout -dates

# Verify certificate format
openssl x509 -in certs/server.crt -text -noout

Enable Debug Logging

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

References

• PostgreSQL SSL Docs: https://www.postgresql.org/docs/current/ssl-tcp.html
• Protocol Spec: https://www.postgresql.org/docs/current/protocol.html

Support

For issues or questions:

1. Enable debug logging: RUST_LOG=debug