#!/bin/sh
# Measurements for "FATAL: no pg_hba.conf entry for host ..." (SQLSTATE 28000).
# Run setup.sh first, with the same PREFIX and BASE. Needs psql (libpq) on the host.
# Every section prints the pg_hba.conf it installs and the raw client output.
#
#   PREFIX=pghba BASE=55800 ./run.sh 2>&1 | tee results.txt
set -u
PREFIX=${PREFIX:-pghba}
BASE=${BASE:-55800}
VERSIONS=${VERSIONS:-"12 13 14 16 17 18"}
export PGPASSWORD=test

port_of() { i=0; for v in $VERSIONS; do [ "$v" = "$1" ] && { echo $((BASE + i)); return; }; i=$((i + 1)); done; }
hba() { docker exec "$1" psql -U postgres -Atc 'show hba_file'; }
reload() { docker exec "$1" psql -U postgres -qAtc 'select pg_reload_conf()' >/dev/null; sleep 0.5; }
sethba() { # container, lines...  (always keeps a trusted local socket line first)
  c=$1; shift
  printf '%s\n' 'local all all trust' "$@" | docker exec -i "$c" sh -c "cat > $(hba "$c")"
  echo "   pg_hba.conf of $c:"; printf '     %s\n' 'local all all trust' "$@"
  reload "$c"
}
distro() { # what a distribution package ships for TCP: loopback only
  sethba "$1" \
    'host all all 127.0.0.1/32 scram-sha-256' \
    'host all all ::1/128 scram-sha-256' \
    'local replication all trust' \
    'host replication all 127.0.0.1/32 scram-sha-256' \
    'host replication all ::1/128 scram-sha-256'
}
setssl() { docker exec "$1" psql -U postgres -qAtc "alter system set ssl = $2" -c 'select pg_reload_conf()' >/dev/null; sleep 0.5; }
try() { # port, extra conninfo
  echo "-- host psql: host=127.0.0.1 port=$1 $2"
  psql "host=127.0.0.1 port=$1 $2" -Atc "select 'connected, ssl=' || ssl from pg_stat_ssl where pid = pg_backend_pid()" 2>&1
}
section() { echo; echo "=================== $*"; date -u '+%Y-%m-%d %H:%M:%S UTC'; }

section "0. versions"
psql --version
for c in $(for v in $VERSIONS; do echo "$PREFIX-$v"; done) "$PREFIX-default"; do
  echo "$c: $(docker exec "$c" psql -U postgres -Atc 'select version()') | client in container: $(docker exec "$c" psql --version)"
done
docker network inspect "$PREFIX-net" -f 'network {{range .IPAM.Config}}{{.Subnet}} gateway {{.Gateway}}{{end}}'

section "1. official image, untouched pg_hba.conf"
docker exec "$PREFIX-default" sh -c "grep -Ev '^(#|$)' $(hba "$PREFIX-default")"
try $((BASE + 6)) 'user=postgres dbname=postgres'

section "2. pg_hba.conf as a distribution ships it (loopback only), every version"
for v in $VERSIONS; do
  c="$PREFIX-$v"
  docker exec "$c" psql -U postgres -qAtc 'alter system set log_error_verbosity = verbose' >/dev/null
  distro "$c"
  try "$(port_of "$v")" 'user=postgres dbname=postgres'
  echo "-- from a second container ($PREFIX-16, its psql) over the docker network:"
  docker exec -e PGPASSWORD=test "$PREFIX-16" psql "host=$c user=postgres dbname=postgres" -Atc 'select 1' 2>&1
  echo "-- inside $c, TCP to 127.0.0.1:"
  docker exec -e PGPASSWORD=test "$c" psql "host=127.0.0.1 user=postgres dbname=postgres" -Atc "select 'connected from ' || host(inet_client_addr())" 2>&1
  echo "-- a role and database that do not exist:"
  try "$(port_of "$v")" 'user=nosuchrole dbname=nosuchdb'
  echo "-- server log (log_error_verbosity = verbose):"
  docker logs "$c" 2>&1 | grep -E 'FATAL|DETAIL|HINT' | tail -4
done

