# Supabase Self-Hosting Runbook Audit

You are a Senior DevOps Security Auditor. Your task is to verify whether the
Supabase self-hosting setup has been correctly implemented according to the runbook (Article 1).

Execute the following checks in order. Use the available tools
(Read, Grep, Glob, and the permitted bash commands).

For each check: Report PASS, WARNING, or CRITICAL with a brief justification.
At the end, produce a summary with recommended actions.

---

## A1: Infrastructure Separation

Verify that production and audit run on separate servers.

```bash
# Hostname des aktuellen Servers
hostname

# Gibt es SSH-Zugang zu einem separaten Audit-Server?
grep -r "audit" ~/.ssh/config 2>/dev/null || echo "Kein SSH Config für audit-runner"

# Oder: Wird im Infra-Repo ein zweiter Server referenziert?
grep -ri "audit-runner\|10.0.1.11" . --include="*.yml" --include="*.sh" --include="*.md" 2>/dev/null | head -10
```

Expectation: Two separate hosts (supabase-prod + audit-runner).
Risk if not met: A compromised server can manipulate its own audit results.

---

## A2: Private Network

Verify that a private network is configured and PostgreSQL only listens on internal interfaces.

```bash
# Internes Interface vorhanden?
ip addr show | grep -E "10\.0\.[0-9]+\.[0-9]+"

# Auf welchem Interface lauscht PostgreSQL?
ss -tlnp | grep 5432

# Oder in der docker-compose.yml prüfen:
grep -A2 "5432" docker-compose.yml 2>/dev/null || grep -A2 "5432" /opt/supabase/docker-compose.yml 2>/dev/null
```

Expectation: PostgreSQL listens on 10.0.1.10:5432 (internal interface), NOT on 0.0.0.0:5432.
Risk if not met: Database directly reachable from the internet in case of a firewall misconfiguration.

---

## A3: Reverse Proxy and TLS

Verify that a reverse proxy with TLS is in front of the Supabase stack.

```bash
# Caddy oder Nginx vorhanden?
which caddy 2>/dev/null || which nginx 2>/dev/null || echo "Kein Reverse Proxy gefunden"

# Caddy Config vorhanden?
find / -name "Caddyfile" -type f 2>/dev/null | head -5

# Oder Nginx Config?
find /etc/nginx -name "*.conf" -type f 2>/dev/null | head -5

# TLS Zertifikat vorhanden und gültig?
echo | openssl s_client -connect localhost:443 2>/dev/null | openssl x509 -noout -enddate 2>/dev/null

# Security Headers konfiguriert? Suche in Proxy-Config nach:
grep -ri "X-Frame-Options\|Strict-Transport-Security\|Content-Security-Policy" \
  /etc/caddy/ /etc/nginx/ 2>/dev/null | head -10
```

Expectation:
- Reverse proxy (Caddy or Nginx) installed and configured
- TLS certificate valid (at least 14 days remaining)
- Security headers: Strict-Transport-Security, X-Frame-Options, X-Content-Type-Options, Content-Security-Policy

Risk if not met: Auth tokens transmitted in plaintext. Clickjacking and XSS attacks possible.

---

## A4: Firewall (Two Layers)

Verify that both a cloud firewall and a host firewall are active.

```bash
# Host Firewall (iptables) aktiv?
iptables -L -n 2>/dev/null | head -20

# Default Policy ist DROP?
iptables -L INPUT -n 2>/dev/null | head -1

# Welche Ports sind explizit erlaubt?
iptables -L INPUT -n 2>/dev/null | grep "ACCEPT"

# Firewall-Baseline vorhanden?
ls -la /root/firewall-baseline.txt 2>/dev/null || ls -la /opt/baselines/firewall-baseline.txt 2>/dev/null

# Drift gegen Baseline?
if [ -f /root/firewall-baseline.txt ]; then
  iptables-save | diff /root/firewall-baseline.txt - 2>/dev/null
elif [ -f /opt/baselines/firewall-baseline.txt ]; then
  iptables-save | diff /opt/baselines/firewall-baseline.txt - 2>/dev/null
fi
```

Expectation:
- iptables default policy: DROP
- Only ports 443 (HTTPS) and 22 (from admin IP) allowed
- Internal network (10.0.1.0/24) allowed
- Baseline file present, no drift

Risk if not met: An `iptables -F` opens all ports if no cloud firewall exists.

---

## A5: SSH Access

Verify that SSH is properly hardened.

```bash
# SSH Konfiguration prüfen
sshd -T 2>/dev/null | grep -E "passwordauthentication|permitrootlogin|pubkeyauthentication|allowusers"

# Alternativ direkt die Config lesen
grep -E "^PasswordAuthentication|^PermitRootLogin|^PubkeyAuthentication|^AllowUsers|^MaxAuthTries" \
  /etc/ssh/sshd_config 2>/dev/null
```

Expectation:
- PasswordAuthentication no
- PermitRootLogin no
- PubkeyAuthentication yes
- AllowUsers contains only the deploy user

Risk if not met: Brute-force attacks on SSH passwords; with root login enabled, immediate full control upon compromise.

---

## B1: Versioned Deployment

Verify that the Supabase setup is version-controlled in Git and no manual changes exist.

```bash
# Git Repository vorhanden?
cd /opt/supabase 2>/dev/null && git status --porcelain

# Oder wo liegt das Infra-Repo?
find /opt -name "docker-compose.yml" -path "*/supabase/*" 2>/dev/null | head -5

# Uncommitted Changes?
cd /opt/supabase 2>/dev/null && git diff --stat HEAD 2>/dev/null

# Differenz zum Remote?
cd /opt/supabase 2>/dev/null && git fetch origin 2>/dev/null && git diff HEAD origin/main --stat 2>/dev/null
```

Expectation:
- Git repository at /opt/supabase (or similar)
- No uncommitted changes (git status --porcelain is empty)
- No difference to remote (server is up to date)

