fix(ci): prioritize /root/.ssh/gitea_ci_key mount

.env.example

@@ -17,6 +17,21 @@ SPRING_MAIL_PROPERTIES_MAIL_SMTP_STARTTLS_ENABLE=true
 # Expéditeur E-mail (doit être un expéditeur validé dans Brevo)
 APP_MAIL_FROM=astalange1933@gmail.com
 
+# Configuration Sauvegarde Automatique & API Brevo (v3)
+BREVO_API_KEY=xkeysib-votre_cle_api_brevo
+NOTIFICATION_EMAIL=astalange1933@gmail.com
+BACKUP_DIR=/var/backups/as-talange
+RETENTION_DAYS=7
+
+# Optionnel: Transfert automatique de la sauvegarde de la BDD vers le VPS via SCP/SSH
+ENABLE_REMOTE_COPY=false
+REMOTE_HOST=votre-vps-ip-ou-domaine
+REMOTE_USER=root
+REMOTE_PORT=22
+REMOTE_BACKUP_DIR=/var/backups/as-talange
+SSH_KEY_PATH=/home/user/.ssh/id_rsa
+
 # Configuration Captcha (si applicable)
 CAPTCHA_SITEKEY=
 CAPTCHA_SECRET=
+

.gitea/workflows/ci-prod.yml

@@ -49,14 +49,26 @@ jobs:
         env:
           PROD_DB_USER: ${{ secrets.PROD_DB_USER }}
           PROD_DB_PASSWORD: ${{ secrets.PROD_DB_PASSWORD }}
+          PROD_DB_NAME: ${{ secrets.PROD_DB_NAME }}
           CAPTCHA_SECRET: ${{ secrets.CAPTCHA_SECRET }}
           CAPTCHA_SITEKEY: ${{ secrets.CAPTCHA_SITEKEY }}
           MAIL_USERNAME: ${{ secrets.MAIL_USERNAME }}
           MAIL_PASSWORD: ${{ secrets.MAIL_PASSWORD }}
           MAIL_FROM: ${{ secrets.MAIL_FROM }}
+          BREVO_API_KEY: ${{ secrets.BREVO_API_KEY }}
+          NOTIFICATION_EMAIL: ${{ secrets.NOTIFICATION_EMAIL }}
+          BACKUP_DIR: ${{ secrets.BACKUP_DIR }}
+          RETENTION_DAYS: ${{ secrets.RETENTION_DAYS }}
+          ENABLE_REMOTE_COPY: ${{ secrets.ENABLE_REMOTE_COPY }}
+          REMOTE_HOST: ${{ secrets.REMOTE_HOST }}
+          REMOTE_USER: ${{ secrets.REMOTE_USER }}
+          REMOTE_PORT: ${{ secrets.REMOTE_PORT }}
+          REMOTE_BACKUP_DIR: ${{ secrets.REMOTE_BACKUP_DIR }}
+          SSH_KEY_PATH: ${{ secrets.SSH_KEY_PATH }}
         run: |
           echo "POSTGRES_USER=${PROD_DB_USER:-myuser}" > .env
           echo "POSTGRES_PASSWORD=${PROD_DB_PASSWORD:-mypassword}" >> .env
+          [ -n "${PROD_DB_NAME}" ] && echo "POSTGRES_DB=${PROD_DB_NAME}" >> .env
           echo "CAPTCHA_SECRET=${CAPTCHA_SECRET:-1x0000000000000000000000000000000AA}" >> .env
           echo "CAPTCHA_SITEKEY=${CAPTCHA_SITEKEY:-1x00000000000000000000AA}" >> .env
           if [ -n "${MAIL_USERNAME}" ]; then
@@ -70,6 +82,19 @@ jobs:
               echo "APP_MAIL_FROM=${MAIL_FROM}" >> .env
             fi
           fi
