Harden PostgreSQL SSL for Self-Hosted Supabase

Introduction

By default, self-hosted Supabase allows unencrypted database connections. While Docker's internal network provides some isolation, any client connecting from outside the server (your applications, database tools, or attackers) can send credentials and query data in plain text.

This tutorial shows you how to:

1. Generate self-signed SSL certificates for PostgreSQL
2. Configure PostgreSQL to require SSL for external connections
3. Enable SSL on Supavisor (the connection pooler)
4. Verify that unencrypted connections are rejected

Prerequisites

• A running self-hosted Supabase instance (see "Deploy Self-Hosted Supabase on Hetzner Cloud with Coolify")
• SSH access to your server
• Access to Coolify dashboard

Important: You must complete the initial Supabase deployment and verify it works before applying these hardening steps. Applying SSL configuration before initialization will break the Supabase setup scripts.

Step 1 - Identify Your Service UUID

Coolify assigns a unique UUID to each service. You'll need this to locate your Supabase data directories.

In Coolify:

1. Navigate to your Supabase service
2. Look at the URL — it contains the service UUID

   https://<kong-string>.supabase.<alphanumeric-string>.example.com/project/<project_uuid>/environment/<environment_uuid>/service/<service_uuid>

3. Or check the service's General tab

Example: w8s4k8w88scgg8g048w8sko8

Now that you have the service ID, connect to the server on which the Supabase service runs.

Set this as a variable for the following steps:

export SERVICE_UUID="w8s4k8w88scgg8g048w8sko8"
export SSL_DIR="/data/coolify/services/${SERVICE_UUID}/volumes/ssl"
export DB_DIR="/data/coolify/services/${SERVICE_UUID}/volumes/db"

Step 2 - Generate SSL Certificates

You'll create a self-signed Certificate Authority (CA) and use it to sign a server certificate.

Step 2.1 - Create SSL directory

mkdir -p "${SSL_DIR}"

Step 2.2 - Generate CA key and certificate

# Generate CA private key (4096-bit for long-term security)
openssl genrsa -out "${SSL_DIR}/ca.key" 4096

# Generate CA certificate (valid for 10 years)
openssl req -x509 -new -nodes \
  -key "${SSL_DIR}/ca.key" \
  -sha256 -days 3650 \
  -out "${SSL_DIR}/ca.crt" \
  -subj "/CN=Supabase Self-Hosted CA"

Step 2.3 - Generate server key and certificate signing request

# Generate server private key
openssl genrsa -out "${SSL_DIR}/server.key" 2048

# Generate certificate signing request
openssl req -new \
  -key "${SSL_DIR}/server.key" \
  -out "${SSL_DIR}/server.csr" \
  -subj "/CN=supabase-db"

Step 2.4 - Create certificate extensions file

This file defines which hostnames and IPs the certificate is valid for:

Important: Replace DNS.4 with your actual domain pattern.

cat > "${SSL_DIR}/server.ext" << EOF
authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names

[alt_names]
DNS.1 = supabase-db
DNS.2 = localhost
DNS.3 = supabase-supavisor
DNS.4 = *.supabase.<alphanumeric-string>.<your-domain.com>
IP.1 = 127.0.0.1
EOF

Step 2.5 - Generate signed server certificate

openssl x509 -req \
  -in "${SSL_DIR}/server.csr" \
  -CA "${SSL_DIR}/ca.crt" \
  -CAkey "${SSL_DIR}/ca.key" \
  -CAcreateserial \
  -out "${SSL_DIR}/server.crt" \
  -days 3650 \
  -sha256 \
  -extfile "${SSL_DIR}/server.ext"

Step 2.6 - Set proper permissions

chmod 600 "${SSL_DIR}/server.key"
chmod 644 "${SSL_DIR}/server.crt"
chmod 644 "${SSL_DIR}/ca.crt"

Step 2.7 - Set ownership for PostgreSQL

PostgreSQL runs as a specific user inside the container. Check the UID/GID:

docker run --rm supabase/postgres:15.8.1.048 id postgres