Risk if not met: Manual changes are lost on git pull. Not reproducible after server loss.

---

## B2: Docker Compose Security-Relevant Configuration

Verify the docker-compose.yml for critical security settings.

```bash
# docker-compose.yml finden und lesen
COMPOSE=$(find /opt -name "docker-compose.yml" -path "*/supabase/*" 2>/dev/null | head -1)
if [ -n "$COMPOSE" ]; then
  # Images gepinnt (kein :latest)?
  echo "=== Images mit :latest ==="
  grep "image:" "$COMPOSE" | grep "latest" || echo "Keine :latest gefunden (gut)"

  # Postgres Port-Binding
  echo "=== Postgres Port ==="
  grep -A3 "postgres:" "$COMPOSE" | grep "ports" -A1

  # Kong Port-Binding
  echo "=== Kong Port ==="
  grep -A5 "kong:" "$COMPOSE" | grep "ports" -A1

  # GoTrue JWT Expiry
  echo "=== JWT Expiry ==="
  grep "GOTRUE_JWT_EXP" "$COMPOSE" || grep "JWT_EXP" "$COMPOSE" || echo "Nicht in compose (prüfe .env)"

  # GoTrue Autoconfirm
  echo "=== Autoconfirm ==="
  grep "MAILER_AUTOCONFIRM" "$COMPOSE" || echo "Nicht in compose (prüfe .env)"

  # Refresh Token Rotation
  echo "=== Refresh Token Rotation ==="
  grep "REFRESH_TOKEN_ROTATION" "$COMPOSE" || echo "Nicht in compose (prüfe .env)"
fi
```

Also verify the .env file (without logging the actual values):

```bash
ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)
if [ -n "$ENV_FILE" ]; then
  echo "=== JWT Expiry in .env ==="
  grep "JWT_EXP" "$ENV_FILE" | sed 's/=.*/=***/'
  EXPIRY=$(grep "GOTRUE_JWT_EXP" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
  if [ -n "$EXPIRY" ] && [ "$EXPIRY" -gt 3600 ] 2>/dev/null; then
    echo "KRITISCH: JWT_EXP ist $EXPIRY (über 3600)"
  fi
fi
```

Expectation:
- All images pinned to specific versions (no :latest)
- Postgres port: 10.0.1.10:5432 (internal) or 127.0.0.1:5432
- Kong port: 127.0.0.1:8000 (localhost)
- GOTRUE_JWT_EXP: maximum 3600
- GOTRUE_MAILER_AUTOCONFIRM: false
- REFRESH_TOKEN_ROTATION: true

Risk if not met: Unpinned images can introduce breaking changes or vulnerabilities.
JWT with 24h validity = a stolen token is usable for an entire day.

---

## B3: Secrets Management

Verify that secrets are managed correctly.

```bash
# .env nicht im Git?
cd /opt/supabase 2>/dev/null && git ls-files .env 2>/dev/null
# Muss leer sein

# .env in .gitignore?
cd /opt/supabase 2>/dev/null && grep "^\.env$" .gitignore 2>/dev/null

# Dateirechte der .env
ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)
if [ -n "$ENV_FILE" ]; then
  stat -c "%a %U" "$ENV_FILE"
fi

# Secrets-Länge prüfen (ohne Werte zu zeigen)
if [ -n "$ENV_FILE" ]; then
  echo "=== Secrets kürzer als 16 Zeichen ==="
  awk -F= '{
    if (length($2) > 0 && length($2) < 16 && $1 !~ /PORT|HOST|NAME|SENDER|USER|ENABLED|AUTOCONFIRM|DISABLE|HEADER|URL|ROLE|INTERVAL/)
      print "ZU KURZ: " $1 " (" length($2) " Zeichen)"
  }' "$ENV_FILE"
fi

# Default-Passwörter?
if [ -n "$ENV_FILE" ]; then
  echo "=== Default-Passwörter ==="
  grep -iE "password|secret|key" "$ENV_FILE" | \
    grep -iE "change.me|default|example|your.*here|super-secret|please-change" || \
    echo "Keine Default-Passwörter gefunden (gut)"
fi

# .env.example vorhanden?
ls /opt/supabase/.env.example 2>/dev/null || echo ".env.example fehlt"
```

Expectation:
- .env NOT tracked in Git (git ls-files empty)
- .env listed in .gitignore
- File permissions: 600, owner: deploy
- All secrets at least 16 characters
- No default passwords
- .env.example as template in the repo

Risk if not met: Leaked secrets are the most common security issue with self-hosting.

---

## B4: Database RLS Policies

Verify that Row Level Security is enabled on all public tables.

```bash
# Tabellen ohne RLS
docker compose exec -T postgres psql -U postgres -c \
  "SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public' AND rowsecurity = false;" \
  2>/dev/null

# Tabellen mit RLS aber ohne Policies (gesperrt, aber möglicherweise versehentlich)
docker compose exec -T postgres psql -U postgres -c \
  "SELECT t.tablename FROM pg_tables t LEFT JOIN pg_policies p ON t.tablename = p.tablename WHERE t.schemaname = 'public' AND t.rowsecurity = true AND p.policyname IS NULL;" \
  2>/dev/null

# Zu offene Policies (qual = 'true' = jeder hat Zugriff)
docker compose exec -T postgres psql -U postgres -c \
  "SELECT tablename, policyname, cmd, qual FROM pg_policies WHERE schemaname = 'public' AND qual = 'true';" \
  2>/dev/null
```

Expectation:
- No public tables without RLS (or consciously documented exceptions)
- No tables with RLS enabled but no policies (unless intentionally locked)
- No policies with qual = 'true' (= unrestricted access)

Risk if not met: Tables without RLS are readable by anyone via the anon key. An open policy on the users table exposes all user data.

---

## B5: Secrets Fully Generated and Secure

