Installation et sécurisation PostgreSQL + PostGIS #23

📋 Description

Installer la base géospatiale PostgreSQL + PostGIS de production sur le VPS, avec des accès sécurisés (port non exposé publiquement). La base de dev reste en local (Docker du poste, biowatch_dev) : le VPS n'héberge que la prod (garde-fous dev/prod).

🧭 Choix retenus

• Docker, avec la même image que le local (postgis/postgis:16-3.4-alpine, infra/docker) : dev et prod ont les mêmes versions de PostgreSQL et PostGIS.
• Une seule base sur le VPS : biowatch_prod. Le nom doit finir par _prod, sinon les jobs refusent de se connecter.
• Port 127.0.0.1:5432 uniquement. Docker contourne UFW pour les ports publiés : c'est la liaison sur la boucle locale qui protège le 5432.
• Unité systemd biowatch-postgres.service : démarre le conteneur au boot et attend qu'il réponde. Les jobs l'attendent par ce nom (After=/Wants=).
• Rôles :
  • biowatch_admin, superutilisateur pour l'administration uniquement ;
  • biowatch_jobs, qui crée et écrit les tables métier.
  • Les rôles plus fins (API en lecture seule, etc.) relèvent de Gestion des rôles et permissions DB #29.
• Secrets générés sur le VPS, jamais dans le dépôt :
  • /etc/biowatch/postgres.env (superutilisateur, root 600) ;
  • /etc/biowatch/db-jobs.env (connexion des jobs, root 600, lu par systemd via EnvironmentFile=).
• Script idempotent infra/vps/55-postgres.sh avec --dry-run, comme les autres scripts VPS. Il prouve en fin d'exécution l'écoute locale uniquement, la connexion du rôle des jobs et PostGIS actif.

✅ Acceptance Criteria

• Docker installé (paquets Ubuntu), groupe docker vide
• PostgreSQL installé (conteneur biowatch-postgis, redémarrage automatique)
• PostGIS activé (+ pgcrypto)
• Base biowatch_prod créée, rôle biowatch_jobs créé
• Port 5432 non exposé publiquement (lié à 127.0.0.1, vérifié depuis l'extérieur)
• Connexion locale fonctionnelle (rôle des jobs, en TCP)
• Procédure documentée dans infra/vps/README.md

🔗 Prerequisite Issues

DoR : VPS opérationnel (#22, #25, #28 : /etc/biowatch et comptes de service en place).
Utilisé par : #27 (déploiement des jobs, EnvironmentFile=/etc/biowatch/db-jobs.env, After=biowatch-postgres.service), #29 (rôles et permissions), #30 (sauvegardes).

Source : Notion « Installation et sécurisation PostgreSQL + PostGIS » (Config VPS). Mis à jour le 06/11/2026 : choix Docker, base prod seule, rôles et fichiers de secrets.