---
title: "PostgreSQL SSL/TLS Quick Reference"
---

# PostgreSQL SSL/TLS Quick Reference

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

## Quick Start

### 1. Generate Certificates

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

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

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

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

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

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

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

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

### TLS Handshake Failed

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

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

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