Verify that all required secrets are set and contain no default values.

```bash
ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)
if [ -n "$ENV_FILE" ]; then
  echo "=== Erforderliche Secrets ==="
  for var in JWT_SECRET POSTGRES_PASSWORD ANON_KEY SERVICE_ROLE_KEY \
    DASHBOARD_PASSWORD LOGFLARE_PUBLIC_ACCESS_TOKEN LOGFLARE_PRIVATE_ACCESS_TOKEN \
    SECRET_KEY_BASE VAULT_ENC_KEY PG_META_CRYPTO_KEY; do
    VAL=$(grep "^${var}=" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
    if [ -z "$VAL" ]; then
      echo "KRITISCH: $var ist nicht gesetzt"
    elif echo "$VAL" | grep -qiE "your-|change|example|super-secret|default"; then
      echo "KRITISCH: $var hat Default-Wert"
    else
      LEN=${#VAL}
      echo "OK: $var ($LEN Zeichen)"
    fi
  done

  # JWT_SECRET mindestens 32 Zeichen?
  JWT_LEN=$(grep "^JWT_SECRET=" "$ENV_FILE" | cut -d= -f2 | wc -c)
  [ "$JWT_LEN" -lt 32 ] && echo "KRITISCH: JWT_SECRET zu kurz ($JWT_LEN Zeichen, min. 32)"

  # SECRET_KEY_BASE mindestens 64 Zeichen?
  SKB_LEN=$(grep "^SECRET_KEY_BASE=" "$ENV_FILE" | cut -d= -f2 | wc -c)
  [ "$SKB_LEN" -lt 64 ] && echo "KRITISCH: SECRET_KEY_BASE zu kurz ($SKB_LEN Zeichen, min. 64)"

  # Doppelte Werte (gleicher Wert für verschiedene Secrets)?
  echo "=== Doppelte Secret-Werte ==="
  grep -E "SECRET|PASSWORD|KEY" "$ENV_FILE" | cut -d= -f2 | sort | uniq -d | \
    while read dup; do
      [ -n "$dup" ] && echo "WARNUNG: Mehrere Secrets haben den gleichen Wert"
    done
fi
```

Expectation:
- All secrets set, no default values
- JWT_SECRET at least 32 characters
- SECRET_KEY_BASE at least 64 characters
- No identical values across different secrets

Risk if not met: The default JWT_SECRET is publicly known. An attacker can generate valid tokens.

---

## B6: Kong API Gateway Configuration

Verify that Kong is correctly configured and only listens on localhost.

```bash
# Kong Port-Binding
echo "=== Kong Ports ==="
ss -tlnp | grep -E "8000|8443"
# Erwartung: 127.0.0.1:8000 und 127.0.0.1:8443

# Kong Config vorhanden?
echo "=== Kong Config ==="
ls -la /opt/supabase/volumes/api/kong.yml 2>/dev/null || echo "kong.yml nicht gefunden"

# JWT Validation aktiv auf API-Routes?
echo "=== JWT Plugins in kong.yml ==="
grep -A3 "key-auth\|jwt" /opt/supabase/volumes/api/kong.yml 2>/dev/null | head -20

# CORS Plugin aktiv?
echo "=== CORS Plugin ==="
grep -A3 "cors" /opt/supabase/volumes/api/kong.yml 2>/dev/null | head -10

# Dashboard Basic Auth: Passwort-Stärke
echo "=== Dashboard-Passwort ==="
DASH_PW=$(grep "^DASHBOARD_PASSWORD=" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
if [ -n "$DASH_PW" ]; then
  DASH_LEN=${#DASH_PW}
  [ "$DASH_LEN" -lt 16 ] && echo "WARNUNG: Dashboard-Passwort zu kurz ($DASH_LEN Zeichen)"
  [ "$DASH_LEN" -ge 16 ] && echo "OK: Dashboard-Passwort ($DASH_LEN Zeichen)"
else
  echo "KRITISCH: DASHBOARD_PASSWORD nicht gesetzt"
fi
```

Expectation:
- Kong listens on 127.0.0.1:8000 (NOT 0.0.0.0)
- kong.yml present with JWT validation on API routes
- Dashboard password at least 16 characters

Risk if not met: Kong on 0.0.0.0 = API directly reachable without TLS. Weak dashboard password = Studio access for attackers.

---

## B7: GoTrue (Auth) Configuration

Verify that authentication is securely configured.

```bash
ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)
COMPOSE=$(find /opt -name "docker-compose.yml" -path "*/supabase/*" 2>/dev/null | head -1)

# GoTrue Health
echo "=== GoTrue Health ==="
docker compose exec -T auth wget --no-verbose --tries=1 --spider http://localhost:9999/health 2>&1

# JWT Expiry
echo "=== JWT Expiry ==="
EXPIRY=$(grep -E "^JWT_EXPIRY=|^JWT_EXP=" "$ENV_FILE" 2>/dev/null | head -1 | cut -d= -f2)
if [ -n "$EXPIRY" ] && [ "$EXPIRY" -gt 3600 ] 2>/dev/null; then
  echo "KRITISCH: JWT Expiry ist $EXPIRY Sekunden (max. 3600)"
else
  echo "OK: JWT Expiry ist ${EXPIRY:-3600} Sekunden"
fi

# Autoconfirm
echo "=== E-Mail Autoconfirm ==="
AUTOCONFIRM=$(grep -iE "AUTOCONFIRM|ENABLE_EMAIL_AUTOCONFIRM" "$ENV_FILE" 2>/dev/null | head -1 | cut -d= -f2)
if [ "$AUTOCONFIRM" = "true" ]; then
  echo "KRITISCH: E-Mail Autoconfirm ist aktiviert"
else
  echo "OK: E-Mail Autoconfirm deaktiviert"
fi

# SMTP konfiguriert?
echo "=== SMTP Konfiguration ==="
for var in SMTP_HOST SMTP_PORT SMTP_USER SMTP_PASS; do
  VAL=$(grep "^${var}=" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
  if [ -z "$VAL" ]; then
    echo "WARNUNG: $var ist leer (Magic Links und E-Mail-Bestätigung funktionieren nicht)"
  else
    echo "OK: $var ist gesetzt"
  fi
done

# Refresh Token Rotation
echo "=== Refresh Token Rotation ==="
grep -i "REFRESH_TOKEN_ROTATION" "$ENV_FILE" "$COMPOSE" 2>/dev/null || \
  echo "WARNUNG: Refresh Token Rotation nicht explizit konfiguriert"

# Signup offen oder geschlossen?
echo "=== Signup Status ==="
SIGNUP=$(grep "DISABLE_SIGNUP" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
echo "DISABLE_SIGNUP=${SIGNUP:-false} (false = offen, true = geschlossen)"

# Site URL und API External URL
echo "=== URLs ==="
grep -E "^SITE_URL=|^API_EXTERNAL_URL=" "$ENV_FILE" 2>/dev/null
```

