PostgreSQL TLS

The Floating Server (FS) supports secure database connections over TLS. You can configure your PostgreSQL instance in two ways:

• sslmode=require: Encrypts the connection (no hostname verification).

• sslmode=verify-full: Encrypts and verifies the server’s identity (recommended for production).

Depending on your use case, enable TLS on PostgreSQL (install cert/key) and then set the Floating Server connection accordingly:

• Encrypt-only:

sslmode=require

• Full verification:

sslmode=verify-full
sslrootcert=/path/to/ca.pem

Setting up Verify-Full TLS for PostgreSQL (single or multiple machine)

1

Create a Certificate Authority (CA)

This CA will be used to sign PostgreSQL server certificates. Clients will trust this CA via sslrootcert.

mkdir -p ~/pg-tls && cd ~/pg-tls

# Root CA (keep the key private)
openssl genrsa -out ca.key 4096
openssl req -x509 -new -key ca.key -sha256 -days 3650 \
  -out ca.crt -subj "/CN=Org-Postgres-CA"

Artifacts:

• CA certificate (for clients): ~/pg-tls/ca.crt

• CA private key (do not share): ~/pg-tls/ca.key

If you already have an organizational CA, you may reuse it.

2

Issue a Server Certificate for Each PostgreSQL Host

Generate a server certificate for each PostgreSQL machine. The certificate must include the DNS name clients use to connect. If clients connect via IP, include the IP as a SAN as well.

Set your host values:

HOST="db1.example.com"  # the DNS name clients will use
IP1="1.0.1.1"           # if clients will connect by IP

Generate key + CSR config with SANs:

openssl genrsa -out server.key 4096
cat > server.cnf <<EOF
[ req ]
distinguished_name = dn
req_extensions = v3_req
prompt = no
[ dn ]
CN = ${HOST}
[ v3_req ]
extendedKeyUsage = serverAuth
subjectAltName = @alt
[ alt ]
DNS.1 = ${HOST}
$( [ -n "$IP1" ] && echo "IP.1 = ${IP1}" )
EOF

Make and sign the CSR:

openssl req -new -key server.key -out server.csr -config server.cnf
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -out server.crt -days 825 -sha256 -extfile server.cnf -extensions v3_req

Artifacts for this host:

• Server leaf cert: server.crt

• Server private key: server.key

Repeat for each Postgres machine. Do not reuse the same private key across machines.

3

Install Certificates on the PostgreSQL Host

Copy the leaf certificate and key into the Postgres data directory:

# Copy leaf + key into the Postgres data dir
cp server.crt /PATH/TO/DATADIR/server.crt
cp server.key /PATH/TO/DATADIR/server.key

Ensure the key is restricted:

# key must be restricted
chown postgres:postgres /PATH/TO/DATADIR/server.{crt,key}
chmod 600 /PATH/TO/DATADIR/server.key
chmod 644 /PATH/TO/DATADIR/server.crt

Enable TLS and listen on your network:

Find your Postgres data dir ($PGDATA):

sudo -u postgres psql -tAc "SHOW data_directory;"

Open postgresql.conf:

sudo -u postgres ${EDITOR:-nano} "$PGDATA/postgresql.conf"

Add / confirm these lines:

ssl = on
ssl_cert_file = 'server.crt'   # or an absolute path
ssl_key_file  = 'server.key'   # key must be chmod 600, owned by postgres
listen_addresses = '*'         # or a comma list like '10.0.1.15,127.0.0.1'

Allow TLS connections from your client networks (order matters):

Open pg_hba.conf:

sudo -u postgres ${EDITOR:-nano} "$PGDATA/pg_hba.conf"

Add these lines near the top (before broader rules) and adjust DB/user/CIDRs:

# Tighten the CIDRs to your environment
hostssl  fsdb  fsuser  10.0.0.0/16     scram-sha-256
hostssl  fsdb  fsuser  192.168.0.0/24  scram-sha-256

Restart Postgres (e.g., pg_ctl -D /PATH/TO/DATADIR restart) and ensure port 5432 is open.

4

Client (Floating Server) connection using verify-full

Clients must connect using the same DNS name that’s in the server cert SAN.

Place the CA cert on the app host (or inside the container), for example:

• /etc/ssl/certs/org-postgres-ca.crt

Config file example:

database:
  type: "postgres"
  host: db1.example.com
  port: 5432
  user: fsuser
  password: fspassword
  name: fsdb
  sslmode: "verify-full"   # options: disable, require, verify-full
  sslrootcert: "/etc/ssl/certs/org-postgres-ca.crt"

Run PostgreSQL with TLS in Docker

After you’ve created your certs (ca.crt, server.crt, server.key), mount them into the container and let an init script place them in Postgres’s data dir with the right ownership/permissions.

Init script (runs on first boot)

Create init/00-ssl.sh:

#!/bin/bash
set -e
mkdir -p "$PGDATA/certs"
cp /certs/server.crt "$PGDATA/certs/server.crt"
cp /certs/server.key "$PGDATA/certs/server.key"
chown postgres:postgres "$PGDATA/certs/server."{crt,key}
chmod 600 "$PGDATA/certs/server.key"
chmod 644 "$PGDATA/certs/server.crt"

# Allow TLS clients (adjust DB/user/CIDRs!)
{
  echo "hostssl  fsdb  fsuser  10.0.0.0/16     scram-sha-256"
  echo "hostssl  fsdb  fsuser  192.168.0.0/24  scram-sha-256"
} >> "$PGDATA/pg_hba.conf"

Make it executable:

chmod +x init/00-ssl.sh

