#!/bin/bash
# Connectivity probe from CGIS PG hosts to vdss internal DB hosts
# Read-only TCP connect test (PowerShell Test-NetConnection). No writes.
set -u
export PGPASSWORD57='postgres'
export PGPASSWORD8='Ctech2022!@#'
OUT="/root/ir-assessment/redteam/gitlab_vdss_com_vn/L6_connectivity_probe.txt"

probe() {
  local host="$1" user="$2" label="$3"
  echo "===FROM $label ($host)==="
  PGPASSWORD="$3pw" psql "host=$host port=5432 user=$user dbname=postgres connect_timeout=10" -v ON_ERROR_STOP=1 -At <<'SQL' 2>&1
DROP TABLE IF EXISTS pg_temp.c;
CREATE TEMP TABLE c(line text);
COPY c FROM PROGRAM $cmd$powershell -NoProfile -Command "foreach($t in @(@('172.31.2.15',5432),@('172.31.2.15',3306),@('10.215.102.41',5432))){ $h=$t[0]; $p=$t[1]; $r = Test-NetConnection -ComputerName $h -Port $p -WarningAction SilentlyContinue; Write-Output ($h + ':' + $p + ' => ' + $r.TcpTestSucceeded) }"$cmd$;
SELECT line FROM c;
SQL
}

{
  echo "===L6 CONNECTIVITY PROBE $(date -u) ==="
  echo "### from 14.225.11.57 (superuser_user) ###"
  PGPASSWORD='postgres' psql "host=14.225.11.57 port=5432 user=superuser_user dbname=postgres connect_timeout=10" -v ON_ERROR_STOP=1 -At <<'SQL' 2>&1
DROP TABLE IF EXISTS pg_temp.c;
CREATE TEMP TABLE c(line text);
COPY c FROM PROGRAM $cmd$powershell -NoProfile -Command "foreach($t in @(@('172.31.2.15',5432),@('172.31.2.15',3306),@('10.215.102.41',5432))){ $h=$t[0]; $p=$t[1]; $r = Test-NetConnection -ComputerName $h -Port $p -WarningAction SilentlyContinue; Write-Output ($h + ':' + $p + ' => ' + $r.TcpTestSucceeded) }"$cmd$;
SELECT line FROM c;
SQL
  echo "### from 14.225.11.8 (postgres) ###"
  PGPASSWORD='Ctech2022!@#' psql "host=14.225.11.8 port=5432 user=postgres dbname=postgres connect_timeout=10" -v ON_ERROR_STOP=1 -At <<'SQL' 2>&1
DROP TABLE IF EXISTS pg_temp.c;
CREATE TEMP TABLE c(line text);
COPY c FROM PROGRAM $cmd$powershell -NoProfile -Command "foreach($t in @(@('172.31.2.15',5432),@('172.31.2.15',3306),@('10.215.102.41',5432))){ $h=$t[0]; $p=$t[1]; $r = Test-NetConnection -ComputerName $h -Port $p -WarningAction SilentlyContinue; Write-Output ($h + ':' + $p + ' => ' + $r.TcpTestSucceeded) }"$cmd$;
SELECT line FROM c;
SQL
  echo "===END==="
} > "$OUT" 2>&1

echo "--- output file: $OUT ---"
cat "$OUT"