#!/usr/bin/env bash
#
# JbTecWiz Support Centre -- generated fix script
#
# Fault : MySQL: "Too many connections" or "Access denied for user"
# Fix   : Resolve the authentication mismatch
# Source: https://jbtecwiz.com/support/lnx-web-mysql-connections
#
# Run as     : Root shell
# Expect     : 25 minutes
# Risk       : medium
# Reversible : yes
#
# WHEN THIS IS THE RIGHT FIX
#   Error 1045, access denied.
#
# HOW TO UNDO IT
#   DROP USER removes an account created in error; note the previous
#   grants before changing them.
#
# 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 "  MySQL: "Too many connections" or "Access denied for user""
echo "  Resolve the authentication mismatch"
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 'List the accounts and the hosts they are allowed from. A user defined for '\''localhost'\'' cannot connect from '\''127.0.0.1'\'' -- those are different entries.' 'localhost means the Unix socket; 127.0.0.1 means TCP to loopback. An application configured with an IP address will be refused by a grant written for localhost, and the error gives no hint of this.' cmd 'sudo mysql -e "SELECT user,host,plugin FROM mysql.user ORDER BY user"'; then
  sudo mysql -e "SELECT user,host,plugin FROM mysql.user ORDER BY user"
  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 'Check the authentication plugin. An application with an older client library cannot use caching_sha2_password.' '' cmd 'sudo mysql -e "SELECT user,host,plugin FROM mysql.user WHERE user='\''appuser'\''"'; then
  sudo mysql -e "SELECT user,host,plugin FROM mysql.user WHERE user='appuser'"
  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 'Create or correct the grant for the right host.' '' cmd 'sudo mysql -e "CREATE USER IF NOT EXISTS '\''appuser'\''@'\''127.0.0.1'\'' IDENTIFIED BY '\''password'\'';' 'GRANT SELECT,INSERT,UPDATE,DELETE ON appdb.* TO '\''appuser'\''@'\''127.0.0.1'\'';' 'FLUSH PRIVILEGES;"'; then
  sudo mysql -e "CREATE USER IF NOT EXISTS 'appuser'@'127.0.0.1' IDENTIFIED BY 'password';
  GRANT SELECT,INSERT,UPDATE,DELETE ON appdb.* TO 'appuser'@'127.0.0.1';
  FLUSH PRIVILEGES;"
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 3 failed. The rest of the fix may depend on it." >&2
  fi
fi

step 4 'Grant only what the application needs. An application account with ALL PRIVILEGES on everything turns an SQL injection into a full database compromise.' '' manual || true

if step 5 'Test the credential exactly as the application uses it.' '' cmd 'mysql -h 127.0.0.1 -u appuser -p -e '\''SELECT 1'\'''; then
  mysql -h 127.0.0.1 -u appuser -p -e 'SELECT 1'
  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 the grant list matches what it needs.'
if [ "$DRYRUN" = "0" ]; then
  sudo mysql -e "SHOW GRANTS FOR 'appuser'@'127.0.0.1'"
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-mysql-connections"
else
  echo "  Finished."
fi
echo
prose 'To undo: DROP USER removes an account created in error; note the previous grants before changing them.'
rule