section "3. listen_addresses = 'localhost' (container $PREFIX-listen)"
docker exec "$PREFIX-listen" psql -U postgres -Atc 'show listen_addresses'
try $((BASE + 7)) 'user=postgres dbname=postgres'
echo "-- from a second container:"
docker exec -e PGPASSWORD=test "$PREFIX-16" psql "host=$PREFIX-listen user=postgres" -Atc 'select 1' 2>&1

section "4a. ssl = on (self-signed certificate), distro pg_hba.conf, sslmode prefer/disable/require"
for v in $VERSIONS; do
  c="$PREFIX-$v"
  docker exec -u postgres "$c" sh -c 'cd "$(dirname "$(psql -Atc "show hba_file")")" && openssl req -new -x509 -days 30 -nodes -subj /CN=pghba-test -keyout server.key -out server.crt 2>/dev/null && chmod 600 server.key'
  setssl "$c" on
  echo "$c ssl = $(docker exec "$c" psql -U postgres -Atc 'show ssl')"
  for m in prefer disable require; do try "$(port_of "$v")" "user=postgres dbname=postgres sslmode=$m"; done
done

section "4b. hostssl / hostnossl against every sslmode, PostgreSQL 17"
c="$PREFIX-17"; p=$(port_of 17)
for s in on off; do
  setssl "$c" $s; echo "## server ssl = $s"
  for t in hostssl hostnossl; do
    sethba "$c" "$t all all 0.0.0.0/0 scram-sha-256"
    for m in disable allow prefer require; do try "$p" "user=postgres dbname=postgres sslmode=$m"; done
  done
done
echo "-- hostssl while ssl = off: pg_hba_file_rules and the log at reload"
sethba "$c" 'hostssl all all 0.0.0.0/0 scram-sha-256'
docker exec "$c" psql -U postgres -c 'select line_number, type, address, auth_method, error from pg_hba_file_rules'
docker logs "$c" 2>&1 | grep -v LOCATION | tail -4
setssl "$c" on

section "5. order: the first matching line wins; lines for one database or user, PostgreSQL 17"
docker exec "$c" psql -U postgres -qc "create role app login password 'test'" -c 'create database appdb owner app' 2>&1
sethba "$c" 'host all all 172.16.0.0/12 reject' 'host all all 0.0.0.0/0 scram-sha-256'
try "$p" 'user=postgres dbname=postgres sslmode=disable'
try "$p" 'user=postgres dbname=postgres sslmode=prefer'
sethba "$c" 'host all all 0.0.0.0/0 scram-sha-256' 'host all all 172.16.0.0/12 reject'
try "$p" 'user=postgres dbname=postgres sslmode=disable'
sethba "$c" 'host appdb app 0.0.0.0/0 scram-sha-256'
try "$p" 'user=app dbname=appdb sslmode=disable'
try "$p" 'user=app dbname=postgres sslmode=disable'
try "$p" 'user=postgres dbname=appdb sslmode=disable'
try "$p" 'user=APP dbname=appdb sslmode=disable'
echo "-- no dbname given (libpq then uses the user name):"
try "$p" 'user=app sslmode=disable'
sethba "$c" 'host all +tenants 0.0.0.0/0 scram-sha-256'
try "$p" 'user=app dbname=appdb sslmode=disable'
docker exec "$c" psql -U postgres -qc 'create role tenants nologin' -c 'grant tenants to app'
echo "-- after: grant tenants to app"
try "$p" 'user=app dbname=appdb sslmode=disable'
sethba "$c" 'host all all samenet scram-sha-256'
try "$p" 'user=postgres dbname=postgres sslmode=disable'
docker exec -e PGPASSWORD=test "$PREFIX-16" psql "host=$c user=postgres sslmode=disable" -Atc "select 'from a second container: connected from ' || host(inet_client_addr())" 2>&1

