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 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 it executable 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 leaf + key into the postgres data dir cp server crt /path/to/datadir/server crt cp server key /path/to/datadir/server key \# 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 + listen on your network find your postgres data dir ( $pgdata ) sudo u postgres psql tac "show data directory;" open postgresql conf and add/confirm these lines sudo u postgres ${editor nano} "$pgdata/postgresql conf" 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) 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/ /certs/server crt /certs/server key 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