Place your certificate files in ./certs/:

./certs/server.crt
./certs/server.key

Start the PostgreSQL container:

docker run -d \
  --name fs-postgres \
  --network fsnet \
  -e POSTGRES_USER=fsuser \
  -e POSTGRES_PASSWORD=fspassword \
  -e POSTGRES_DB=fsdb \
  -v pgdata:/var/lib/postgresql/data \
  -v "$(pwd)/certs:/certs:ro" \
  -v "$(pwd)/init:/docker-entrypoint-initdb.d:ro" \
  -p 5432:5432 \
  postgres:16 \
  -c ssl=on \
  -c ssl_cert_file=$PGDATA/certs/server.crt \
  -c ssl_key_file=$PGDATA/certs/server.key \
  -c listen_addresses='*'

Bash Script

The following script implements the steps described above. Create a file named fs-pg.sh and copy the script into it. Make sure to adjust paths and parameters as needed. The ca.crt will be saved under ./pg-tls/. This script only generates the certificates required for TLS setup.

mkdir -p ~/pg-tls && cd ~/pg-tls
openssl genrsa -out ca.key 4096
openssl req -x509 -new -key ca.key -sha256 -days 3650  -out ca.crt -subj "/CN=Org-Postgres-CA"
HOST="localhost"
IP1="1.0.1.1"
openssl genrsa -out server.key 4096
cat > server.cnf <<EOF
[ req ]
distinguished_name = dn
req_extensions = v3_req
prompt = no
[ dn ]
CN = ${HOST}
[ v3_req ]
extendedKeyUsage = serverAuth
subjectAltName = @alt
[ alt ]
DNS.1 = ${HOST}
EOF

# Append SANs conditionally
[ -n "${IP1:-}" ] && echo "IP.1 = ${IP1}" >> server.cnf
openssl req -new -key server.key -out server.csr -config server.cnf
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -out server.crt -days 825 -sha256 -extfile server.cnf -extensions v3_req

# --- Install certs into Postgres and enable TLS ---

# 1) Discover PGDATA (works for macOS/Homebrew and Linux)
PGDATA="$(psql -tAc 'SHOW data_directory;' 2>/dev/null || sudo -u postgres psql -tAc 'SHOW data_directory;' || psql -d postgres -tAc 'SHOW data_directory;')"
PGDATA="$(echo "$PGDATA" | tr -d '[:space:]')"
[ -d "$PGDATA" ] || { echo "Could not determine PGDATA"; exit 1; }

# 2) Copy leaf + key into PGDATA (or PGDATA/certs if you prefer)
sudo cp server.crt "$PGDATA/server.crt"
sudo cp server.key "$PGDATA/server.key"

# 3) Set owner/group to match the data dir owner (works both on mac and Linux)
if command -v stat >/dev/null && uname | grep -qi darwin; then
  OWNER="$(stat -f %Su "$PGDATA")"
  GROUP="$(stat -f %Sg "$PGDATA")"
else
  OWNER="$(stat -c %U "$PGDATA")"
  GROUP="$(stat -c %G "$PGDATA")"
fi
sudo chown "$OWNER:$GROUP" "$PGDATA/server.crt" "$PGDATA/server.key"

# 4) Lock down permissions (Postgres requires 600 on the key)
sudo chmod 600 "$PGDATA/server.key"
sudo chmod 644 "$PGDATA/server.crt"

# 5) Enable TLS + listen in postgresql.conf
CONF="$PGDATA/postgresql.conf"
sudo sed -i.bak -e "s/^#\?ssl *=.*/ssl = on/" \
                -e "s/^#\?ssl_cert_file *=.*/ssl_cert_file = 'server.crt'/" \
                -e "s/^#\?ssl_key_file *=.*/ssl_key_file  = 'server.key'/" \
                -e "s/^#\?listen_addresses *=.*/listen_addresses = '*'/" "$CONF" \
  || {
    # Fallback: append if keys not present
    sudo tee -a "$CONF" >/dev/null <<'EOS'
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'
listen_addresses = '*'
EOS
  }

# 6) Allow TLS clients from your networks (add hostssl lines if missing)
HBA="$PGDATA/pg_hba.conf"
add_hba() { grep -qF "$1" "$HBA" || echo "$1" | sudo tee -a "$HBA" >/dev/null; }
add_hba "hostssl  fsdb  fsuser  all     scram-sha-256"

# 7) Reload or restart Postgres
# Try a reload first:
psql -tAc "SELECT pg_reload_conf();" 2>/dev/null \
  || sudo -u postgres psql -tAc "SELECT pg_reload_conf();" 2>/dev/null \
  || {
    # If reload isn't available, do a restart 
    if command -v brew >/dev/null; then
      # macOS Homebrew service name may vary (postgresql@16, etc.)
      brew services restart postgresql || true
else
      sudo systemctl restart postgresql 2>/dev/null || \
      sudo service postgresql restart 2>/dev/null || \
      sudo -u "$OWNER" pg_ctl -D "$PGDATA" restart
    fi
  }

Notes

• The init script runs only when the pgdata volume is new/empty. If you’ve already used it, either remove the volume and re-run, or copy the files into $PGDATA/certs and fix ownership/permissions manually.

• Adjust DB/user/CIDRs to your environment.

• On the Floating Server side, use sslmode=verify-full and point to your CA: sslrootcert=/path/to/ca.crt.

• Paths and exact details may vary in your environment (OS, image, mount points). Adjust them as needed. The only requirements are that Postgres can read the certs at the paths you configure and that server.key is owned by postgres with 0600 permissions.