Expectation:
- JWT expiry maximum 3600 seconds
- Autoconfirm disabled (false)
- SMTP fully configured (host, port, user, pass)
- Refresh token rotation enabled
- SITE_URL and API_EXTERNAL_URL correctly set (HTTPS)

Risk if not met: Autoconfirm = true without SMTP = anyone can create accounts without email verification. JWT with 24h validity = a stolen token is usable for an entire day.

---

## B8: PostgREST Configuration

Verify that PostgREST is securely configured.

```bash
COMPOSE=$(find /opt -name "docker-compose.yml" -path "*/supabase/*" 2>/dev/null | head -1)
ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)

# PostgREST nutzt authenticator-Rolle (nicht postgres)?
echo "=== PostgREST DB User ==="
grep "PGRST_DB_URI" "$COMPOSE" "$ENV_FILE" 2>/dev/null | head -1
# Muss "authenticator" enthalten, NICHT "postgres"

if grep "PGRST_DB_URI" "$COMPOSE" "$ENV_FILE" 2>/dev/null | grep -q "postgres://postgres:"; then
  echo "KRITISCH: PostgREST nutzt den postgres Superuser statt authenticator"
fi

# Schemas eingeschränkt?
echo "=== PostgREST Schemas ==="
SCHEMAS=$(grep "PGRST_DB_SCHEMAS" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
if [ -z "$SCHEMAS" ]; then
  echo "WARNUNG: PGRST_DB_SCHEMAS ist leer (alle Schemas exponiert)"
else
  echo "OK: Schemas eingeschränkt auf: $SCHEMAS"
fi
```

Expectation:
- PGRST_DB_URI uses the authenticator role (NOT the postgres superuser)
- PGRST_DB_SCHEMAS explicitly set (public,storage,graphql_public)

Risk if not met: PostgREST with the postgres superuser = RLS is ineffective, every request has full DB access. Empty schemas = internal Supabase schemas (auth, _realtime) exposed via API.

---

## B9: Realtime Configuration

Verify that Realtime is securely configured.

```bash
COMPOSE=$(find /opt -name "docker-compose.yml" -path "*/supabase/*" 2>/dev/null | head -1)
ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)

# DB_ENC_KEY nicht auf Default?
echo "=== Realtime DB_ENC_KEY ==="
ENC_KEY=$(grep "DB_ENC_KEY" "$COMPOSE" 2>/dev/null | grep -v "^#" | head -1)
if echo "$ENC_KEY" | grep -q "supabaserealtime"; then
  echo "KRITISCH: DB_ENC_KEY ist auf dem Default-Wert 'supabaserealtime'"
else
  echo "OK: DB_ENC_KEY ist geändert"
fi

# SECRET_KEY_BASE Länge
echo "=== SECRET_KEY_BASE ==="
SKB=$(grep "^SECRET_KEY_BASE=" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
if [ -n "$SKB" ]; then
  SKB_LEN=${#SKB}
  [ "$SKB_LEN" -lt 64 ] && echo "KRITISCH: SECRET_KEY_BASE nur $SKB_LEN Zeichen (min. 64)"
  [ "$SKB_LEN" -ge 64 ] && echo "OK: SECRET_KEY_BASE hat $SKB_LEN Zeichen"
else
  echo "KRITISCH: SECRET_KEY_BASE nicht gesetzt"
fi

# Realtime Health
echo "=== Realtime Health ==="
docker compose exec -T realtime-dev.supabase-realtime \
  curl -sSf -o /dev/null -w "%{http_code}" \
  -H "Authorization: Bearer $(grep '^ANON_KEY=' $ENV_FILE | cut -d= -f2)" \
  http://localhost:4000/api/tenants/realtime-dev/health 2>/dev/null || echo "Health Check fehlgeschlagen"
```

Expectation:
- DB_ENC_KEY NOT "supabaserealtime" (default value)
- SECRET_KEY_BASE at least 64 characters
- Realtime health check successful

Risk if not met: Default DB_ENC_KEY = encryption is predictable.

---

## B10: Storage and MinIO

Verify that Storage and MinIO (if used) are securely configured.

