# 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)

{% stepper %}
{% step %}

### Create a Certificate Authority (CA)

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

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

```bash
# 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`

{% hint style="info" %}
If you already have an organizational CA, you may reuse it.
{% endhint %}
{% endstep %}

{% step %}

### 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:

```bash
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:

```bash
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:

```bash
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.
{% endstep %}

{% step %}

### Install Certificates on the PostgreSQL Host

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

```bash
# 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:

```bash
# 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`):

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

Open `postgresql.conf`:

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

Add / confirm these lines:

```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`:

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

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

```conf
# 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.
{% endstep %}

{% step %}

### 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:

```yaml
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"
```

{% endstep %}
{% endstepper %}

## 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`:

```bash
#!/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:

```bash
chmod +x init/00-ssl.sh
```

Place your certificate files in `./certs/`:

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

Start the PostgreSQL container:

```bash
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.

```bash
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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.licensespring.com/floating-server/floating-server-v2/postgresql-tls.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.