01 restore postgres pitr

Runbook 01 — Restauration PostgreSQL PITR (Point-In-Time Recovery)

1. Quand activer ce runbook

Scénario	Activer ?
Suppression accidentelle d’une table ou d’une ligne (ex : DELETE FROM clients sans WHERE)	OUI
Bug applicatif ayant corrompu logiquement une donnée (ex : import audit a écrit du JSON invalide dans ControlResult)	OUI
Échec de migration Prisma laissant la base dans un état incohérent	OUI
Lockdown chaîne hash audit log déclenché (platformState.audit_log_lockdown.locked = true)	OUI (PITR pré-corruption)
Container postgres crashé sans corruption disque	NON (un simple restart suffit)
Corruption disque détectée par fsck	OUI mais commencer par une copie binaire avant restore
Ransomware confirmé	NON — utiliser 05-recover-from-ransomware.md

2. Objectifs

• RPO atteint : 5 minutes (WAL streaming continu)
• RTO cible : 2 heures (de la décision à postgres ré-actif)
• WRT cible : 30 minutes (vérifications post-restore)

3. Prérequis

• Accès SSH au VPS production OVH avec utilisateur mssp
• Accès make + docker compose configuré
• Vault opérationnel et unsealed (sinon : exécuter 02-restore-vault-from-snapshot.md d’abord)
• DR AppRole creds présentes (/vault/approle-dr/dr.env)
• pgbackrest stanza healthy sur OVH (vérifier avec make dr-shell-pg puis pgbackrest --stanza=mssp info)
• Connaître le target-time ou target-xid vers lequel restaurer (cf. §5)

4. Communication client (si plateforme exposée publiquement au moment de l’incident)

Avant de commencer, envoyer à tous les clients actifs :

Objet : SnakySec — Maintenance d'urgence en cours

Une opération de restauration de la base de données est en cours pour
résoudre un incident technique survenu à HH:MM UTC.

La plateforme sera indisponible pendant maximum 2 heures. Aucune perte de
données significative attendue (RPO 5 minutes maximum).

Nous publierons un rapport post-incident sous 7 jours conformément à votre
contrat. Vos données d'audits, configurations et rapports sont intègres et
seront restaurées dans leur état au plus proche du moment précédant l'incident.

Pour toute question urgente : [e-mail]

5. Identifier le target-time ou target-xid

5.1 Si l’incident a une heure connue (cas le plus fréquent)

Le target-time doit être juste avant l’événement déclencheur. Exemple : si la suppression accidentelle a eu lieu à 2026-04-26 14:32:15 UTC, choisir 2026-04-26 14:32:00 UTC (15 secondes plus tôt).

# Format attendu : YYYY-MM-DD HH:MM:SS+00 (UTC obligatoire pour cohérence)
TARGET_TIME="2026-04-26 14:32:00+00"

5.2 Si on connaît le numéro de transaction

Visible dans les logs Postgres, l’audit log applicatif, ou les breadcrumbs Sentry quand l’incident est tracé :

TARGET_XID="987654321"

5.3 Si le lockdown chaîne hash a été déclenché

Lire la séquence cassée depuis platformState puis remonter aux logs Postgres :

docker exec mssp-postgres psql -U mssp -d mssp_platform -c \
  "SELECT value->>'brokenSeq' AS broken_seq, value->>'lockedAt' AS locked_at \
   FROM platform_state WHERE key = 'audit_log_lockdown';"

Convertir lockedAt en target-time minus 5 minutes (marge de sécurité).

6. Procédure de restauration

6.1 Sauvegarde de l’état actuel (CRITIQUE — avant tout restore)

Une mauvaise restauration est récupérable SI on a une copie de l’état présent.

# Snapshot binaire du volume postgres-data avant intervention
ssh [e-mail]
sudo tar czf /opt/mssp/snapshots/pre-restore-$(date +%Y%m%dT%H%M%SZ).tar.gz \
  -C /var/lib/docker/volumes/platform_postgres-data _data

Conserver ce fichier jusqu’à validation complète (étape §7).