section "6a. editing without reload, and a typo at reload, PostgreSQL 17"
distro "$c"
try "$p" 'user=postgres dbname=postgres sslmode=disable'
f=$(hba "$c")
echo 'host all all 172.16.0.0/12 scram-sha-256' | docker exec -i "$c" sh -c "cat >> $f"
echo "-- appended 'host all all 172.16.0.0/12 scram-sha-256', no reload yet:"
try "$p" 'user=postgres dbname=postgres sslmode=disable'
docker exec "$c" psql -U postgres -c 'select line_number, type, address, netmask, auth_method, error from pg_hba_file_rules'
reload "$c"; echo "-- after select pg_reload_conf():"
try "$p" 'user=postgres dbname=postgres sslmode=disable'
docker exec "$c" sed -i 's#172.16.0.0/12 scram-sha-256#172.16.0.0/12 scram-sha256#' "$f"
echo "-- changed the line to 'scram-sha256' (typo), reload:"
reload "$c"
try "$p" 'user=postgres dbname=postgres sslmode=disable'
docker exec "$c" psql -U postgres -c 'select line_number, type, address, auth_method, error from pg_hba_file_rules'
docker logs "$c" 2>&1 | grep -v LOCATION | tail -4
distro "$c"

section "6b. restart with a typo in pg_hba.conf (container $PREFIX-typo)"
t="$PREFIX-typo"; ft=$(hba "$t")
docker exec "$t" sh -c "echo 'host all all 172.16.0.0/12 scram-sha256' >> $ft"
docker restart "$t" >/dev/null; sleep 4
docker ps -a --filter "name=^$t\$" --format '{{.Names}}: {{.Status}}'
docker logs "$t" 2>&1 | tail -5
try $((BASE + 8)) 'user=postgres dbname=postgres'
echo "-- repair: remove the line from the stopped container, start again"
docker cp "$t:$ft" /tmp/pghba-typo.conf && sed -i '$d' /tmp/pghba-typo.conf && docker cp /tmp/pghba-typo.conf "$t:$ft" && rm -f /tmp/pghba-typo.conf
docker start "$t" >/dev/null; sleep 4
try $((BASE + 8)) 'user=postgres dbname=postgres'

section "7. IPv6: 'localhost' resolves to ::1, only 127.0.0.1/32 allowed, PostgreSQL 17"
sethba "$c" 'host all all 127.0.0.1/32 scram-sha-256'
docker exec "$c" grep localhost /etc/hosts
echo "-- inside the container: host=localhost"
docker exec -e PGPASSWORD=test "$c" psql "host=localhost user=postgres sslmode=disable" -Atc "select 'connected from ' || host(inet_client_addr())" 2>&1
echo "-- inside the container: host=127.0.0.1"
docker exec -e PGPASSWORD=test "$c" psql "host=127.0.0.1 user=postgres sslmode=disable" -Atc "select 'connected from ' || host(inet_client_addr())" 2>&1
distro "$c"

section "8. replication connections against the untouched official image ($PREFIX-default)"
d="$PREFIX-default"
echo "-- pg_basebackup from $c (17) over the docker network:"
docker exec -e PGPASSWORD=test "$c" sh -c "pg_basebackup -h $d -U postgres -D /tmp/bb -X none; rm -rf /tmp/bb" 2>&1
echo "-- host psql replication=true (physical):"
psql "host=127.0.0.1 port=$((BASE + 6)) user=postgres replication=true sslmode=disable" -c 'IDENTIFY_SYSTEM' 2>&1
echo "-- host psql replication=database (logical):"
psql "host=127.0.0.1 port=$((BASE + 6)) user=postgres dbname=postgres replication=database sslmode=disable" -c 'IDENTIFY_SYSTEM' 2>&1
fd=$(hba "$d")
docker exec "$d" sh -c "echo 'host replication all all scram-sha-256' >> $fd"; reload "$d"
echo "-- after appending 'host replication all all scram-sha-256' and a reload:"
docker exec -e PGPASSWORD=test "$c" sh -c "pg_basebackup -h $d -U postgres -D /tmp/bb -X none && ls /tmp/bb | head -3; rm -rf /tmp/bb" 2>&1

section "9. what the server log adds (log_connections = on), PostgreSQL 17 and 18"
for v in 17 18; do
  cc="$PREFIX-$v"
  docker exec "$cc" psql -U postgres -qAtc 'alter system set log_connections = on' -c 'select pg_reload_conf()' >/dev/null; sleep 0.5
  try "$(port_of "$v")" 'user=postgres dbname=postgres sslmode=disable'
  docker logs "$cc" 2>&1 | tail -4
done