[Infra] Installation PostgreSQL 17

olivier commented

2026-09-01 09:50:34 +00:00

Member

Exigence couverte

No response

Épreuve servie

EC01 — Architecture

Ce qu'on veut obtenir

Un worker pour la base de donnée PostgreSQL 17 qui va contenir les différentes base de données :

Enervision_prod (role + bdd)
Enervision_preprod (role + bdd)
keycloack (ole + bdd)
grafana (role only)
mlflow (role + bdd)

Le but est de ségrégués chaque bdd en fonction de son rôle pour des raisons de sécurité.

Il y a en plus timescaledb en extension afin de garantir la rapidité pour la gestion des données en masse en temps réels

Il faut aussi mettre en place les backups de la bdd en local en cas de corruption de données

Critères d'acceptation

BDD + roles enervision prod
BDD + roles enervision preprod
BDD + roles keycloack
BDD + roles mlflow
roles grafana
Backup mise en place en local
Extension timescaledb
remote access par role

Comment on le vérifie

Cluster PostgreSQL :

Liste des BDD :

Liste des roles :

Preuve de ségrégation des bases :

Preuve de l'extension TimeScaleDB :

Manuel d'exploitation à mettre à jour

No response

Risque et retour arrière

No response

### Exigence couverte _No response_ ### Épreuve servie EC01 — Architecture ### Ce qu'on veut obtenir Un worker pour la base de donnée PostgreSQL 17 qui va contenir les différentes base de données : - Enervision_prod (role + bdd) - Enervision_preprod (role + bdd) - keycloack (ole + bdd) - grafana (role only) - mlflow (role + bdd) Le but est de ségrégués chaque bdd en fonction de son rôle pour des raisons de sécurité. Il y a en plus timescaledb en extension afin de garantir la rapidité pour la gestion des données en masse en temps réels Il faut aussi mettre en place les backups de la bdd en local en cas de corruption de données ### Critères d'acceptation - [x] BDD + roles enervision prod - [x] BDD + roles enervision preprod - [x] BDD + roles keycloack - [x] BDD + roles mlflow - [x] roles grafana - [x] Backup mise en place en local - [x] Extension timescaledb - [x] remote access par role ### Comment on le vérifie Cluster PostgreSQL : ![image](/attachments/536dd030-3b74-4157-83bc-d42558358857) Liste des BDD : ![image](/attachments/944a825d-d9f3-484d-a925-a3f3a31f9adb) Liste des roles : ![image](/attachments/a376f94f-d836-4dea-8262-0b8805f713db) Preuve de ségrégation des bases : ![image](/attachments/b6f1b301-5ffa-4745-ae2a-79bbe605fb84) Preuve de l'extension TimeScaleDB : ![image](/attachments/7c08a6cc-bb85-46fd-87a1-d5ad7234bb5b) ### Manuel d'exploitation à mettre à jour _No response_ ### Risque et retour arrière _No response_

image.png

79 KiB

image.png

166 KiB

image.png

144 KiB

image.png

631 KiB

image.png

119 KiB

olivier added the

Kind/Infra

label

2026-09-01 09:50:34 +00:00

florian changed title from ~~[infra] Installation PostgreSQL 17~~ to [Infra] Installation PostgreSQL 17

2026-09-01 09:51:26 +00:00

olivier was assigned by florian

2026-09-01 09:51:37 +00:00

florian added this to the EnerVision project

2026-09-01 09:51:50 +00:00

florian added the

labels

2026-09-01 09:52:54 +00:00

olivier added spent time

2026-09-01 09:58:28 +00:00

3 hours

olivier added the

Reviewed

Confirmed

Kind/Data

labels

2026-09-01 09:59:19 +00:00

lenaic added a new dependency

2026-09-01 10:36:09 +00:00

#29 [infra] TimescaleDB, hypertables et compression

lenaic added a new dependency

2026-09-01 10:36:09 +00:00

#30 [infra] MLflow, registre de modèles

