Export evidence for compliance audits

#!/bin/bash
# audit-evidence.sh
# Run on the Olympus host. Outputs files into ./audit/
set -e
mkdir -p audit
DATE=$(date +%Y%m%d)

# 1. All identities with admin role
psql -d olympus -c "
COPY (
  SELECT id, traits->>'email' AS email, traits->>'role' AS role,
         created_at, updated_at, state
  FROM identities
  WHERE traits->>'role' IN ('admin', 'iam:admin')
  ORDER BY created_at
) TO STDOUT WITH CSV HEADER
" > audit/admins-${DATE}.csv

# 2. MFA enrollment among admins
psql -d olympus -c "
COPY (
  SELECT i.id, i.traits->>'email' AS email,
         EXISTS (SELECT 1 FROM identity_credentials c
                 WHERE c.identity_id = i.id AND c.type IN ('totp', 'webauthn')) AS has_mfa
  FROM identities i
  WHERE i.traits->>'role' IN ('admin', 'iam:admin')
) TO STDOUT WITH CSV HEADER
" > audit/admin-mfa-${DATE}.csv

# 3. Deprovisioned users (90 days)
psql -d olympus -c "
COPY (
  SELECT id, traits->>'email' AS email, state, updated_at
  FROM identities
  WHERE state = 'inactive'
    AND updated_at > NOW() - INTERVAL '90 days'
) TO STDOUT WITH CSV HEADER
" > audit/deprovisioned-${DATE}.csv

# 4. Login events for last 30 days (sampled)
psql -d olympus -c "
COPY (
  SELECT identity_id, source_ip, user_agent, created_at,
         event_type, outcome
  FROM security_audit
  WHERE event_type IN ('login', 'mfa_challenge', 'recovery')
    AND created_at > NOW() - INTERVAL '30 days'
  ORDER BY created_at DESC
  LIMIT 10000
) TO STDOUT WITH CSV HEADER
" > audit/login-events-${DATE}.csv

# 5. Configurations
cp /home/deploy/olympus/config/kratos.yml audit/kratos-config-${DATE}.yml
cp /home/deploy/olympus/config/hydra.yml audit/hydra-config-${DATE}.yml
cp /home/deploy/olympus/config/Caddyfile audit/caddyfile-${DATE}

# 6. Backups
ls -la /var/backups/olympus/ > audit/backups-${DATE}.txt

# 7. Container versions
podman ps --format "table {{ '{{' }}.Names{{ '}}' }}\t{{ '{{' }}.Image{{ '}}' }}\t{{ '{{' }}.Status{{ '}}' }}" > audit/containers-${DATE}.txt

# Bundle
tar czf audit-${DATE}.tar.gz audit/
echo "Written audit-${DATE}.tar.gz"

audit-bundle/
├── CC6.1-logical-access/
│   ├── admins-list.csv
│   └── admin-mfa.csv
├── CC6.2-user-provisioning/
│   └── provisioning-changes.csv
├── CC6.3-user-deprovisioning/
│   └── deprovisioned-90d.csv
├── CC6.6-mfa/
│   ├── kratos-config.yml      (highlight `mfa.required: true`)
│   └── admin-mfa.csv
├── CC6.7-encryption/
│   ├── encryption-config.yml
│   └── adr-0006-encryption.md
├── CC7.1-monitoring/
│   └── audit-log-sample.csv
└── A1.2-backups/
    └── backups-listing.txt

-- Quarterly admin access review
COPY (
  SELECT
    id,
    traits->>'email' AS email,
    traits->>'role' AS role,
    created_at,
    (SELECT MAX(created_at) FROM security_audit
     WHERE identity_id = i.id AND event_type = 'login') AS last_login
  FROM identities i
  WHERE traits->>'role' IN ('admin', 'iam:admin')
  ORDER BY last_login DESC NULLS FIRST
) TO STDOUT WITH CSV HEADER

COPY (
  SELECT id, severity, summary, opened_at, resolved_at, postmortem_url
  FROM incidents
  WHERE opened_at > NOW() - INTERVAL '12 months'
  ORDER BY opened_at
) TO STDOUT WITH CSV HEADER

# In a sandbox VM (NOT prod):
psql -c "CREATE DATABASE olympus_restore_test"
pg_restore -d olympus_restore_test latest-backup.dump
psql -d olympus_restore_test -c "SELECT COUNT(*) FROM identities"
# Record date, count, screenshot, send to auditor.