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 ncrypts and verifies the server’s identity (recommended for production) depending on your use case, configure postgresql with tls((enable tls, install cert/key) and then set the floating server connection accordingly encrypt only full verification 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 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 generate key + csr config with sans make it executable 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 enable tls + listen on your network find your postgres data dir ( $pgdata ) open postgresql conf and add/confirm these lines 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 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) example path /etc/ssl/certs/org postgres ca crt config file 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 1\) 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 2\) place certificates put your files in /certs/ 3\) start 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