#!/usr/bin/env bash
#
# JbTecWiz Support Centre -- generated fix script
#
# Fault : PostgreSQL: "too many clients", pg_hba refusals and a database that will not start
# Fix   : Write the right pg_hba rule
# Source: https://jbtecwiz.com/support/lnx-web-postgres
#
# Run as     : Root shell
# Expect     : 25 minutes
# Risk       : medium
# Reversible : yes
#
# WHEN THIS IS THE RIGHT FIX
#   "no pg_hba.conf entry for host" or an authentication failure.
#
# HOW TO UNDO IT
#   Remove the added line and reload.
#
# Walks the fix one step at a time and asks before each. Steps with no
# command are yours to do -- it prints those and waits. DRYRUN=1 prints
# without executing; UNATTENDED=1 does not ask.
#
# --------------------------------------------------------------------
# NO WARRANTY - USE AT YOUR OWN RISK
#
# This script is provided by JbTecWiz as-is and with no warranty of any
# kind, express or implied. You run it entirely at your own risk.
#
# JbTecWiz accepts no liability for any loss or damage arising from its
# use, including but not limited to data loss, downtime, or configuration
# changes that turn out to be wrong for your system.
#
# You are responsible for reading this script before running it, for
# satisfying yourself that it suits the machine in front of you, and for
# having a working backup first. Some steps cannot be undone.
# --------------------------------------------------------------------

set -uo pipefail
DRYRUN="${DRYRUN:-0}"
UNATTENDED="${UNATTENDED:-0}"
failed=0

if [ "$(id -u)" -ne 0 ]; then
  echo "  This fix is documented as needing root. Re-run with sudo." >&2
  exit 3
fi

rule() { printf "\n%s\n" "$(printf '-%.0s' $(seq 1 70))"; if [ $# -gt 0 ]; then echo "$1"; fi; }
prose() { echo "$1" | fold -s -w 74 | sed "s/^/  /"; }

# Returns 0 when the caller should run the command, 1 when it should not.
# A manual step always returns 1 -- there is nothing for the caller to run.
step() {  # step <n> <do> <why> <manual|cmd> [command lines...]
  local n="$1" dotext="$2" why="$3" mode="$4"; shift 4
  rule "  Step $n of 5"
  prose "$dotext"
  if [ -n "$why" ]; then echo; prose "$why"; fi
  if [ "$mode" = "manual" ]; then
    echo; echo "  -> Do this yourself, then press Enter to carry on."
    if [ "$UNATTENDED" = "0" ] && [ "$DRYRUN" = "0" ]; then read -r _; fi
    return 1
  fi
  echo; printf "    %s\n" "$@"; echo
  if [ "$DRYRUN" = "1" ]; then echo "  (dry run -- not executed)"; return 1; fi
  if [ "$UNATTENDED" = "0" ]; then
    read -r -p "  Run this step? [Y]es / [S]kip / [Q]uit " a
    case "$a" in
      [Qq]*) echo "  Stopped at your request."; exit 0 ;;
      [Ss]*) echo "  Skipped."; return 1 ;;
    esac
  fi
  return 0
}

rule
echo "  PostgreSQL: "too many clients", pg_hba refusals and a database that will not start"
echo "  Write the right pg_hba rule"
echo
echo "  Risk: medium   Reversible   25 minutes"
echo
prose 'No warranty. Use at your own risk - JbTecWiz accepts no liability. Read it before you run it, and have a backup.'
rule
echo
if [ "$UNATTENDED" = "0" ] && [ "$DRYRUN" = "0" ]; then
  read -r -p "  Ready? [y/N] " go
  case "$go" in [Yy]*) ;; *) echo "  Nothing was changed."; exit 0;; esac
fi

if step 1 'Find the file in use -- there may be more than one installation.' '' cmd 'sudo -u postgres psql -c '\''SHOW hba_file'\''' 'sudo -u postgres psql -c '\''SHOW config_file'\'''; then
  sudo -u postgres psql -c 'SHOW hba_file'
  sudo -u postgres psql -c 'SHOW config_file'
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 1 failed. The rest of the fix may depend on it." >&2
  fi
fi

if step 2 'Read the current rules. They are evaluated top to bottom and the first match wins, so a broad reject above your rule silences it.' 'First match wins is the part that catches people. Adding a correct rule at the bottom of the file does nothing if a earlier line already matched that host and method.' cmd 'sudo grep -vE '\''^\s*#|^\s*$'\'' $(sudo -u postgres psql -Atc '\''SHOW hba_file'\'')'; then
  sudo grep -vE '^\s*#|^\s*$' $(sudo -u postgres psql -Atc 'SHOW hba_file')
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 2 failed. The rest of the fix may depend on it." >&2
  fi
fi

if step 3 'Add a specific rule with the narrowest address range that works.' '' cmd 'echo '\''host    appdb    appuser    10.0.0.0/24    scram-sha-256'\'' | sudo tee -a /etc/postgresql/16/main/pg_hba.conf'; then
  echo 'host    appdb    appuser    10.0.0.0/24    scram-sha-256' | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 3 failed. The rest of the fix may depend on it." >&2
  fi
fi

if step 4 'Reload rather than restart -- pg_hba is re-read without dropping connections.' '' cmd 'sudo -u postgres psql -c '\''SELECT pg_reload_conf()'\'''; then
  sudo -u postgres psql -c 'SELECT pg_reload_conf()'
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 4 failed. The rest of the fix may depend on it." >&2
  fi
fi

if step 5 'Check the server is listening on the address at all -- listen_addresses defaults to localhost.' '' cmd 'sudo -u postgres psql -c '\''SHOW listen_addresses'\''' 'sudo ss -tlnp | grep 5432'; then
  sudo -u postgres psql -c 'SHOW listen_addresses'
  sudo ss -tlnp | grep 5432
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 5 failed. The rest of the fix may depend on it." >&2
  fi
fi

rule "  Confirm it worked"
prose 'The application connects and pg_hba_file_rules shows the rule with no error.'
if [ "$DRYRUN" = "0" ]; then
  sudo -u postgres psql -c 'SELECT line_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules'
fi

rule
if [ "$failed" -gt 0 ]; then
  echo "  Finished with $failed failed step(s)."
  echo "  Read the full write-up at https://jbtecwiz.com/support/lnx-web-postgres"
else
  echo "  Finished."
fi
echo
prose 'To undo: Remove the added line and reload.'
rule