lenaic commented

2026-09-01 11:44:48 +00:00

Owner

Relu ce midi, sur le serveur. Le travail est bon et va plus loin que le titre du ticket : la ségrégation base par base avec un rôle par usage, grafana_ro en lecture seule, aucun superutilisateur en dehors de postgres. C'est une vraie décision de conception, elle sert l'ENF-02 et elle se défendra bien à l'oral.

Quatre points avant de fermer.

1. La sauvegarde ne passera pas cette nuit

/usr/local/bin/pg-backup.sh sauvegarde enervision, keycloak et mlflow. La base enervision n'existe plus, elle a été scindée en enervision_prod et enervision_preprod après le dernier passage.

$ sudo -u postgres pg_dump -p 5433 -Fc enervision
FATAL: la base de données « enervision » n'existe pas

Le script commence par set -euo pipefail, donc il s'arrête à la première erreur. Cette nuit à 2h30, ni enervision_prod ni enervision_preprod ne seront sauvegardées, et rien ne le signalera. Les seuls fichiers présents datent de 8h08.

Correctif, une ligne :

for d in enervision_prod enervision_preprod keycloak mlflow; do

L'ENF-05 fixe une perte maximale de 24 heures, c'est ce critère qui tombe.

2. L'accès distant est à trancher, pas à corriger

Le critère « remote access par role » est coché, donc l'ouverture réseau est voulue. Sauf qu'elle contredit l'ENF-14, qu'on a ratifiée hier : ce qui ne doit pas sortir écoute sur 127.0.0.1.

Aujourd'hui la base répond sur 10.105.200.41:5433, avec ufw ouvert à tous et host all all 0.0.0.0/0 dans pg_hba.conf. Vérifié depuis un poste : la connexion aboutit. Les quatre autres groupes de la salle n'ont qu'un mot de passe à franchir.

Le besoin derrière est légitime — se connecter avec un client graphique depuis son portable. Le tunnel le sert sans rien exposer :

ssh -L 5433:127.0.0.1:5433 g2

puis connexion sur localhost:5433.

Soit on part là-dessus, soit on assume l'ouverture et on révise l'ENF-14 en écrivant pourquoi. Les deux se défendent, mais il faut choisir et l'écrire. Je propose le tunnel.

3. Le port est 5433, pas 5432

Le plan d'adressage de docs/EXIGENCES-collectives.md annonce 5432. L'écart vient de l'installation : postgresql-16 occupait 5432 quand le cluster 17 a été créé, il a pris le port suivant.

Le plus simple est de corriger le document plutôt que de déplacer le cluster, mais il faut le faire avant que quelqu'un code une chaîne de connexion sur 5432.

4. Trois champs restés vides

Exigence couverte : vide. Ce ticket sert ENF-02, ENF-05 et ENF-14. C'est ce lien qui construit la chaîne exigence, ticket, commit, test.

Épreuve servie : marqué EC01. L'EC01 est déjà rendu. Ce travail sert l'EC04 pour la sécurisation et l'EC05 pour la donnée, et ce libellé décide à quelle preuve individuelle il comptera au jour 10.

Manuel d'exploitation : vide, et docs/runbooks/ ne contient que son README. L'ENF-16 demande un manuel par geste sensible : ici la restauration. La base enervision_restore_test existe déjà, donc la procédure est à moitié faite, il reste à l'écrire et à la faire rejouer par quelqu'un d'autre.

Pour fermer

Le correctif de sauvegarde, l'arbitrage sur l'accès distant, le manuel de restauration, et les deux libellés remis droit. Le reste est acquis.

Dernier point, qui ne t'est pas adressé personnellement : tout a été fait sous le compte root partagé, dont une connexion par mot de passe sans empreinte de clé. Le journal ne permet donc pas de t'attribuer ce travail, alors que l'ENF-15 l'exige. C'est de ma responsabilité, le durcissement SSH n'est pas appliqué. Je m'en occupe demain matin.