+          # Configuration des sauvegardes de BDD
+          [ -n "${BREVO_API_KEY}" ] && echo "BREVO_API_KEY=${BREVO_API_KEY}" >> .env
+          [ -n "${NOTIFICATION_EMAIL}" ] && echo "NOTIFICATION_EMAIL=${NOTIFICATION_EMAIL}" >> .env
+          [ -n "${BACKUP_DIR}" ] && echo "BACKUP_DIR=${BACKUP_DIR}" >> .env
+          [ -n "${RETENTION_DAYS}" ] && echo "RETENTION_DAYS=${RETENTION_DAYS}" >> .env
+          if [ "${ENABLE_REMOTE_COPY}" = "true" ]; then
+            echo "ENABLE_REMOTE_COPY=true" >> .env
+            [ -n "${REMOTE_HOST}" ] && echo "REMOTE_HOST=${REMOTE_HOST}" >> .env
+            [ -n "${REMOTE_USER}" ] && echo "REMOTE_USER=${REMOTE_USER}" >> .env
+            [ -n "${REMOTE_PORT}" ] && echo "REMOTE_PORT=${REMOTE_PORT}" >> .env
+            [ -n "${REMOTE_BACKUP_DIR}" ] && echo "REMOTE_BACKUP_DIR=${REMOTE_BACKUP_DIR}" >> .env
+            [ -n "${SSH_KEY_PATH}" ] && echo "SSH_KEY_PATH=${SSH_KEY_PATH}" >> .env
+          fi
           docker compose down app || true
           docker compose up -d --build app
 
@@ -89,3 +114,39 @@ jobs:
           echo "Application failed to start or login page is not accessible."
           docker logs astalange_app
           exit 1
+
+      - name: Deploy Backup Script & Config to DB Server (10.8.0.3)
+        env:
+          DB_SERVER_HOST: ${{ secrets.PROD_DB_HOST }}
+          DB_SERVER_USER: ${{ secrets.PROD_DB_SSH_USER }}
+          REMOTE_PORT: ${{ secrets.REMOTE_PORT }}
+        run: |
+          TARGET_HOST="${DB_SERVER_HOST:-10.8.0.3}"
+          TARGET_USER="${DB_SERVER_USER:-root}"
+          PORT="${REMOTE_PORT:-22}"
+          DEST_DIR="/opt/as-talange"
+
+          docker run --rm --net=host \
+            -v "$(pwd):/workspace" -w /workspace \
+            -v "/root/.ssh:/root/.ssh:ro" \
+            -v "/home/ubuntu/.ssh:/home/ubuntu/.ssh:ro" \
+            -e TARGET_HOST="${TARGET_HOST}" \
+            -e TARGET_USER="${TARGET_USER}" \
+            -e PORT="${PORT}" \
+            -e DEST_DIR="${DEST_DIR}" \
+            alpine:latest sh -c '
+              apk add --no-cache openssh-client rsync && \
+              KEY_ARG="" && \
+              for k in /root/.ssh/gitea_ci_key /home/ubuntu/.ssh/gitea_ci_key /root/.ssh/id_ed25519 /root/.ssh/id_rsa; do \
+                if [ -f "$k" ]; then \
+                  KEY_ARG="-i $k -o IdentitiesOnly=yes" && break ; \
+                fi ; \
+              done && \
+              SSH_CMD="ssh -p ${PORT} ${KEY_ARG} -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -o ConnectTimeout=15" && \
+              echo "Deploying backup script and .env to DB Server (${TARGET_USER}@${TARGET_HOST}:${DEST_DIR})..." && \
+              $SSH_CMD "${TARGET_USER}@${TARGET_HOST}" "mkdir -p ${DEST_DIR}/scripts" && \
+              rsync -avz -e "$SSH_CMD" scripts/backup_db.sh "${TARGET_USER}@${TARGET_HOST}:${DEST_DIR}/scripts/" && \
+              rsync -avz -e "$SSH_CMD" .env "${TARGET_USER}@${TARGET_HOST}:${DEST_DIR}/.env" && \
+              $SSH_CMD "${TARGET_USER}@${TARGET_HOST}" "chmod +x ${DEST_DIR}/scripts/backup_db.sh" && \
+              echo "Backup script and .env deployed successfully to ${DEST_DIR} on DB Server!"
+            '

as-talange-common/pom.xml

