Appearance

ADR 0009 — Comptes SQL séparés : migration (DDL) vs runtime (DML) ​

Date : 2026-07-30 Statut : Accepté

Contexte ​

Jusqu'ici, un seul compte SQL (DefaultConnection) servait à tout : requêtes applicatives, migrations EF au démarrage (DataInitializer.Initialize → MigrateAsync() dans Startup.Configure), et même DeleteAllDatabaseObjects() (drop complet du schéma, atteignable via Areas/Dev/DbController). Ce compte devait donc être db_owner. Conséquences :

  • Une injection SQL ou une compromission du conteneur web/worker donne les droits DDL complets sur la base de production (drop de tables inclus).
  • La migration au boot n'était pas attendue (Initialize(...) fire-and-forget, Task jetée) : elle tournait en parallèle des premières requêtes et ses exceptions étaient silencieuses.
  • La chaîne de connexion du DesignTimeDbContextFactory était codée en dur (localhost\SQLEXPRESS).

Contraintes d'exploitation : plusieurs mises à jour par jour via Watchtower (poll 15 min, staging et production sur des serveurs différents), pas de downtime supplémentaire acceptable, pas de blue/green.

Décision ​

Deux comptes SQL avec des rôles de base fixes, créés manuellement par l'administrateur de la base (pas de script en source) :

CompteRôles SQLUtilisé par
horizon_appdb_datareader + db_datawriterconteneurs Web + Worker (DefaultConnection)
horizon_migratordb_ddladmin + db_datareader + db_datawriterentrypoint du conteneur web uniquement
sql
-- sur master
CREATE LOGIN horizon_app      WITH PASSWORD = '<...>';
CREATE LOGIN horizon_migrator WITH PASSWORD = '<...>';

-- sur la base Horizon
CREATE USER horizon_app      FOR LOGIN horizon_app;
CREATE USER horizon_migrator FOR LOGIN horizon_migrator;

ALTER ROLE db_datareader ADD MEMBER horizon_app;
ALTER ROLE db_datawriter ADD MEMBER horizon_app;

ALTER ROLE db_ddladmin   ADD MEMBER horizon_migrator;
ALTER ROLE db_datareader ADD MEMBER horizon_migrator;  -- lecture __EFMigrationsHistory
ALTER ROLE db_datawriter ADD MEMBER horizon_migrator;  -- migrations de données

Rôles de base plutôt que GRANT table par table : db_datareader/db_datawriter couvrent automatiquement les tables créées par les migrations futures ; un GRANT par table exigerait une maintenance à chaque migration.

db_ddladmin et pas db_owner pour le migrator : suffisant pour CREATE/ALTER/DROP de tables, index, contraintes et sp_rename, mais ne permet ni de gérer utilisateurs/permissions ni de dropper la base. Une migration qui aurait besoin d'un GRANT échouera bruyamment — comportement voulu.

Migration « migrate-then-run » dans l'entrypoint du conteneur web (pattern init-container, docker/entrypoint.sh) :

  • Le Dockerfile (stage build) produit un bundle EF (dotnet ef migrations bundle, framework-dependent, /efbundle).
  • Au démarrage du conteneur : /efbundle --connection "$HORIZON_MIGRATION_CONNECTION", puis exec env -u HORIZON_MIGRATION_CONNECTION dotnet Horizon.Web.dll.
  • Le secret DDL est effacé avant l'exec : le process de l'app ne l'a jamais (même /proc/1/environ est propre) — une injection SQL/RCE dans l'app ne peut pas récupérer les droits DDL. Le secret reste visible via docker inspect côté hôte, même niveau de confiance que l'appsettings.json monté.
  • HORIZON_MIGRATION_CONNECTION est posée sur le conteneur web uniquement dans le compose serveur, jamais sur le worker. Absente → l'entrypoint saute la migration (image réutilisable sans rôle de migration).

Pourquoi l'entrypoint et pas un job CI migrate avant deploy : avec Watchtower, la séquence est naturellement correcte — l'ancien conteneur est arrêté avant que le nouveau démarre, donc l'ancienne app ne voit jamais le nouveau schéma et la nouvelle ne tourne jamais sur l'ancien. Zéro fenêtre de désynchronisation, sans discipline expand/contract. Le downtime par déploiement reste celui du restart Watchtower existant (+ quelques secondes de migration). Et le mécanisme est identique sur staging et production alors qu'ils sont sur des serveurs différents (un job CI aurait exigé SSH ou un runner par serveur).

