wan
e93861d2c5
Password file lives in a 700 dir and userlist is 640 postgres, so non-root status false-DRifted right after a clean apply. Check them only as root; non-root status still verifies db/ini/tls/listener.
202 lines
9.5 KiB
Bash
202 lines
9.5 KiB
Bash
#!/usr/bin/env bash
|
|
# 015-remote-access - Open PgBouncer to the internet for the `ocrt_dev`
|
|
# app database.
|
|
#
|
|
# Architecture: clients -> pgbouncer :6432 (TLS + SCRAM) -> postgres
|
|
# (localhost, stock pg_hba unchanged). PostgreSQL stays closed to the net.
|
|
#
|
|
# role ocrt_dev LOGIN, random 128-bit password, NOT superuser -
|
|
# owning the database is sufficient for schema migrations
|
|
# database ocrt_dev OWNER ocrt_dev
|
|
# password /var/lib/sysmig/secrets/ocrt_dev.pass (root-only, no interactive
|
|
# prompt -> cloud-init safe; retrieve with sudo cat)
|
|
# TLS self-signed CA + server cert (SAN covers hostname + IPs) in
|
|
# /etc/pgbouncer/tls; client_tls_sslmode=require forces
|
|
# encryption; verify-full possible later by distributing ca.crt
|
|
# pools ocrt_dev (transaction - runtime)
|
|
# ocrt_dev_migrate (session - migration tools using session
|
|
# advisory locks, e.g. Prisma migrate)
|
|
#
|
|
# GCP firewall is outside sysmig's reach - run manually:
|
|
# gcloud compute firewall-rules create allow-pgbouncer-6432 \
|
|
# --allow tcp:6432 --source-ranges 0.0.0.0/0
|
|
#
|
|
# Connection strings (password: sudo cat /var/lib/sysmig/secrets/ocrt_dev.pass):
|
|
# runtime: postgresql://ocrt_dev:PW@35.212.194.2:6432/ocrt_dev?sslmode=require
|
|
# migrations: postgresql://ocrt_dev:PW@35.212.194.2:6432/ocrt_dev_migrate?sslmode=require
|
|
#
|
|
# Note: global statement_timeout=30s (010) applies here too; per-statement
|
|
# migrations are unaffected, ALTER ROLE ... SET statement_timeout overrides.
|
|
#
|
|
# up: TLS material, ini edits (listen/dbs/tls), role+db+password,
|
|
# userlist sync, restart pgbouncer, end-to-end auth smoke test
|
|
# down: drop db+role, clean userlist, restore localhost-only ini,
|
|
# remove certs + stored password
|
|
set -euo pipefail
|
|
|
|
ROLE=ocrt_dev
|
|
DB=ocrt_dev
|
|
INI=/etc/pgbouncer/pgbouncer.ini
|
|
USERLIST=/etc/pgbouncer/userlist.txt
|
|
TLS=/etc/pgbouncer/tls
|
|
SECRET_DIR=/var/lib/sysmig/secrets
|
|
SECRET=$SECRET_DIR/${ROLE}.pass
|
|
|
|
pg() { runuser -u postgres -- psql -d postgres -v ON_ERROR_STOP=1 -tAc "$1"; }
|
|
role_exists() { pg "SELECT 1 FROM pg_roles WHERE rolname='$ROLE'" | grep -q 1; }
|
|
db_exists() { pg "SELECT 1 FROM pg_database WHERE datname='$DB'" | grep -q 1; }
|
|
|
|
make_tls() {
|
|
[[ -f $TLS/server.crt && -f $TLS/server.key && -f $TLS/ca.crt ]] && return 0
|
|
install -d -m 755 "$TLS"
|
|
# CA; signing key stays root-only, pgbouncer never needs it at runtime
|
|
openssl req -x509 -newkey rsa:2048 -nodes \
|
|
-keyout "$TLS/ca.key" -out "$TLS/ca.crt" -days 3650 \
|
|
-subj '/CN=sysmig pgbouncer CA' \
|
|
-addext 'basicConstraints=critical,CA:TRUE' \
|
|
-addext 'keyUsage=critical,keyCertSign,cRLSign' 2>/dev/null
|
|
chmod 600 "$TLS/ca.key"; chmod 644 "$TLS/ca.crt"
|
|
# server cert; SAN covers hostname + loopback + external IP (if the GCP
|
|
# metadata server answers) so clients can later upgrade to verify-full
|
|
local san='DNS:ocrt-postgres,DNS:localhost,IP:127.0.0.1' extip
|
|
extip=$(curl -s -m 3 -H 'Metadata-Flavor: Google' \
|
|
'http://metadata.google.internal/computeMetadata/v1/instance/network-interfaces/0/access-configs/0/external-ip' || true)
|
|
[[ $extip =~ ^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$ ]] && san="$san,IP:$extip"
|
|
openssl req -newkey rsa:2048 -nodes \
|
|
-keyout "$TLS/server.key" -out "$TLS/server.csr" \
|
|
-subj '/CN=ocrt-postgres' 2>/dev/null
|
|
printf 'basicConstraints=CA:FALSE\nkeyUsage=digitalSignature,keyEncipherment\nextendedKeyUsage=serverAuth\nsubjectAltName=%s\n' \
|
|
"$san" > "$TLS/server.ext"
|
|
openssl x509 -req -in "$TLS/server.csr" -CA "$TLS/ca.crt" -CAkey "$TLS/ca.key" \
|
|
-CAcreateserial -out "$TLS/server.crt" -days 3650 -extfile "$TLS/server.ext" 2>/dev/null
|
|
rm -f "$TLS/server.csr" "$TLS/server.ext"
|
|
chown postgres:postgres "$TLS/server.key" "$TLS/server.crt"
|
|
chmod 600 "$TLS/server.key"; chmod 644 "$TLS/server.crt"
|
|
echo " TLS material generated (SAN: $san)"
|
|
}
|
|
|
|
patch_ini_up() {
|
|
sed -i 's/^listen_addr = .*/listen_addr = 0.0.0.0/' "$INI"
|
|
# [databases] block right after the iwanhae entry; ranges deleted on down
|
|
grep -q 'begin sysmig 015 databases' "$INI" || sed -i \
|
|
'/^iwanhae = host=/a\;; --- begin sysmig 015 databases ---\nocrt_dev = host=127.0.0.1 port=5432 dbname=ocrt_dev\nocrt_dev_migrate = host=127.0.0.1 port=5432 dbname=ocrt_dev pool_mode=session\n;; --- end sysmig 015 databases ---' "$INI"
|
|
# TLS block; [pgbouncer] is the last section of the 013-managed ini
|
|
grep -q 'begin sysmig 015 tls' "$INI" || cat >> "$INI" <<'EOF'
|
|
|
|
;; --- begin sysmig 015 tls ---
|
|
client_tls_sslmode = require
|
|
client_tls_protocols = secure
|
|
client_tls_cert_file = /etc/pgbouncer/tls/server.crt
|
|
client_tls_key_file = /etc/pgbouncer/tls/server.key
|
|
;; --- end sysmig 015 tls ---
|
|
EOF
|
|
}
|
|
|
|
sync_userlist() {
|
|
local verifier tmp
|
|
verifier=$(pg "SELECT rolpassword FROM pg_authid WHERE rolname='$ROLE'")
|
|
[[ $verifier == SCRAM-SHA-256* ]] || { echo "FATAL: $ROLE has no SCRAM verifier" >&2; exit 1; }
|
|
tmp=$(mktemp)
|
|
{ grep -v "^\"$ROLE\" " "$USERLIST" 2>/dev/null || true
|
|
printf '"%s" "%s"\n' "$ROLE" "$verifier"
|
|
} > "$tmp"
|
|
install -m 640 -o postgres -g postgres "$tmp" "$USERLIST"
|
|
rm -f "$tmp"
|
|
}
|
|
|
|
case "${1:-}" in
|
|
up)
|
|
make_tls
|
|
patch_ini_up
|
|
if role_exists; then
|
|
echo " role $ROLE exists - keeping password, re-syncing userlist"
|
|
else
|
|
pw=$(openssl rand -hex 16)
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -qc \
|
|
"CREATE ROLE $ROLE LOGIN PASSWORD '$pw'"
|
|
install -d -m 700 "$SECRET_DIR"
|
|
printf '%s\n' "$pw" > "$SECRET.tmp"
|
|
install -m 600 "$SECRET.tmp" "$SECRET"; rm -f "$SECRET.tmp"
|
|
echo " role $ROLE created (password: sudo cat $SECRET)"
|
|
unset pw
|
|
fi
|
|
if db_exists; then
|
|
echo " database $DB exists - skipping"
|
|
else
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -qc \
|
|
"CREATE DATABASE $DB OWNER $ROLE"
|
|
echo " database $DB created, owner $ROLE"
|
|
fi
|
|
sync_userlist
|
|
systemctl restart pgbouncer
|
|
pg_isready -h 127.0.0.1 -p 6432 -q
|
|
# end-to-end proof: TLS + userlist auth + SCRAM pass-through + db mapping
|
|
if PGPASSWORD="$(cat "$SECRET")" psql -h 127.0.0.1 -p 6432 -U "$ROLE" -d "$DB" \
|
|
-tAc 'SELECT 1' | grep -q 1; then
|
|
echo " end-to-end through pgbouncer OK (TLS + SCRAM pass-through)"
|
|
else
|
|
echo "FATAL: end-to-end login via 127.0.0.1:6432 failed" >&2
|
|
exit 1
|
|
fi
|
|
echo " pgbouncer now listens on 0.0.0.0:6432 - open the GCP firewall to finish"
|
|
;;
|
|
status)
|
|
if [[ $EUID -eq 0 ]]; then
|
|
q() { runuser -u postgres -- psql -d postgres -v ON_ERROR_STOP=1 -tAc "$1"; }
|
|
elif psql -d postgres -tAc 'SELECT 1' >/dev/null 2>&1; then
|
|
q() { psql -d postgres -v ON_ERROR_STOP=1 -tAc "$1"; }
|
|
else
|
|
echo "? needs root or peer DB access to verify"
|
|
exit 2
|
|
fi
|
|
[[ $(q "SELECT count(*) FROM pg_roles WHERE rolname='$ROLE'") -ge 1 ]] \
|
|
|| { echo "DRIFT role $ROLE missing"; exit 1; }
|
|
[[ $(q "SELECT pg_get_userbyid(datdba) FROM pg_database WHERE datname='$DB'") == "$ROLE" ]] \
|
|
|| { echo "DRIFT database $DB missing or not owned by $ROLE"; exit 1; }
|
|
[[ -f $TLS/server.crt ]] || { echo "DRIFT TLS cert missing"; exit 1; }
|
|
grep -q '^listen_addr = 0.0.0.0' "$INI" || { echo "DRIFT listen_addr not 0.0.0.0"; exit 1; }
|
|
grep -q "^$DB = host=" "$INI" || { echo "DRIFT $DB pool entry missing"; exit 1; }
|
|
grep -q '^client_tls_sslmode = require' "$INI" || { echo "DRIFT client TLS not enforced"; exit 1; }
|
|
# root-only checks: password file (700 dir) and userlist (640 postgres)
|
|
if [[ $EUID -eq 0 ]]; then
|
|
[[ -f $SECRET ]] || { echo "DRIFT password file $SECRET missing"; exit 1; }
|
|
ver=$(q "SELECT rolpassword FROM pg_authid WHERE rolname='$ROLE'")
|
|
grep -qF "\"$ver\"" "$USERLIST" || { echo "DRIFT userlist verifier out of sync"; exit 1; }
|
|
note="userlist synced, "
|
|
fi
|
|
systemctl is-active --quiet pgbouncer || { echo "DRIFT pgbouncer not active"; exit 1; }
|
|
ss -ltn 2>/dev/null | grep -q '0.0.0.0:6432' \
|
|
|| { echo "DRIFT not listening on 0.0.0.0:6432"; exit 1; }
|
|
echo "OK role+db $ROLE, TLS enforced, ${note:-}listening on 0.0.0.0:6432"
|
|
;;
|
|
down)
|
|
if db_exists; then
|
|
objects=$(runuser -u postgres -- psql -d "$DB" -tAc \
|
|
"SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
|
|
AND c.relkind IN ('r', 'p', 'v', 'm', 'S', 'f')" | tr -d ' ')
|
|
if [[ $objects -gt 0 ]]; then
|
|
echo "refusing to drop $DB - it contains user objects (data would be lost)" >&2
|
|
echo "drop manually if you really want: runuser -u postgres -- psql -c 'DROP DATABASE $DB WITH (FORCE)'" >&2
|
|
exit 1
|
|
fi
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -qc "DROP DATABASE $DB WITH (FORCE)"
|
|
echo " database $DB dropped (was empty)"
|
|
fi
|
|
if role_exists; then
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -qc "DROP ROLE $ROLE" \
|
|
|| { echo "cannot drop $ROLE - run DROP OWNED BY in each db first" >&2; exit 1; }
|
|
echo " role $ROLE dropped"
|
|
fi
|
|
tmp=$(mktemp)
|
|
grep -v "^\"$ROLE\" " "$USERLIST" 2>/dev/null > "$tmp" || true
|
|
install -m 640 -o postgres -g postgres "$tmp" "$USERLIST"; rm -f "$tmp"
|
|
sed -i '/;; --- begin sysmig 015 databases ---/,/;; --- end sysmig 015 databases ---/d' "$INI"
|
|
sed -i '/;; --- begin sysmig 015 tls ---/,/;; --- end sysmig 015 tls ---/d' "$INI"
|
|
sed -i 's/^listen_addr = .*/listen_addr = 127.0.0.1/' "$INI"
|
|
rm -rf "$TLS" "$SECRET"
|
|
systemctl restart pgbouncer
|
|
echo " pgbouncer back to localhost-only; userlist, certs and password removed"
|
|
;;
|
|
esac
|