Relu ce midi, sur le serveur. Le travail est bon et va plus loin que le titre du ticket : la ségrégation base par base avec un rôle par usage, `grafana_ro` en lecture seule, aucun superutilisateur en dehors de `postgres`. C'est une vraie décision de conception, elle sert l'ENF-02 et elle se défendra bien à l'oral. Quatre points avant de fermer. ## 1. La sauvegarde ne passera pas cette nuit `/usr/local/bin/pg-backup.sh` sauvegarde `enervision`, `keycloak` et `mlflow`. La base `enervision` n'existe plus, elle a été scindée en `enervision_prod` et `enervision_preprod` après le dernier passage. ``` $ sudo -u postgres pg_dump -p 5433 -Fc enervision FATAL: la base de données « enervision » n'existe pas ``` Le script commence par `set -euo pipefail`, donc il s'arrête à la première erreur. Cette nuit à 2h30, ni `enervision_prod` ni `enervision_preprod` ne seront sauvegardées, et rien ne le signalera. Les seuls fichiers présents datent de 8h08. Correctif, une ligne : ``` for d in enervision_prod enervision_preprod keycloak mlflow; do ``` L'ENF-05 fixe une perte maximale de 24 heures, c'est ce critère qui tombe. ## 2. L'accès distant est à trancher, pas à corriger Le critère « remote access par role » est coché, donc l'ouverture réseau est voulue. Sauf qu'elle contredit l'ENF-14, qu'on a ratifiée hier : ce qui ne doit pas sortir écoute sur `127.0.0.1`. Aujourd'hui la base répond sur `10.105.200.41:5433`, avec `ufw` ouvert à tous et `host all all 0.0.0.0/0` dans `pg_hba.conf`. Vérifié depuis un poste : la connexion aboutit. Les quatre autres groupes de la salle n'ont qu'un mot de passe à franchir. Le besoin derrière est légitime — se connecter avec un client graphique depuis son portable. Le tunnel le sert sans rien exposer : ``` ssh -L 5433:127.0.0.1:5433 g2 ``` puis connexion sur `localhost:5433`. Soit on part là-dessus, soit on assume l'ouverture et on révise l'ENF-14 en écrivant pourquoi. Les deux se défendent, mais il faut choisir et l'écrire. Je propose le tunnel. ## 3. Le port est 5433, pas 5432 Le plan d'adressage de `docs/EXIGENCES-collectives.md` annonce 5432. L'écart vient de l'installation : `postgresql-16` occupait 5432 quand le cluster 17 a été créé, il a pris le port suivant. Le plus simple est de corriger le document plutôt que de déplacer le cluster, mais il faut le faire avant que quelqu'un code une chaîne de connexion sur 5432. ## 4. Trois champs restés vides **Exigence couverte** : vide. Ce ticket sert ENF-02, ENF-05 et ENF-14. C'est ce lien qui construit la chaîne exigence, ticket, commit, test. **Épreuve servie** : marqué EC01. L'EC01 est déjà rendu. Ce travail sert l'EC04 pour la sécurisation et l'EC05 pour la donnée, et ce libellé décide à quelle preuve individuelle il comptera au jour 10. **Manuel d'exploitation** : vide, et `docs/runbooks/` ne contient que son README. L'ENF-16 demande un manuel par geste sensible : ici la restauration. La base `enervision_restore_test` existe déjà, donc la procédure est à moitié faite, il reste à l'écrire et à la faire rejouer par quelqu'un d'autre. ## Pour fermer Le correctif de sauvegarde, l'arbitrage sur l'accès distant, le manuel de restauration, et les deux libellés remis droit. Le reste est acquis. Dernier point, qui ne t'est pas adressé personnellement : tout a été fait sous le compte `root` partagé, dont une connexion par mot de passe sans empreinte de clé. Le journal ne permet donc pas de t'attribuer ce travail, alors que l'ENF-15 l'exige. C'est de ma responsabilité, le durcissement SSH n'est pas appliqué. Je m'en occupe demain matin.

