andreidrang/postgreslite-tls repository overview

# Run with auto-generated TLS certificates
docker run -d \
  --name postgres-tls \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/18/main \
  your-dockerhub-username/postgres-tls:18.1

ssl = on
ssl_min_protocol_version = TLSv1.2
ssl_max_protocol_version = TLSv1.3
ssl_ciphers = HIGH:MEDIUM:+3DES:!aNULL
ssl_prefer_server_ciphers = on

# Extract CA certificate from running container
docker cp postgres-tls:/var/lib/postgresql/18/main/certs/ca.crt ./ca.crt

# Or from a volume
docker run --rm -v pgdata:/data alpine cat /data/certs/ca.crt > ca.crt

psql "postgresql://myuser:mypassword@localhost:5432/mydb?sslmode=require"

psql "host=localhost dbname=mydb user=myuser sslmode=verify-ca sslrootcert=./ca.crt"

psql "host=postgres-server dbname=mydb user=myuser sslmode=verify-full sslrootcert=./ca.crt"

import ssl

# Create SSL context with CA verification
ssl_context = ssl.create_default_context(cafile="/path/to/ca.crt")
ssl_context.check_hostname = False  # verify-ca mode
ssl_context.verify_mode = ssl.CERT_REQUIRED

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@host:5432/db",
    connect_args={"ssl": ssl_context}
)

const { Client } = require('pg');
const fs = require('fs');

const client = new Client({
  host: 'localhost',
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword',
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/path/to/ca.crt').toString(),
  }
});

import (
    "crypto/tls"
    "crypto/x509"
    "io/ioutil"
)

caCert, _ := ioutil.ReadFile("/path/to/ca.crt")
caCertPool := x509.NewCertPool()
caCertPool.AppendCertsFromPEM(caCert)

config, _ := pgx.ParseConfig("postgres://user:pass@host:5432/db")
config.TLSConfig = &tls.Config{
    RootCAs:            caCertPool,
    InsecureSkipVerify: false,
    ServerName:         "", // empty for verify-ca
}

# Extract CA certificate
docker cp postgres-tls:/var/lib/postgresql/certs/ca.crt ./ca.crt

# Upload to Cloudflare
npx wrangler cert upload certificate-authority \
  --ca-cert ./ca.crt \
  --name postgres-ca

npx wrangler hyperdrive create my-database \
  --connection-string="postgres://user:pass@your-host:5432/dbname" \
  --ca-certificate-id <CERT_ID_FROM_STEP_1> \
  --sslmode verify-ca

export default {
  async fetch(request: Request, env: Env) {
    const client = new Client(env.HYPERDRIVE.connectionString);
    await client.connect();

    const result = await client.query('SELECT NOW()');

    await client.end();
    return Response.json(result.rows);
  }
};

[databases]
mydb = host=postgres-host port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction

# Backend TLS to PostgreSQL
server_tls_sslmode = verify-ca
server_tls_ca_file = /etc/pgbouncer/ca.crt

docker run -d \
  --name postgres-tls \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -v pgdata:/var/lib/postgresql/18/main \
  your-dockerhub-username/postgres-tls:18.1

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

docker exec postgres-tls ls -la /etc/postgresql.conf
# Should show: -rw-r--r-- postgres postgres

docker exec postgres-tls psql -U myuser -d mydb -c "SHOW ssl;"
# Should return: on

openssl x509 -in ca.crt -text -noout

git clone <repository>
cd postgresql

docker build -f Dockerfile.tls -t postgres-tls:18.1 .