Au démarrage de l'app (hors Development) : plus de MigrateAsync(). DataInitializer.VerifySchemaAsync() loggue en Critical (→ Sentry) si des migrations sont pendantes (défense en profondeur — ne devrait jamais arriver après l'entrypoint), puis SeedAsync() (rôles Identity, admin bootstrap, données de référence — DML pur). Le tout est attendu (GetAwaiter().GetResult()) et enveloppé d'un try/catch : on ne crash pas le boot (restart:always + Watchtower = boucle), on rend l'erreur visible.

DesignTimeDbContextFactory lit la variable d'environnement HORIZON_MIGRATION_CONNECTION (fallback : localhost\SQLEXPRESS en Windows auth pour le dev local). C'est le point d'entrée de dotnet ef en local ; le bundle reçoit sa connexion via --connection.

DataInitializer découpé : MigrateAsync(deleteDb) (DDL), VerifySchemaAsync() (contrôle), SeedAsync(seedDevUsers) (DML). Initialize() reste comme façade pour le seed Development (Areas/Dev/DbController).

Alternatives écartées ​

  • Job CI migrate entre test et deploy (première version de cette décision) : crée une course avec Watchtower dans les deux sens — l'ancienne app peut tourner jusqu'à 15 min sur le nouveau schéma, ou la nouvelle image peut être déployée avant la migration. Exigerait de plus un accès réseau du runner vers les deux serveurs (staging ≠ production).
  • Déploiement orchestré stop → migrate → start piloté par la CI : fenêtre nulle aussi, mais downtime à chaque déploiement (plusieurs/jour — refusé), et ne couvre pas staging (autre serveur, hors de portée du runner linux).
  • Discipline expand/contract (migrations toujours rétro-compatibles, changements destructifs étalés sur deux releases) : repose sur la rigueur humaine à chaque migration ; l'entrypoint donne la garantie mécaniquement. La discipline reste utile en plus, pas à la place.
  • Compte distinct pour le Worker : écarté (décision utilisateur) — même profil de droits que le web, pas de valeur immédiate.

Conséquences ​

  • Ordre de bascule (l'ancien compte db_owner reste actif jusqu'à la fin) :
    1. Créer les deux logins (admin DB), sur staging et production.
    2. Ajouter HORIZON_MIGRATION_CONNECTION (compte horizon_migrator) dans le compose serveur, service web uniquement, sur les deux serveurs.
    3. Déployer cette version ; vérifier dans les logs du conteneur entrypoint: migrations up to date.
    4. Basculer DefaultConnection sur horizon_app dans les appsettings.json serveur (web + worker).
    5. Désactiver l'ancien login.
  • Une migration qui échoue = web down (boucle de restart du conteneur) jusqu'à intervention, là où un job CI aurait bloqué avant le déploiement. Garde-fou : staging (même mécanisme) attrape la migration cassée avant la production. Le bundle est idempotent : au restart, il reprend là où __EFMigrationsHistory s'est arrêté.
  • Le worker ne migre pas : pendant un cycle Watchtower il peut tourner quelques secondes/minutes en décalé avec le schéma. Ses jobs sont des ticks (paiements 1 min, VP quotidien) qui réessaient au tick suivant ; une erreur ponctuelle part dans Sentry.
  • DeleteAllDatabaseObjects et le DbController deviennent inoffensifs en production même si atteints : horizon_app ne peut pas dropper (défense en profondeur, ne dispense pas du gating d'environnement).
  • L'environnement Integration n'est plus maintenu (TestCafe abandonné) : le boot ne fait plus le reset deleteDb d'Integration. La stack docker-compose.yml locale pose HORIZON_MIGRATION_CONNECTION = compte sa pour que l'entrypoint migre localement.
  • Requêtes SQL brutes runtime existantes (MiscController.MergeStopDuplicates : UPDATE) : DML pur, compatibles horizon_app. Toute future maintenance nécessitant du DDL devra passer par une migration EF.
  • docker/entrypoint.sh est en LF (.gitattributes : *.sh text eol=lf) — un checkout Windows en CRLF casserait le conteneur.

Contributors

No contributors

Changelog

No recent changes