lenaic referenced this issue

2026-09-01 14:02:25 +00:00

outillage: le point du soir pose des questions nominatives #55

lenaic referenced this issue

2026-09-02 08:32:42 +00:00

[décision] Fermer l'accès distant à PostgreSQL et passer par un tunnel SSH #65

lenaic added a new dependency

2026-09-02 08:32:42 +00:00

#65 [décision] Fermer l'accès distant à PostgreSQL et passer par un tunnel SSH

lenaic removed a dependency

2026-09-02 08:32:54 +00:00

#65 [décision] Fermer l'accès distant à PostgreSQL et passer par un tunnel SSH

lenaic commented

2026-09-02 08:36:15 +00:00

Owner

Fermé. Sept critères sur huit sont tenus, et la documentation est allée plus loin que ce que le ticket demandait : docs/POSTGRESQL.md porte l'installation, la configuration, le script de sauvegarde corrigé et les écarts constatés, docs/runbooks/postgresql.md porte les gestes.

Le huitième critère, l'accès distant, sort de ce ticket. Il est repris au #65 : la base repasse en écoute locale et l'accès depuis les postes se fait par tunnel SSH, avec la fiche ADR qui va avec.

Deux suites sont ouvertes ailleurs plutôt que laissées en suspens ici. Le #64 versionne les scripts d'exploitation, qui vivent aujourd'hui uniquement sur le serveur avec une copie recopiée dans une page de documentation. Et l'écart n°4 que tu as identifié, l'absence d'alerte quand la sauvegarde échoue, reste à traiter : c'est ce qui a rendu la panne du 1er septembre muette.

Reste à corriger sur ce ticket avant qu'il serve de preuve au jour 10 : l'épreuve servie est marquée EC01, alors que ce travail sert l'EC04 pour la sécurisation et l'EC05 pour la donnée. Et le champ « exigence couverte » est vide, il devrait porter ENF-02, ENF-05 et ENF-14.

Fermé. Sept critères sur huit sont tenus, et la documentation est allée plus loin que ce que le ticket demandait : `docs/POSTGRESQL.md` porte l'installation, la configuration, le script de sauvegarde corrigé et les écarts constatés, `docs/runbooks/postgresql.md` porte les gestes. Le huitième critère, l'accès distant, sort de ce ticket. Il est repris au #65 : la base repasse en écoute locale et l'accès depuis les postes se fait par tunnel SSH, avec la fiche ADR qui va avec. Deux suites sont ouvertes ailleurs plutôt que laissées en suspens ici. Le #64 versionne les scripts d'exploitation, qui vivent aujourd'hui uniquement sur le serveur avec une copie recopiée dans une page de documentation. Et l'écart n°4 que tu as identifié, l'absence d'alerte quand la sauvegarde échoue, reste à traiter : c'est ce qui a rendu la panne du 1er septembre muette. Reste à corriger sur ce ticket avant qu'il serve de preuve au jour 10 : l'épreuve servie est marquée EC01, alors que ce travail sert l'EC04 pour la sécurisation et l'EC05 pour la donnée. Et le champ « exigence couverte » est vide, il devrait porter ENF-02, ENF-05 et ENF-14.

lenaic closed this issue

2026-09-02 08:36:15 +00:00

gabriel added this to the J1 — 04/09 bout-en-bout milestone

2026-09-03 12:48:28 +00:00

Rows
Columns

[Infra] Installation PostgreSQL 17 #26

Exigence couverte

Épreuve servie

Ce qu'on veut obtenir

Critères d'acceptation

Comment on le vérifie

Manuel d'exploitation à mettre à jour

Risque et retour arrière

1. La sauvegarde ne passera pas cette nuit

2. L'accès distant est à trancher, pas à corriger

3. Le port est 5433, pas 5432

4. Trois champs restés vides

Pour fermer