```bash
# Storage Health
echo "=== Storage Health ==="
docker compose exec -T storage wget --no-verbose --tries=1 --spider http://localhost:5000/status 2>&1

# Storage Backend (file oder s3)?
echo "=== Storage Backend ==="
COMPOSE=$(find /opt -name "docker-compose*.yml" -path "*/supabase/*" 2>/dev/null)
grep "STORAGE_BACKEND" $COMPOSE 2>/dev/null || echo "Default: file (lokal)"

# MinIO vorhanden?
echo "=== MinIO ==="
docker ps --format '{{.Names}}\t{{.Image}}' 2>/dev/null | grep -i "minio"

# MinIO Ports nur localhost?
if docker ps 2>/dev/null | grep -q minio; then
  echo "=== MinIO Ports ==="
  ss -tlnp | grep -E "9000|9001"
  # Erwartung: 127.0.0.1, NICHT 0.0.0.0

  # MinIO Default-Credentials?
  ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)
  MINIO_USER=$(grep "MINIO_ROOT_USER" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
  MINIO_PASS=$(grep "MINIO_ROOT_PASSWORD" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
  [ "$MINIO_USER" = "minioadmin" ] && echo "KRITISCH: MinIO Root User ist 'minioadmin' (Default)"
  [ "$MINIO_PASS" = "minioadmin" ] && echo "KRITISCH: MinIO Root Password ist 'minioadmin' (Default)"
  [ -n "$MINIO_PASS" ] && [ ${#MINIO_PASS} -lt 8 ] && echo "WARNUNG: MinIO Passwort zu kurz (min. 8)"
fi

# Storage Volume Rechte
echo "=== Storage Volume ==="
ls -la /opt/supabase/volumes/storage/ 2>/dev/null | head -5
```

Expectation:
- Storage health check successful
- If MinIO: ports bound to 127.0.0.1 (not 0.0.0.0)
- If MinIO: no default credentials (minioadmin/minioadmin)
- Storage volume present and writable

Risk if not met: MinIO with default credentials on 0.0.0.0 = all files publicly readable and writable.

---

## B11: Internal Services (Analytics, Vector, Supavisor)

Verify that internal services are properly secured.

```bash
# Analytics (Logflare) nur intern?
echo "=== Analytics Port ==="
ss -tlnp | grep 4000
# Erwartung: 127.0.0.1:4000

# Logflare Tokens nicht auf Default?
ENV_FILE=$(find /opt -name ".env" -path "*/supabase/*" -not -path "*example*" 2>/dev/null | head -1)
echo "=== Logflare Tokens ==="
for var in LOGFLARE_PUBLIC_ACCESS_TOKEN LOGFLARE_PRIVATE_ACCESS_TOKEN; do
  VAL=$(grep "^${var}=" "$ENV_FILE" 2>/dev/null | cut -d= -f2)
  if [ -z "$VAL" ] || echo "$VAL" | grep -qiE "your-|change|example"; then
    echo "WARNUNG: $var ist nicht gesetzt oder Default"
  else
    echo "OK: $var ist gesetzt (${#VAL} Zeichen)"
  fi
done

# Vector Docker Socket Read-Only?
echo "=== Vector Docker Socket ==="
COMPOSE=$(find /opt -name "docker-compose.yml" -path "*/supabase/*" 2>/dev/null | head -1)
if grep -A5 "vector:" "$COMPOSE" 2>/dev/null | grep "docker.sock" | grep -q ":ro"; then
  echo "OK: Docker Socket ist Read-Only gemountet"
else
  echo "WARNUNG: Docker Socket möglicherweise nicht Read-Only"
fi

# Supavisor Port nur intern?
echo "=== Supavisor Port ==="
ss -tlnp | grep 6543
# Erwartung: 127.0.0.1:6543

# Pooler Config
echo "=== Pooler Config ==="
grep -E "POOLER_DEFAULT_POOL_SIZE|POOLER_MAX_CLIENT_CONN" "$ENV_FILE" 2>/dev/null
```

Expectation:
- Analytics on 127.0.0.1:4000 (not 0.0.0.0)
- Logflare tokens set (not defaults)
- Vector Docker socket mounted with :ro (read-only)
- Supavisor transaction port on localhost

Risk if not met: Analytics externally reachable = logs from all services publicly visible. Vector without read-only = container can execute Docker commands. Supavisor externally reachable = connection pooler (and thus DB) accessible from outside.

---

## B12: PostgreSQL Roles and Grants

Verify that database roles are correctly configured.

```bash
# Alle relevanten Rollen und ihre Rechte
echo "=== Datenbankrollen ==="
docker compose exec -T db psql -U postgres -c \
  "SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
   FROM pg_roles
   WHERE rolname IN ('anon','authenticated','service_role','authenticator',
     'supabase_admin','supabase_auth_admin','supabase_storage_admin')
   ORDER BY rolname;" 2>/dev/null

# anon darf NICHT superuser sein
echo "=== anon Superuser Check ==="
ANON_SUPER=$(docker compose exec -T db psql -U postgres -t -c \
  "SELECT rolsuper FROM pg_roles WHERE rolname = 'anon';" 2>/dev/null | tr -d ' ')
if [ "$ANON_SUPER" = "t" ]; then
  echo "KRITISCH: anon-Rolle ist Superuser!"
else
  echo "OK: anon-Rolle ist kein Superuser"
fi

# service_role hat die erwarteten Rechte?
echo "=== service_role Rechte ==="
docker compose exec -T db psql -U postgres -t -c \
  "SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = 'service_role';" 2>/dev/null

# Gibt es unbekannte Rollen mit Login-Berechtigung?
echo "=== Rollen mit Login ==="
docker compose exec -T db psql -U postgres -c \
  "SELECT rolname FROM pg_roles WHERE rolcanlogin = true
   AND rolname NOT IN ('postgres','authenticator','supabase_admin',
     'supabase_auth_admin','supabase_storage_admin','supabase_replication_admin',
     'supabase_read_only_user')
   ORDER BY rolname;" 2>/dev/null
```

Expectation:
- anon: NOT superuser, NOT createrole, NOT createdb
- authenticated: NOT superuser
- service_role: bypassrls = true (this is intended), NOT superuser
- No unknown roles with login privileges

Risk if not met: anon as superuser = every unauthenticated request has full DB access. Unknown roles with login can be backdoor accounts.

---

## C1: Backups

Verify that backups are correctly configured.