Expected output: uid=105(postgres) gid=106(postgres)

Set ownership:

chown 105:106 "${SSL_DIR}/server.key"

Step 3 - Configure PostgreSQL Host-Based Authentication

Create a pg_hba.conf file that:

• Allows internal Docker traffic without SSL (for container-to-container communication)
• Requires SSL for all external connections
• Explicitly rejects non-SSL external connections

cat > "${DB_DIR}/pg_hba.conf" << 'EOF'
# TYPE  DATABASE    USER            ADDRESS         METHOD

# ============================================
# Local connections (preserve existing)
# ============================================
local   all         supabase_admin                  scram-sha-256
local   all         all                             peer map=supabase_map
host    all         all             127.0.0.1/32    trust
host    all         all             ::1/128         trust

# ============================================
# Docker internal networks (no SSL required)
# Container-to-container traffic never leaves host
# ============================================
host    all         all             10.0.0.0/8      scram-sha-256
host    all         all             172.16.0.0/12   scram-sha-256
host    all         all             192.168.0.0/16  scram-sha-256

# ============================================
# External connections - REQUIRE SSL
# ============================================
hostssl all         all             0.0.0.0/0       scram-sha-256
hostssl all         all             ::0/0           scram-sha-256

# Reject non-SSL external connections
hostnossl all       all             0.0.0.0/0       reject
hostnossl all       all             ::0/0           reject
EOF

Step 4 - Update PostgreSQL Configuration in Coolify

In Coolify UI, go to "Projects" → your Supabase project → your Supabase service. In the Supabase service overview, select Edit Compose File next to "Service Stack". In the compose file, navigate to the service supabase-db.

Step 4.1 - Update the command

Find the current command section and update it to include SSL parameters:

Before:

    command:
      - postgres
      - "-c"
      - config_file=/etc/postgresql/postgresql.conf
      - "-c"
      - log_min_messages=fatal

After:

    command:
      - postgres
      - "-c"
      - config_file=/etc/postgresql/postgresql.conf
      - "-c"
      - hba_file=/etc/postgresql/pg_hba.conf
      - "-c"
      - ssl=on
      - "-c"
      - ssl_cert_file=/var/lib/postgresql/ssl/server.crt
      - "-c"
      - ssl_key_file=/var/lib/postgresql/ssl/server.key
      - "-c"
      - ssl_ca_file=/var/lib/postgresql/ssl/ca.crt
      - "-c"
      - log_min_messages=fatal

Step 4.2 - Add volume mounts

Add these volume mounts to the supabase-db service:

Replace <SERVICE_UUID> with your actual UUID.

    volumes:
      # ... existing volumes ...
      - "/data/coolify/services/<SERVICE_UUID>/volumes/ssl:/var/lib/postgresql/ssl:ro"
      - "/data/coolify/services/<SERVICE_UUID>/volumes/db/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro"

Step 5 - Configure Supavisor for SSL

Supavisor is the connection pooler that handles ports 5432 (direct) and 6543 (pooled). It also needs SSL configuration.

In Coolify UI, go to "Projects" → your Supabase project → your Supabase service. In the Supabase service overview, select Edit Compose File next to "Service Stack". In the compose file, navigate to the service supabase-supavisor.

Step 5.1 - Add volume mounts

Replace <SERVICE_UUID> with your actual UUID.

    volumes:
      # ... existing volumes ...
      - "/data/coolify/services/<SERVICE_UUID>/volumes/ssl/server.crt:/etc/ssl/server.crt"
      - "/data/coolify/services/<SERVICE_UUID>/volumes/ssl/server.key:/etc/ssl/server.key"

Step 5.2 - Add environment variables

      - GLOBAL_DOWNSTREAM_CERT_PATH=/etc/ssl/server.crt
      - GLOBAL_DOWNSTREAM_KEY_PATH=/etc/ssl/server.key

Step 5.3 - Ensure ports are exposed

    ports:
      - "6543:6543"
      - "5432:5432"