@@ -5,7 +5,7 @@
     <parent>
         <artifactId>as-talange-parent</artifactId>
         <groupId>com.astalange</groupId>
-        <version>1.8-SNAPSHOT</version>
+        <version>1.9-SNAPSHOT</version>
     </parent>
     <modelVersion>4.0.0</modelVersion>
 

as-talange-core/pom.xml

@@ -5,7 +5,7 @@
     <parent>
         <artifactId>as-talange-parent</artifactId>
         <groupId>com.astalange</groupId>
-        <version>1.8-SNAPSHOT</version>
+        <version>1.9-SNAPSHOT</version>
     </parent>
     <modelVersion>4.0.0</modelVersion>
 

as-talange-web/pom.xml

@@ -5,7 +5,7 @@
     <parent>
         <artifactId>as-talange-parent</artifactId>
         <groupId>com.astalange</groupId>
-        <version>1.8-SNAPSHOT</version>
+        <version>1.9-SNAPSHOT</version>
     </parent>
     <modelVersion>4.0.0</modelVersion>
 

pom.xml

@@ -13,7 +13,7 @@
 
     <groupId>com.astalange</groupId>
     <artifactId>as-talange-parent</artifactId>
-    <version>1.8-SNAPSHOT</version>
+    <version>1.9-SNAPSHOT</version>
     <packaging>pom</packaging>
 
     <name>as-talange-parent</name>

scripts/backup_db.sh