```bash
# Backup-Verzeichnis vorhanden?
ls -lh /opt/backups/*.gpg 2>/dev/null | tail -5

# Letztes Backup: Alter in Stunden
LAST=$(ls -t /opt/backups/*.gpg 2>/dev/null | head -1)
if [ -n "$LAST" ]; then
  AGE=$(( ($(date +%s) - $(stat -c %Y "$LAST")) / 3600 ))
  echo "Letztes Backup: $LAST ($AGE Stunden alt)"
  [ "$AGE" -gt 26 ] && echo "WARNUNG: Älter als 26 Stunden"
else
  echo "KRITISCH: Kein Backup gefunden"
fi

# Backup-Dateien nicht 0 Bytes?
find /opt/backups -name "*.gpg" -size 0 -print 2>/dev/null

# Backup verschlüsselt? (GPG Check)
LAST=$(ls -t /opt/backups/*.gpg 2>/dev/null | head -1)
if [ -n "$LAST" ]; then
  file "$LAST" | grep -i "pgp\|gpg\|encrypted" || echo "WARNUNG: Möglicherweise nicht verschlüsselt"
fi

# Cron Job aktiv?
crontab -l 2>/dev/null | grep "backup"

# Backups extern gespeichert? (auf audit-runner)
ssh deploy@10.0.1.11 "ls -lh /opt/backup-archive/*.gpg 2>/dev/null | tail -3" 2>/dev/null || \
  echo "WARNUNG: Externe Backups nicht prüfbar (kein SSH zum audit-runner)"

# Restore-Script vorhanden?
ls /opt/supabase/scripts/restore*.sh 2>/dev/null || echo "WARNUNG: Kein Restore-Script gefunden"
```

Expectation:
- Daily backup present (less than 26 hours old)
- Backups encrypted (GPG)
- No 0-byte files
- Cron job for daily backup active
- Backups stored offsite (audit-runner)
- Restore script available

Risk if not met: In case of server loss (hardware failure, ransomware) there is no way back.

---

## C2: Restore Tested

Verify that a restore test has been performed.

```bash
# Restore-Test Log vorhanden?
ls -la /var/log/restore-test.log 2>/dev/null

# Wann war der letzte Restore-Test?
if [ -f /var/log/restore-test.log ]; then
  stat -c "%y" /var/log/restore-test.log
  tail -5 /var/log/restore-test.log
fi

# Restore-Script vorhanden und ausführbar?
ls -la /opt/supabase/scripts/restore-test.sh 2>/dev/null
```

Expectation:
- Restore test performed at least once (log present)
- Last test no older than 30 days
- Restore script present and executable

Risk if not met: Backups that have never been tested are frequently unusable.

---

## C3: Daily Security Checks

Verify that automated security checks are configured.

```bash
# Security Check Script vorhanden?
ls -la /opt/supabase/scripts/security-check.sh 2>/dev/null || \
ls -la /opt/audit/scripts/security-check.sh 2>/dev/null || \
  echo "WARNUNG: Kein Security Check Script gefunden"

# Cron Job für tägliche Checks?
crontab -l 2>/dev/null | grep "security-check"

# Letzter Check-Log
ls -la /var/log/security-check.log 2>/dev/null
if [ -f /var/log/security-check.log ]; then
  echo "=== Letzte Einträge ==="
  tail -10 /var/log/security-check.log
fi
```

Expectation:
- Security check script present
- Daily cron job active
- Logs from today present

Risk if not met: Configuration drift goes undetected until an incident occurs.

---

## C4: Supabase Studio Access

Verify that Supabase Studio is not externally reachable.

```bash
# Studio Container läuft?
docker compose ps 2>/dev/null | grep -i "studio"

# Auf welchem Port/Interface lauscht Studio?
ss -tlnp | grep -E "3000|9000" 2>/dev/null

# Von localhost erreichbar?
curl -s -o /dev/null -w "%{http_code}" http://localhost:3000 2>/dev/null || echo "Nicht erreichbar"
```

Expectation:
- Studio either not in the production stack OR only listening on localhost/internal interface
- NOT listening on 0.0.0.0

Risk if not met: Studio with a default password grants full database access.

---

---

## M1: Unattended Upgrades (Automatic OS Security Patches)

```bash
echo "=== Unattended Upgrades installiert? ==="
dpkg -l | grep unattended-upgrades || echo "NICHT installiert"

echo "=== Service aktiv? ==="
systemctl is-active unattended-upgrades

echo "=== Konfiguration ==="
cat /etc/apt/apt.conf.d/50unattended-upgrades 2>/dev/null | grep -E "Allowed-Origins|Automatic-Reboot|Mail" | head -10

echo "=== Auto-Update Konfiguration ==="
cat /etc/apt/apt.conf.d/20auto-upgrades 2>/dev/null

echo "=== Letzte automatische Updates ==="
ls -la /var/log/unattended-upgrades/ 2>/dev/null
tail -20 /var/log/unattended-upgrades/unattended-upgrades.log 2>/dev/null || echo "Kein Log vorhanden"
```

Expectation:
- unattended-upgrades installed and active
- Security origins configured
- Auto-update interval set to daily
- Logs present

Risk: Without automatic security patches, known OS vulnerabilities accumulate over time.

---

## M2: Pending Updates

```bash
echo "=== Ausstehende Packages ==="
apt list --upgradable 2>/dev/null | head -20

echo "=== Davon Security Updates ==="
apt list --upgradable 2>/dev/null | grep -i security | head -10

echo "=== Letztes apt update ==="
stat -c "%y" /var/cache/apt/pkgcache.bin 2>/dev/null
AGE=$(( ($(date +%s) - $(stat -c %Y /var/cache/apt/pkgcache.bin 2>/dev/null || echo 0)) / 86400 ))
echo "Alter: ${AGE} Tage"
[ "$AGE" -gt 7 ] && echo "WARNUNG: apt update ist überfällig"

echo "=== Reboot erforderlich? ==="
test -f /var/run/reboot-required && cat /var/run/reboot-required || echo "Kein Reboot nötig"
```