Important: If you followed "Deploy Self-Hosted Supabase on Hetzner Cloud with Coolify", you added ports: - '5432:5432' to the supabase-db service for initial testing. You must remove that port exposure from supabase-db now, as Supavisor will handle port 5432 with SSL enforcement. Having both services expose the same port will cause a conflict.

Step 5.4 - Enable SSL enforcement in pooler configuration

SSH into the server on which the Supabase service runs, and edit the pooler configuration:

vim /data/coolify/services/<SERVICE_UUID>/volumes/pooler/pooler.exs

Add or ensure this line exists:

    "enforce_ssl" => true,

Step 6 - Deploy Changes

Step 6.1 - Pre-deployment checklist

Before redeploying, verify you have:

• Generated SSL certificates with correct ownership
• Created pg_hba.conf with proper rules
• Updated supabase-db command with SSL configuration
• Added SSL volume mounts to supabase-db
• Added SSL volume mounts to supabase-supavisor
• Added SSL environment variables to supabase-supavisor
• Updated pooler.exs with enforce_ssl

Step 6.2 - Redeploy

In Coolify, click Deploy to apply the hardening configuration.

Wait for all services to become healthy.

Step 7 - Verify SSL Enforcement

Step 7.1 - Test SSL connection works

From your local machine:

psql "postgresql://postgres.<TENANT_ID>:<password>@<your-domain>:5432/postgres?sslmode=require" -c '\conninfo'

Expected output:

You are connected to database "postgres" as user "postgres.<TENANT_ID>" on host "<your-domain>" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

Test the pooler port as well:

psql "postgresql://postgres.<TENANT_ID>:<password>@<your-domain>:6543/postgres?sslmode=require" -c '\conninfo'

Step 7.2 - Verify unencrypted connections are rejected

Attempt to connect without SSL:

psql "postgresql://postgres.<TENANT_ID>:<password>@<your-domain>:5432/postgres?sslmode=disable" -c '\conninfo'

Expected output:

psql: error: connection to server at "<your-domain>" (<ip>), port 5432 failed: FATAL:  SSL connection is required

Test the pooler port:

psql "postgresql://postgres.<TENANT_ID>:<password>@<your-domain>:6543/postgres?sslmode=disable" -c '\conninfo'

Expected output:

psql: error: FATAL:  SSL connection is required

Step 8 - Update Connection Strings

After enabling SSL, update your application connection strings to include sslmode=require:

Direct connection (for migrations):

postgresql://postgres.<TENANT_ID>:[PASSWORD]@<domain>:5432/postgres?sslmode=require

Pooled connection (for application runtime):

postgresql://postgres.<TENANT_ID>:[PASSWORD]@<domain>:6543/postgres?sslmode=require&pgbouncer=true

Prisma example (.env file):

DATABASE_URL="postgresql://postgres.<TENANT_ID>:[PASSWORD]@<domain>:6543/postgres?sslmode=require&pgbouncer=true"
DIRECT_URL="postgresql://postgres.<TENANT_ID>:[PASSWORD]@<domain>:5432/postgres?sslmode=require"

Conclusion

Your self-hosted Supabase instance now enforces SSL/TLS encryption for all external database connections. This ensures:

• ✅ All credentials are encrypted in transit
• ✅ All query data is encrypted in transit
• ✅ Man-in-the-middle attacks are prevented
• ✅ Internal Docker traffic remains unaffected (for performance)
• ✅ Both direct and pooled connections are protected

Security notes:

• The self-signed CA certificate is sufficient for encryption. For additional verification, you can distribute ca.crt to clients and use sslmode=verify-ca or sslmode=verify-full.
• Certificates are valid for 10 years. Set a reminder to regenerate them before expiration.
• The private key (server.key) should never leave the server.

Next steps:

• Set up CrowdSec to detect and block brute-force attacks — see "Protect Self-Hosted Services with CrowdSec and Traefik"
• Configure centralized monitoring — see "Centralized Security Monitoring with Prometheus and Grafana"

License: MIT