6.2 Stop de l’application + worker (mise hors service)

cd /opt/mssp/app/platform
make app-down
docker compose -f compose/_common.yml -f compose/app.prod.yml stop \
  worker-chain worker-digest worker-retention worker-scheduler \
  worker-import worker-deadline worker-regression worker-permission-expiry

6.3 Stop postgres (sans supprimer le volume)

make db-down

6.4 Vider le volume postgres-data

docker run --rm \
  -v platform_postgres-data:/data \
  alpine sh -c "rm -rf /data/* /data/.pgbackrest 2>/dev/null || true"

6.5 Lancer le restore pgbackrest

Le restore se fait depuis le repo OVH par défaut. Si OVH inaccessible, ajouter --repo=2 pour basculer sur Scaleway.

make db-up
sleep 5  # postgres démarre en mode "no PGDATA, will initdb"

# Wait — non. PGDATA vide va déclencher initdb. Stopper postgres et restorer
# AVANT que postgres tente d'initdb.

Procédure correcte : restore via container temporaire, PAS via le service postgres normal.

# Le service postgres ne doit PAS être démarré pendant le restore
make db-down

# Container temporaire qui partage le volume + a pgbackrest
docker run --rm -it \
  --network platform_mssp-net \
  -v platform_postgres-data:/var/lib/postgresql/data \
  -v platform_pgbackrest-log:/var/log/pgbackrest \
  -v platform_pgbackrest-spool:/var/spool/pgbackrest \
  -v platform_mssp-approle-dr:/vault/approle-dr:ro \
  -v $(pwd)/../../scripts/dr:/dr:ro \
  -v $(pwd)/../docker/postgres/pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf:ro \
  -e VAULT_ADDR=http://mssp-vault:8200 \
  -e VAULT_DR_APPROLE_FILE=/vault/approle-dr/dr.env \
  --user postgres \
  registry.gitlab.com/snakysec/mssp-snakysec-multi-tenants/postgres:16-pgbackrest \
  bash /dr/restore/postgres-pitr.sh "${TARGET_TIME}"

Le script postgres-pitr.sh (cf. scripts/dr/restore/postgres-pitr.sh) fait :

1. Lit pgbackrest_cipher_pass + S3 keys depuis Vault DR
2. Exécute pgbackrest restore --type=time --target="${TARGET_TIME}" --target-action=pause
3. Logue progression à mesure

Durée typique : 30 min - 1h selon volume base + WAL à rejouer.

6.6 Démarrage postgres en mode recovery

make db-up
docker logs -f mssp-postgres

Postgres va rejouer les WAL jusqu’au target-time, puis pause (recovery_target_action=pause).

À ce stade, postgres accepte les connexions en lecture seule. Vérifier :

docker exec mssp-postgres psql -U mssp -d mssp_platform -c "SELECT pg_is_in_recovery();"
# attendu : t (true, en recovery)

docker exec mssp-postgres psql -U mssp -d mssp_platform -c \
  "SELECT MAX(seq) AS tip_seq, MAX(\"createdAt\") AS tip_time FROM platform_audit_log;"
# vérifier que tip_time est juste avant target-time

6.7 Promotion (sortie du mode recovery)

Si la base correspond à l’état attendu, promouvoir :

docker exec mssp-postgres psql -U mssp -d mssp_platform -c "SELECT pg_promote();"
docker exec mssp-postgres psql -U mssp -d mssp_platform -c "SELECT pg_is_in_recovery();"
# attendu : f (false, plus en recovery, base normale)

Si la base n’est PAS dans l’état attendu, ne pas promouvoir. Restorer à un target-time différent en repassant par §6.4.

7. Validation post-restore

7.1 Smoke check automatique

make dr-shell
/dr/restore/smoke-after-restore.sh

Vérifications effectuées :

• Tables critiques présentes : User, Role, Client, ClientSecret, AuditRun, ControlResult, GapFinding, Baseline, PlatformAuditLog, LogAnchor, PlatformState
• Compte d’enregistrements dans chaque table (alerte si déviation >10% vs avant restore)
• Chaîne hash Ed25519 valide : verifyFullChain() retourne valid: true
• Dernier LogAnchor Ed25519 valide signature
• Dernier AuditRun < target-time
• platformState.audit_log_lockdown.locked = false (sinon lever manuellement après vérification)