Expectation:
- No pending security updates
- apt update less than 7 days old
- No pending reboot (or consciously scheduled)

---

## M3: Supabase Image Versions and Age

```bash
cd /opt/supabase 2>/dev/null || cd /opt

echo "=== Aktuelle Image Versionen ==="
docker compose images --format '{{.Repository}}:{{.Tag}}' 2>/dev/null

echo "=== Image Alter ==="
for image in $(docker compose images --format '{{.Repository}}:{{.Tag}}' 2>/dev/null); do
  CREATED=$(docker inspect --format='{{.Created}}' "$image" 2>/dev/null | cut -dT -f1)
  if [ -n "$CREATED" ]; then
    AGE=$(( ($(date +%s) - $(date -d "$CREATED" +%s 2>/dev/null || echo 0)) / 86400 ))
    echo "$image: erstellt $CREATED ($AGE Tage)"
    [ "$AGE" -gt 90 ] && echo "  → WARNUNG: Älter als 90 Tage"
  fi
done

echo "=== Images gepinnt (kein :latest)? ==="
grep "image:" docker-compose.yml 2>/dev/null | grep "latest" && \
  echo "WARNUNG: :latest Images gefunden" || echo "OK: Alle Images versioniert"

echo "=== Letzter Update-Commit ==="
git log --oneline --grep="Update\|update\|upgrade" -5 2>/dev/null || echo "Keine Update-Commits gefunden"

LAST_UPDATE=$(git log --format="%ai" --grep="Update\|update" -1 2>/dev/null | cut -d' ' -f1)
if [ -n "$LAST_UPDATE" ]; then
  UPDATE_AGE=$(( ($(date +%s) - $(date -d "$LAST_UPDATE" +%s)) / 86400 ))
  echo "Letztes Update: $LAST_UPDATE ($UPDATE_AGE Tage her)"
  [ "$UPDATE_AGE" -gt 45 ] && echo "WARNUNG: Über 45 Tage seit letztem Update"
fi
```

Expectation:
- All images pinned to specific versions (no :latest)
- No image older than 90 days
- Update commit within the last 45 days

Risk: Outdated images contain known CVEs. Supabase GoTrue, PostgREST, and Kong receive regular security patches.

---

## M4: Backup Integrity

```bash
echo "=== Letztes Backup ==="
LAST=$(ls -t /opt/backups/*.gpg 2>/dev/null | head -1)
if [ -n "$LAST" ]; then
  AGE=$(( ($(date +%s) - $(stat -c %Y "$LAST")) / 3600 ))
  SIZE=$(stat -c %s "$LAST")
  echo "Datei: $LAST"
  echo "Alter: ${AGE} Stunden"
  echo "Grösse: $(numfmt --to=iec $SIZE)"
  [ "$AGE" -gt 26 ] && echo "WARNUNG: Backup älter als 26 Stunden"
  [ "$SIZE" -lt 1024 ] && echo "KRITISCH: Backup verdächtig klein (< 1KB)"
else
  echo "KRITISCH: Kein Backup gefunden"
fi

echo "=== Backup Cron aktiv? ==="
crontab -l 2>/dev/null | grep "backup" || echo "WARNUNG: Kein Backup-Cron"

echo "=== Externe Backups (audit-runner)? ==="
ssh deploy@10.0.1.11 "ls -lh /opt/backup-archive/*.gpg 2>/dev/null | tail -3" 2>/dev/null || \
  echo "INFO: Externe Backups nicht prüfbar"

echo "=== Letzter Restore-Test ==="
if [ -f /var/log/restore-test.log ]; then
  RESTORE_DATE=$(stat -c "%y" /var/log/restore-test.log | cut -d' ' -f1)
  RESTORE_AGE=$(( ($(date +%s) - $(date -d "$RESTORE_DATE" +%s)) / 86400 ))
  echo "Letzter Test: $RESTORE_DATE ($RESTORE_AGE Tage)"
  [ "$RESTORE_AGE" -gt 35 ] && echo "WARNUNG: Restore-Test über 35 Tage her"
else
  echo "WARNUNG: Kein Restore-Test Log gefunden"
fi

echo "=== Restore-Test Cron? ==="
crontab -l 2>/dev/null | grep "restore" || echo "WARNUNG: Kein Restore-Test Cron"
```

Expectation:
- Daily backup present and current (< 26h)
- Backup not suspiciously small
- Backup stored offsite
- Restore test within the last 35 days
- Cron jobs active for both

---

## M5: Docker Engine and Tools

```bash
echo "=== Docker Version ==="
docker version --format 'Client: {{.Client.Version}}, Server: {{.Server.Version}}' 2>/dev/null

echo "=== Docker Compose Version ==="
docker compose version

echo "=== Caddy Version ==="
caddy version 2>/dev/null || echo "Caddy nicht installiert"

echo "=== Node.js Version ==="
node --version 2>/dev/null || echo "Node.js nicht installiert"

echo "=== Git Version ==="
git --version

echo "=== OpenSSL Version ==="
openssl version
```

Expectation:
- Docker: current stable version
- Caddy: current version
- OpenSSL: no known CVEs in the installed version

---

## M6: TLS Certificate Validity

```bash
echo "=== TLS Zertifikat ==="
CERT_END=$(echo | openssl s_client -connect localhost:443 2>/dev/null | \
  openssl x509 -noout -enddate 2>/dev/null | cut -d= -f2)
if [ -n "$CERT_END" ]; then
  DAYS=$(( ($(date -d "$CERT_END" +%s) - $(date +%s)) / 86400 ))
  echo "Ablauf: $CERT_END ($DAYS Tage)"
  [ "$DAYS" -lt 14 ] && echo "KRITISCH: Zertifikat läuft in $DAYS Tagen ab"
  [ "$DAYS" -lt 30 ] && echo "WARNUNG: Zertifikat läuft in $DAYS Tagen ab"
else
  echo "WARNUNG: Zertifikat nicht prüfbar"
fi

echo "=== Caddy Auto-Renewal aktiv? ==="
systemctl is-active caddy
# Caddy erneuert automatisch, aber nur wenn der Service läuft
```