@@ -0,0 +1,259 @@
+#!/usr/bin/env bash
+# ==============================================================================
+# Script de sauvegarde automatisée de la base de données PostgreSQL AS Talange
+# Exécution : Sur le serveur distant / interne hébergeant la base de données
+# Rétention : 7 jours glissants (en local et sur le serveur de stockage VPS)
+# Transfert : Copie sécurisée via SCP/SSH vers le serveur VPS (optionnel)
+# Notification : Email de bilan (Succès/Échec + Logs) via l'API Brevo
+# ==============================================================================
+
+set -u
+
+# Localisation du répertoire du script et du projet
+SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
+PROJECT_DIR="$(cd "${SCRIPT_DIR}/.." && pwd)"
+
+# Chargement du fichier .env si présent dans le projet
+if [ -f "${PROJECT_DIR}/.env" ]; then
+    set -a
+    source "${PROJECT_DIR}/.env"
+    set +a
+fi
+
+# Variables de configuration (avec valeurs par défaut)
+BACKUP_DIR="${BACKUP_DIR:-${PROJECT_DIR}/backups}"
+RETENTION_DAYS="${RETENTION_DAYS:-7}"
+
+DB_NAME="${POSTGRES_DB:-astalange}"
+DB_USER="${POSTGRES_USER:-myuser}"
+DB_PASSWORD="${POSTGRES_PASSWORD:-mypassword}"
+DB_HOST="${POSTGRES_HOST:-localhost}"
+DB_PORT="${POSTGRES_PORT:-5432}"
+CONTAINER_NAME="${DOCKER_CONTAINER_NAME:-astalange_db}"
+
+# Configuration du transfert distant vers le VPS (optionnel)
+ENABLE_REMOTE_COPY="${ENABLE_REMOTE_COPY:-false}"
+REMOTE_HOST="${REMOTE_HOST:-}"
+REMOTE_USER="${REMOTE_USER:-root}"
+REMOTE_PORT="${REMOTE_PORT:-22}"
+REMOTE_BACKUP_DIR="${REMOTE_BACKUP_DIR:-/var/backups/as-talange}"
+SSH_KEY_PATH="${SSH_KEY_PATH:-${HOME}/.ssh/id_rsa}"
+
+# Configuration Brevo
+BREVO_API_KEY="${BREVO_API_KEY:-}"
+BREVO_SENDER_EMAIL="${APP_MAIL_FROM:-astalange1933@gmail.com}"
+BREVO_SENDER_NAME="${BREVO_SENDER_NAME:-AS Talange Backup}"
+RECIPIENT_EMAIL="${NOTIFICATION_EMAIL:-astalange1933@gmail.com}"
+
+# Fichier journal temporaire pour cette exécution
+TIMESTAMP=$(date +"%Y-%m-%d_%H%M%S")
+DATE_HUMAN=$(date +"%d/%m/%Y à %H:%M:%S")
+LOG_FILE=$(mktemp /tmp/astalange_backup_${TIMESTAMP}_XXXXXX.log)
+DUMP_FILENAME="astalange_db_backup_${TIMESTAMP}.sql.gz"
+DUMP_PATH="${BACKUP_DIR}/${DUMP_FILENAME}"
+
+STATUS="SUCCESS"
+DUMP_SIZE="0 KB"
+
+log() {
+    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
+}
+
+log "======================================================================"
+log " Démarrage du script de sauvegarde AS Talange (Serveur BDD)"
+log "======================================================================"
+log "Date d'exécution : ${DATE_HUMAN}"
+log "Dossier local de sauvegarde : ${BACKUP_DIR}"
+log "Fichier cible : ${DUMP_FILENAME}"
+log "Rétention : ${RETENTION_DAYS} jours"
+
+# 1. Création du répertoire de sauvegarde local si nécessaire
+if ! mkdir -p "$BACKUP_DIR" >> "$LOG_FILE" 2>&1; then
+    log "ERREUR: Impossible de créer le dossier de sauvegarde local ${BACKUP_DIR}"
+    STATUS="FAILURE"
+fi
+
+# 2. Déroulement du Dump PostgreSQL
+if [ "$STATUS" = "SUCCESS" ]; then
+    log "Démarrage de l'extraction (dump) de la base de données..."
+    
+    # Vérification si le conteneur Docker est actif
+    if command -v docker >/dev/null 2>&1 && docker ps --format '{{.Names}}' | grep -q "^${CONTAINER_NAME}$"; then
+        log "Mode détection: Conteneur Docker '${CONTAINER_NAME}' trouvé. Utilisation de pg_dump via docker exec."
+        docker exec -e PGPASSWORD="${DB_PASSWORD}" "${CONTAINER_NAME}" pg_dump -U "${DB_USER}" -d "${DB_NAME}" | gzip > "$DUMP_PATH" 2>> "$LOG_FILE"
+        DUMP_EXIT_CODE=${PIPESTATUS[0]}
+    else
+        log "Mode détection: Exécution directe de pg_dump (hôte local)."
+        PGPASSWORD="${DB_PASSWORD}" pg_dump -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" | gzip > "$DUMP_PATH" 2>> "$LOG_FILE"
+        DUMP_EXIT_CODE=${PIPESTATUS[0]}
+    fi
+
+    if [ ${DUMP_EXIT_CODE} -eq 0 ] && [ -s "$DUMP_PATH" ]; then
+        DUMP_SIZE=$(du -h "$DUMP_PATH" | cut -f1)
+        log "✅ Dump généré avec succès ! Taille du fichier : ${DUMP_SIZE}"
+    else
+        log "❌ ERREUR lors de l'extraction de la base de données (code de sortie: ${DUMP_EXIT_CODE})."
+        STATUS="FAILURE"
+        rm -f "$DUMP_PATH"
+    fi
+fi
+
+# 3. Transfert sécurisé RSYNC / SCP vers le serveur VPS (Optionnel)
+if [ "$STATUS" = "SUCCESS" ] && [ "$ENABLE_REMOTE_COPY" = "true" ] && [ -n "$REMOTE_HOST" ]; then
+    log "Transfert du dump vers le serveur VPS distant (${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_BACKUP_DIR})..."
+    
+    SSH_CMD="ssh -i ${SSH_KEY_PATH} -p ${REMOTE_PORT} -o StrictHostKeyChecking=no -o ConnectTimeout=10"
+    
+    # Création du dossier distant si nécessaire
+    $SSH_CMD "${REMOTE_USER}@${REMOTE_HOST}" "mkdir -p ${REMOTE_BACKUP_DIR}" >> "$LOG_FILE" 2>&1
+    
+    TRANSFER_SUCCESS=false
+
+    # Privilégier rsync si disponible
+    if command -v rsync >/dev/null 2>&1; then
+        log "Utilisation de RSYNC pour le transfert..."
+        RSYNC_CMD="rsync -avz -e \"ssh -i ${SSH_KEY_PATH} -p ${REMOTE_PORT} -o StrictHostKeyChecking=no -o ConnectTimeout=10\""
+        if eval $RSYNC_CMD "$DUMP_PATH" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_BACKUP_DIR}/${DUMP_FILENAME}" >> "$LOG_FILE" 2>&1; then
+            TRANSFER_SUCCESS=true
+        fi
+    fi
+
+    # Fallback vers SCP si rsync échoue ou n'est pas installé
+    if [ "$TRANSFER_SUCCESS" = "false" ]; then
+        log "Utilisation de SCP pour le transfert..."
+        SCP_CMD="scp -i ${SSH_KEY_PATH} -P ${REMOTE_PORT} -o StrictHostKeyChecking=no -o ConnectTimeout=10"
+        if $SCP_CMD "$DUMP_PATH" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_BACKUP_DIR}/${DUMP_FILENAME}" >> "$LOG_FILE" 2>&1; then
+            TRANSFER_SUCCESS=true
+        fi
+    fi
+
+    if [ "$TRANSFER_SUCCESS" = "true" ]; then
+        log "✅ Copie distante vers le VPS réussie !"
+        
+        # Nettoyage des sauvegardes distantes sur le VPS (> 7 jours)
+        log "Nettoyage des sauvegardes distantes de plus de ${RETENTION_DAYS} jours sur le VPS..."
+        $SSH_CMD "${REMOTE_USER}@${REMOTE_HOST}" "find ${REMOTE_BACKUP_DIR} -type f -name 'astalange_db_backup_*.sql.gz' -mtime +${RETENTION_DAYS} -delete" >> "$LOG_FILE" 2>&1
+    else
+        log "❌ ERREUR lors du transfert (rsync/scp) vers le serveur VPS."
+        STATUS="FAILURE"
+    fi
+fi
+
+# 4. Nettoyage des anciennes sauvegardes locales (> 7 jours)
+if [ "$STATUS" = "SUCCESS" ]; then
+    log "Nettoyage des sauvegardes locales de plus de ${RETENTION_DAYS} jours..."
+    DELETED_FILES=$(find "$BACKUP_DIR" -type f -name "astalange_db_backup_*.sql.gz" -mtime +${RETENTION_DAYS} -print)
+    if [ -n "$DELETED_FILES" ]; then
+        log "Fichiers locaux supprimés (obsolètes) :"
+        echo "$DELETED_FILES" | tee -a "$LOG_FILE"
+        find "$BACKUP_DIR" -type f -name "astalange_db_backup_*.sql.gz" -mtime +${RETENTION_DAYS} -delete >> "$LOG_FILE" 2>&1
+    else
+        log "Aucune sauvegarde locale obsolète à supprimer."
+    fi
+fi
+
+log "======================================================================"
+log " Fin du traitement de sauvegarde. Statut global : ${STATUS}"
+log "======================================================================"
+
+# 5. Envoi de l'email de rapport via Brevo API
+send_email_notification() {
+    if [ -z "${BREVO_API_KEY}" ]; then
+        log "ATTENTION: BREVO_API_KEY n'est pas définie. Envoi d'email ignoré."
+        return 0
+    fi
+
+    log "Envoi du rapport par email via l'API Brevo à ${RECIPIENT_EMAIL}..."
+
+    if [ "$STATUS" = "SUCCESS" ]; then
+        SUBJECT="[AS Talange] ✅ Backup Base de Données Réussi - $(date +'%d/%m/%Y')"
+        HEADER_BG="#28a745"
+        STATUS_LABEL="SUCCÈS"
+    else
+        SUBJECT="[AS Talange] ❌ ECHEC du Backup Base de Données - $(date +'%d/%m/%Y')"
+        HEADER_BG="#dc3545"
+        STATUS_LABEL="ÉCHEC"
+    fi
+
+    LOG_CONTENT=$(cat "$LOG_FILE")
+    
+    PYTHON_JSON_GEN=$(python3 -c '
+import json, sys
+
+status_label = sys.argv[1]
+header_bg = sys.argv[2]
+timestamp = sys.argv[3]
+dump_name = sys.argv[4]
+dump_size = sys.argv[5]
+log_content = sys.argv[6]
+sender_name = sys.argv[7]
+sender_email = sys.argv[8]
+recipient_email = sys.argv[9]
+subject = sys.argv[10]
+
+html = f"""
+<!DOCTYPE html>
+<html>
+<head><meta charset="utf-8"></head>
+<body style="font-family: Arial, sans-serif; background-color: #f4f6f9; padding: 20px; color: #333;">
+  <div style="max-width: 650px; margin: 0 auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.1);">
+    <div style="background-color: {header_bg}; color: white; padding: 15px 20px; text-align: center;">
+      <h2 style="margin:0;">Sauvegarde Base de Données AS Talange</h2>
+      <p style="margin: 5px 0 0 0; font-weight: bold;">Statut : {status_label}</p>
+    </div>
+    <div style="padding: 20px;">
+      <table style="width: 100%; border-collapse: collapse; margin-bottom: 20px;">
+        <tr><td style="padding: 8px; font-weight: bold; width: 35%;">Date :</td><td style="padding: 8px;">{timestamp}</td></tr>
+        <tr><td style="padding: 8px; font-weight: bold;">Fichier Dump :</td><td style="padding: 8px;"><code>{dump_name}</code></td></tr>
+        <tr><td style="padding: 8px; font-weight: bold;">Taille :</td><td style="padding: 8px;">{dump_size}</td></tr>
+      </table>
+      
+      <h3 style="border-bottom: 2px solid #eee; padding-bottom: 8px; color: #555;">Logs d&apos;exécution :</h3>
+      <pre style="background: #272822; color: #f8f8f2; padding: 15px; border-radius: 5px; font-size: 12px; overflow-x: auto; white-space: pre-wrap;">{log_content}</pre>
+    </div>
+    <div style="background: #e9ecef; padding: 10px 20px; font-size: 12px; text-align: center; color: #6c757d;">
+      Script automatique de sauvegarde AS Talange - Rétention 7 jours
+    </div>
+  </div>
+</body>
+</html>
+"""
+
+payload = {
+    "sender": {"name": sender_name, "email": sender_email},
+    "to": [{"email": recipient_email}],
+    "subject": subject,
+    "htmlContent": html
+}
+
+print(json.dumps(payload))
+' "$STATUS_LABEL" "$HEADER_BG" "$DATE_HUMAN" "$DUMP_FILENAME" "${DUMP_SIZE:-N/A}" "$LOG_CONTENT" "$BREVO_SENDER_NAME" "$BREVO_SENDER_EMAIL" "$RECIPIENT_EMAIL" "$SUBJECT" 2>/dev/null)
+
+    if [ -n "$PYTHON_JSON_GEN" ]; then
+        RESPONSE=$(curl -s -w "\nHTTP_CODE:%{http_code}" -X POST "https://api.brevo.com/v3/smtp/email" \
+            -H "accept: application/json" \
+            -H "api-key: ${BREVO_API_KEY}" \
+            -H "content-type: application/json" \
+            -d "$PYTHON_JSON_GEN")
+
+        HTTP_CODE=$(echo "$RESPONSE" | grep "HTTP_CODE:" | cut -d: -f2)
+        if [ "$HTTP_CODE" = "201" ] || [ "$HTTP_CODE" = "200" ]; then
+            log "✅ Email envoyé avec succès via l'API Brevo !"
+        else
+            log "❌ ERREUR lors de l'envoi de l'email via Brevo (Code HTTP: ${HTTP_CODE}). Réponse: ${RESPONSE}"
+        fi
+    else
+        log "❌ ERREUR: Échec de la génération du payload JSON pour l'email."
+    fi
+}
+
+send_email_notification
+
+# Nettoyage du fichier log temporaire
+rm -f "$LOG_FILE"
+
+if [ "$STATUS" = "FAILURE" ]; then
+    exit 1
+fi
+
+exit 0