7.2 Smoke check fonctionnel

# Démarrer l'application
make app-up
docker compose -f compose/_common.yml -f compose/app.prod.yml up -d \
  worker-chain worker-digest worker-retention worker-scheduler \
  worker-import worker-deadline worker-regression worker-permission-expiry

# Attendre healthy
docker compose ps next-app
# attendu : status (healthy)

# Tester login + page audit
curl -sI https://snakysec.com/api/health
# attendu : HTTP/2 200

7.3 Lever le lockdown si nécessaire

Si platformState.audit_log_lockdown.locked = true (héritage d’avant restore) :

docker exec mssp-postgres psql -U mssp -d mssp_platform -c \
  "UPDATE platform_state \
   SET value = jsonb_set(value, '{locked}', 'false'::jsonb) \
       || jsonb_build_object('acknowledgedAt', NOW()::text, \
                             'acknowledgedBy', 'restore-runbook-01') \
   WHERE key = 'audit_log_lockdown';"

Émettre un événement audit pour traçabilité :

docker exec mssp-postgres psql -U mssp -d mssp_platform -c \
  "INSERT INTO platform_audit_log \
     (id, action, outcome, severity, \"resourceType\", \"sourceService\", \
      \"actorType\", \"actorDisplay\", \"changeSummary\") \
   VALUES \
     (gen_random_uuid(), 'platform.dr.restore_completed', 'SUCCESS', 'CRITICAL', \
      'Platform', 'dr-runbook', 'SYSTEM', \
      'DR Runbook 01 — Postgres PITR', \
      'Restore PITR completed to target-time ${TARGET_TIME}, lockdown lifted');"

8. Communication post-incident

8.1 Email aux clients (sous 4h après reprise)

Objet : SnakySec — Incident résolu, plateforme à nouveau disponible

L'opération de restauration s'est achevée à HH:MM UTC.

Synthèse :
- Incident : <type>
- Détection : YYYY-MM-DD HH:MM UTC
- Reprise : YYYY-MM-DD HH:MM UTC
- RPO réel constaté : N minutes
- Données impactées : aucune perte significative

Vos audits, rapports et configurations sont intègres. Un rapport
post-incident détaillé vous sera transmis sous 7 jours.

Si vous constatez une anomalie sur vos données : [e-mail]

8.2 Si breach data perso (cas exceptionnel)

Activer la procédure docs/dr/incident-response/03-cnil-rgpd-notification.md sous 72h.

8.3 Procès-verbal de restauration

Remplir le template docs/dr/templates/post-incident-report.md et le déposer dans docs/dr/test-results/YYYY-MM-DD-pitr-restore.md.

9. Erreurs courantes et solutions

Erreur	Cause probable	Solution
pgbackrest: ERROR: [055]: unable to load info file	Stanza non créée ou repo inaccessible	Vérifier S3 keys + ré-exécuter stanza-create (cf. scripts/dr/setup/pgbackrest-stanza-create.sh)
pgbackrest: ERROR: [031]: target time before earliest archive	target-time antérieur à la rétention WAL (>3 mois)	Restore impossible, données perdues. Activer plan B : annonce client + RGPD
Postgres ne démarre pas après restore : database files are incompatible with server	Restore avec image postgres différente version	Re-build image custom postgres + retry
pg_promote() retourne false	Recovery pas terminée (WAL encore à rejouer)	Attendre puis re-tester
Chaîne Ed25519 invalide post-restore	LogAnchor signé après le target-time mais entrée correspondante restaurée	Re-restore à un target-time antérieur au LogAnchor problématique

10. Validation du runbook

Ce runbook est testé annuellement (Q1) sur l’environnement pré-prod avec seed représentatif. Les résultats sont consignés dans docs/dr/test-results/.

Version	Date	Auteur
1.0	2026-04-26	Nicolas Schiffgens