Expectation:
- Certificate valid for at least 14 days
- Caddy service active (for auto-renewal)

---

## M7: Disk Space and Cleanup

```bash
echo "=== Disk Usage ==="
df -h / | tail -1

echo "=== Docker Disk Usage ==="
docker system df

echo "=== Alte Docker Images ==="
docker images --filter "dangling=true" -q | wc -l
echo "dangling Images (können gelöscht werden)"

echo "=== Alte Backups ==="
find /opt/backups -name "*.gpg" -mtime +30 -print | wc -l
echo "Backups älter als 30 Tage"

echo "=== Docker Logs Grösse ==="
du -sh /var/lib/docker/containers/*/  2>/dev/null | sort -rh | head -5
```

Expectation:
- Disk usage below 85%
- No large number of dangling Docker images
- Old backups are cleaned up (retention)

---

## M8: Audit-Runner Maintenance Monitoring

```bash
echo "=== Maintenance Check Cron auf audit-runner? ==="
ssh deploy@10.0.1.11 "crontab -l 2>/dev/null | grep maintenance" 2>/dev/null || \
  echo "WARNUNG: Kein Maintenance Check Cron auf audit-runner"

echo "=== Security Release Monitor Cron auf audit-runner? ==="
ssh deploy@10.0.1.11 "crontab -l 2>/dev/null | grep security-release" 2>/dev/null || \
  echo "WARNUNG: Kein Security Release Monitor Cron auf audit-runner"

echo "=== Trivy installiert auf audit-runner? ==="
ssh deploy@10.0.1.11 "trivy --version 2>/dev/null" || \
  echo "WARNUNG: Trivy nicht installiert auf audit-runner"

echo "=== Letzter Maintenance Report ==="
ssh deploy@10.0.1.11 "tail -20 /var/log/maintenance-check.log 2>/dev/null" 2>/dev/null || \
  echo "INFO: Maintenance Log nicht verfügbar"

echo "=== Letzter Security Release Report ==="
ssh deploy@10.0.1.11 "tail -20 /var/log/security-releases.log 2>/dev/null" 2>/dev/null || \
  echo "INFO: Security Release Log nicht verfügbar"

echo "=== E-Mail Alerting konfiguriert? ==="
ssh deploy@10.0.1.11 "which mail 2>/dev/null && echo 'mail Befehl verfügbar' || echo 'WARNUNG: mail nicht installiert'" 2>/dev/null
```

Expectation:
- Maintenance check cron on audit-runner active (weekly Monday)
- Security release monitor cron active (daily)
- Trivy installed
- Recent reports present
- Email alerting configured

---

## M9: Auto-Patch Setup

```bash
echo "=== Auto-Patch Script vorhanden? ==="
ls -la /opt/supabase/scripts/auto-patch.sh 2>/dev/null || echo "WARNUNG: auto-patch.sh fehlt"

echo "=== Auto-Patch Cron aktiv? ==="
crontab -l 2>/dev/null | grep auto-patch || echo "WARNUNG: Kein Auto-Patch Cron"

echo "=== Letzter Auto-Patch Lauf ==="
tail -20 /var/log/auto-patch.log 2>/dev/null || echo "INFO: Noch kein Auto-Patch gelaufen"

echo "=== Auto-Patch Ergebnisse ==="
grep -E "Patches eingespielt|Keine Patches|ABBRUCH|FEHLER" /var/log/auto-patch.log 2>/dev/null | tail -10

echo "=== Cron Zeitplan korrekt? ==="
echo "Erwartet:"
echo "  02:00 Backup"
echo "  03:00 Auto-Patch"
echo "  04:00 Restore-Test (monatlich)"
echo ""
echo "Aktuell:"
crontab -l 2>/dev/null | grep -E "backup|auto-patch|restore"
```

Expectation:
- auto-patch.sh present and executable
- Cron: 03:00 daily (AFTER the backup at 02:00)
- Recent logs show successful runs
- Backup cron MUST run before auto-patch cron

---

## Summary

Now produce a summary in the following format:

```
# Supabase Self-Hosting Audit - [DATE]

## Result

PASS:     X of Y checks (Y = approx. 34 checks: A1-A5, B1-B12, C1-C4, M1-M9)
WARNING:  X checks
CRITICAL: X checks

## Critical Findings (act immediately)
- ...

## Warnings (resolve this week)
- ...

## Passed
- ...

## Service Status Overview
| Service       | Status    | Finding                    |
|---------------|-----------|----------------------------|
| PostgreSQL    | OK/WARN   | ...                        |
| Kong          | OK/WARN   | ...                        |
| GoTrue (Auth) | OK/WARN   | ...                        |
| PostgREST     | OK/WARN   | ...                        |
| Realtime      | OK/WARN   | ...                        |
| Storage/MinIO | OK/WARN   | ...                        |
| Analytics     | OK/WARN   | ...                        |
| Vector        | OK/WARN   | ...                        |
| Supavisor     | OK/WARN   | ...                        |
| Studio        | OK/WARN   | ...                        |

## Update Status
| Area                 | Last Update    | Age    | Status   |
|----------------------|----------------|--------|----------|
| OS Security Patches  | ...            | X days | OK/WARN  |
| Supabase Images      | ...            | X days | OK/WARN  |
| Docker Engine        | v...           |        | OK/WARN  |
| Caddy                | v...           |        | OK/WARN  |
| TLS Certificate      | ...            | X days | OK/WARN  |

## Recommended Next Steps
1. ...
2. ...
3. ...
```

Prioritize strictly: Critical findings first, then warnings.
For each finding: What is the problem, why is it a risk, what is the fix.