task-manager/DOCKER-DATABASE-SSL-GUIDE.md

PostgreSQL SSL Configuration Guide 🔒

The Issue

PostgreSQL servers have different SSL requirements:

• Some require SSL encryption
• Some support SSL but don't require it
• Some don't support SSL at all

Your TaskFlow application now supports all three scenarios via the DATABASE_SSL environment variable.

How to Configure

Option 1: No SSL (Default)

Use this for:

• ✅ Local PostgreSQL (docker-compose postgres container)
• ✅ PostgreSQL servers without SSL configured
• ✅ Development environments

Configuration:

# docker-compose.yml or .env
DATABASE_SSL=   # Leave unset or empty

Or simply omit the variable entirely.

Option 2: SSL with Self-Signed Certificates

Use this for:

• ✅ PostgreSQL servers with self-signed certificates
• ✅ Internal/private PostgreSQL servers with SSL
• ✅ Cloud PostgreSQL with SSL enabled

Configuration:

# docker-compose.yml or .env
DATABASE_SSL=true

Option 3: SSL with Valid CA Certificates

Use this for:

• ✅ PostgreSQL servers with CA-signed certificates
• ✅ Public cloud databases (AWS RDS, Google Cloud SQL, etc.)
• ✅ Maximum security requirements

Configuration:

# docker-compose.yml or .env
DATABASE_SSL=require

How to Determine Which Option You Need

Test Method 1: Check Error Messages

Error: "no encryption" or "requires SSL" → Use DATABASE_SSL=true

Error: "does not support SSL" → Use DATABASE_SSL= (unset) or leave blank

Error: "certificate verify failed" → Your server has invalid cert, use DATABASE_SSL=true

No errors, but want encryption → Try DATABASE_SSL=true

Test Method 2: Try Connection

# Test without SSL
psql "postgresql://user:pass@host:5432/db?sslmode=disable"

# Test with SSL
psql "postgresql://user:pass@host:5432/db?sslmode=require"

If the second command works, use DATABASE_SSL=true or DATABASE_SSL=require.

Deployment Scenarios

Scenario 1: Using docker-compose.yml (Local PostgreSQL)

The included PostgreSQL container does not have SSL configured.

Use:

environment:
  DATABASE_URL: postgresql://taskflow:taskflow_password@postgres:5432/taskflow
  DATABASE_SSL:  # Leave empty (no SSL)

Deploy:

docker-compose up -d

Scenario 2: External PostgreSQL (No SSL)

Your own PostgreSQL server without SSL.

Use:

environment:
  DATABASE_URL: postgresql://user:password@your-postgres-server:5432/taskflow
  DATABASE_SSL:  # Leave empty (no SSL)

Scenario 3: External PostgreSQL (With SSL)

Your own PostgreSQL server with SSL enabled.

Use:

environment:
  DATABASE_URL: postgresql://user:password@your-postgres-server:5432/taskflow
  DATABASE_SSL: true  # Enable SSL

Scenario 4: Cloud PostgreSQL (AWS RDS, etc.)

Cloud-hosted databases typically require SSL.

Use:

environment:
  DATABASE_URL: postgresql://user:password@your-rds-endpoint.amazonaws.com:5432/taskflow
  DATABASE_SSL: true  # Enable SSL

For Your Current Setup

Based on the error "The server does not support SSL connections", your PostgreSQL server does not have SSL configured.

Solution:

1. Option A: Keep SSL Disabled (Recommended)

   # Don't set DATABASE_SSL or set it to empty
   DATABASE_SSL=
   

2. Option B: Enable SSL on Your PostgreSQL Server

   If you need encryption, configure your PostgreSQL server to support SSL:

   # On your PostgreSQL server
   # Edit postgresql.conf
   ssl = on
   ssl_cert_file = 'server.crt'
   ssl_key_file = 'server.key'
   
   # Restart PostgreSQL
   systemctl restart postgresql
   

How the Code Works

// server/db.ts
let sslConfig: any = false;  // Default: no SSL

if (process.env.DATABASE_SSL === 'true') {
  sslConfig = { rejectUnauthorized: false };  // SSL with self-signed certs
} else if (process.env.DATABASE_SSL === 'require') {
  sslConfig = { rejectUnauthorized: true };   // SSL with valid certs only
}

pool = new Pool({
  connectionString: databaseUrl,
  ssl: sslConfig,
});

Quick Fix for Your Deployment

Update your deployment configuration:

# If using docker-compose
environment:
  - DATABASE_URL=postgresql://taskflow:password@your-host:5432/taskflow
  - DATABASE_SSL=  # Empty = no SSL

# Or in your shell/environment
export DATABASE_SSL=

Then redeploy:

git add .
git commit -m "Configure PostgreSQL SSL settings"
git push

# After Drone CI builds
docker pull registry.local.nothaft.cloud/taskflow:latest
docker-compose up -d

Expected Success Output

serving on port 5000
Checking database schema...
✓ Database schema is up to date
✓ Created default labels
✓ Database initialized successfully

Troubleshooting

Still Getting SSL Errors?

1. Check your DATABASE_URL

   echo $DATABASE_URL
   # Should not have sslmode parameter
   

2. Verify DATABASE_SSL is not set

   echo $DATABASE_SSL
   # Should be empty
   

3. Test connection manually

   psql "$DATABASE_URL"
   

Connection Refused

• ✅ Check PostgreSQL is running
• ✅ Check firewall allows port 5432
• ✅ Check DATABASE_URL has correct host/port

Authentication Failed

• ✅ Check username in DATABASE_URL
• ✅ Check password in DATABASE_URL
• ✅ Check database exists
• ✅ Check user has permissions

Security Considerations

When to Use SSL

Use SSL when:

• ✅ Connecting over the internet
• ✅ Connecting across networks you don't control
• ✅ Regulatory compliance requires encryption
• ✅ Handling sensitive data

SSL not required when:

• ✅ PostgreSQL and app on same machine
• ✅ Both on same private network
• ✅ Using docker-compose with same network
• ✅ Local development

Self-Signed vs CA Certificates

DATABASE_SSL=true (self-signed)

• Pro: Works with any SSL certificate
• Con: Doesn't verify server identity
• Use: Internal/private servers

DATABASE_SSL=require (CA-signed)

• Pro: Verifies server identity
• Con: Requires valid certificate
• Use: Public cloud, production

Production Ready! 🎉

Your TaskFlow application now supports:

✅ Any PostgreSQL configuration
✅ SSL or no SSL
✅ Self-signed or CA certificates
✅ Flexible deployment options

Status: Production Ready! 🚀

Summary

PostgreSQL Type	DATABASE_SSL Setting	Use Case
Local docker-compose	(unset)	Default setup
Internal server (no SSL)	(unset)	Private network
Internal server (with SSL)	true	Encrypted internal
Cloud database	true	AWS RDS, etc.
High security	require	Valid cert only

---

Next Step: Set DATABASE_SSL appropriately for your PostgreSQL server and redeploy!