PD-15 — Créer schéma DB users avec RLS¶

Références¶

• EPIC : PD-186 — BACKEND-CORE
• JIRA : PD-15
• Dépendances liées : PD-24/PD-25 (SRP-6a), PD-16 (documents)

Objectif¶

Créer la table vault_secure.users comme fondation du modèle multi-compte ProbatioVault, avec :

• Isolation stricte par utilisateur via RLS (Row-Level Security)
• Compatibilité SRP-6a : stockage serveur uniquement du verifier SRP (password_hash) — jamais le mot de passe
• Gouvernance minimale (plan d'abonnement, timestamps)
• Mécanisme standard d'injection de contexte : app.current_user_id

Contexte¶

ProbatioVault impose un backend conforme à :

• Zero-Knowledge : aucune donnée sensible en clair et aucun mot de passe stocké côté serveur
• Séparation logique stricte : schéma vault_secure
• RLS : un utilisateur ne peut lire/modifier que sa ligne
• SRP-6a : le backend stocke uniquement salt + verifier (ici password_hash = verifier), jamais un hash classique de mot de passe

vault_secure.users est la base de toute la gouvernance d'accès, et doit rester simple, robuste, auditable, et sécurisée.

Périmètre¶

Inclus¶

• Création du schéma vault_secure (si absent)
• Création table vault_secure.users
• Contraintes d'intégrité (unicité email, plan, non-null)
• Indexation pour lookup SRP (email)
• Trigger updated_at
• Activation RLS + policies SELECT/UPDATE/DELETE
• Convention applicative : SET app.current_user_id = '<uuid>'
• Tests sécurité (RLS, intégrité, règles)
• Documentation : /docs/db/vault_secure/users.md

Exclu (hors périmètre)¶

• Implémentation SRP-6a phase ½ (PD-24/PD-25)
• Gestion des documents (PD-16)
• Audit logs signés HSM (PD-37)

Modèle de données¶

Table : vault_secure.users¶

• id : UUID (PK) — identifiant unique utilisateur
• email : TEXT — unique, non nul — identifiant de connexion (SRP init)
• password_hash : BYTEA — non nul — verifier SRP-6a (pas un hash de mot de passe classique)
• plan : TEXT — non nul — valeurs autorisées : free | premium | business (défaut free)
• created_at : TIMESTAMPTZ — défaut now()
• updated_at : TIMESTAMPTZ — défaut now()

Contraintes¶

• email unique
• password_hash non nul
• plan ∈ {free, premium, business}
• id immuable (par convention API + blocage logique côté service)

Index¶

• Index de lookup sur email (SRP init)
• Contrainte UNIQUE sur email (détection doublons)

RLS (Row-Level Security)¶

Convention applicative¶

Avant toute requête applicative (hors endpoints publics d'inscription), l'application positionne :

• app.current_user_id = '<uuid>'

Règles d'accès¶

• SELECT : un utilisateur ne peut lire que sa propre ligne
• UPDATE : un utilisateur ne peut modifier que sa propre ligne
• DELETE : interdit pour tous les utilisateurs standards ; réservé à un rôle admin/probatoire (ou à une procédure contrôlée)
• INSERT : effectué via backend (endpoint register) — modèle recommandé :
• l'insertion n'est pas faite "au nom" de l'utilisateur authentifié (il ne l'est pas encore),
• elle doit être contrôlée par un rôle serveur dédié (ex. role_auth) et par la logique applicative.

Décision : pas de policy RLS "INSERT utilisateur". Les créations de compte passent via un rôle/service d'auth, qui applique ses propres validations.

Règles API / Backend¶

Création de compte¶

• password_hash = verifier SRP (généré côté client)
• aucun mot de passe ne transite ni n'est stocké
• plan = free par défaut (sauf logique métier)
• email normalisé (minimisation des variantes, ex. trim + lowercase)

Connexion (SRP init)¶

• lookup rapide par email (index requis)
• extraction des paramètres SRP stockés (verifier + autres champs associés dans les US SRP)

Mise à jour profil¶

• id non modifiable
• email reste unique
• plan modifiable uniquement via logique de facturation (pas en libre-service)

Logs¶

• interdiction stricte de logguer password_hash
• aucune donnée SRP sensible (x, A, a, M1, M2, etc.) stockée ni logguée

Diagrammes¶

Diagramme d'états — Cycle de vie utilisateur¶

Le compte utilisateur traverse plusieurs états, de la création à la suppression éventuelle par un administrateur. L'isolation RLS (AC3) et l'interdiction de suppression standard (AC6) contraignent les transitions.

stateDiagram-v2
    [*] --> pending : INSERT via role_auth\n(endpoint register)
    pending --> active : email confirmé\n(hors périmètre PD-15)
    active --> active : UPDATE profil\n(RLS — AC3 : propre ligne uniquement)
    active --> deleted : DELETE par rôle admin\n(AC6 : user standard interdit)
    pending --> deleted : DELETE par rôle admin\n(AC6 : user standard interdit)
    deleted --> [*]

    note right of active
        RLS SELECT/UPDATE : id = app.current_user_id
        email unique (AC4)
        password_hash = verifier SRP (AC2)
    end note

    note right of deleted
        Suppression réservée rôle admin
        ou procédure contrôlée (AC6)
    end note

Diagramme de séquence — Création de compte (SRP-6a)¶

Le flux de création implique le client, le backend (rôle role_auth) et PostgreSQL avec injection du contexte RLS. Aucun mot de passe ne transite ni n'est stocké (AC2/AC5 — Zero-Knowledge).

sequenceDiagram
    participant C as Client
    participant B as Backend (role_auth)
    participant DB as PostgreSQL (vault_secure)

    C->>C : Générer verifier SRP-6a localement
    C->>B : POST /register {email, verifier}
    B->>B : Normaliser email (trim + lowercase)
    B->>DB : INSERT INTO vault_secure.users\n(id, email, password_hash, plan='free')

    alt Email déjà existant
        DB-->>B : UNIQUE violation (AC4)
        B-->>C : 409 Conflict
    else Succès
        DB-->>B : Row inserted
        B-->>C : 201 Created {id}
    end

    Note over C,DB : password_hash = verifier SRP uniquement (AC2)\nAucun mot de passe stocké (AC5)

Diagramme de séquence — Requête avec isolation RLS¶

Toute requête applicative post-authentification injecte le contexte RLS via app.current_user_id. L'isolation stricte garantit qu'un utilisateur ne peut accéder qu'à sa propre ligne (AC3).

sequenceDiagram
    participant C as Client
    participant B as Backend
    participant DB as PostgreSQL (vault_secure)

    C->>B : GET /users/me (JWT)
    B->>DB : SET app.current_user_id = '<user_uuid>'
    B->>DB : SELECT * FROM vault_secure.users\nWHERE id = app.current_user_id

    DB->>DB : RLS policy évalue :\nid = current_setting('app.current_user_id')

    alt Ligne propre (AC3 — OK)
        DB-->>B : Row retournée
        B-->>C : 200 {id, email, plan, created_at, updated_at}
    else Tentative accès autre utilisateur (AC3 — DENIED)
        DB-->>B : 0 rows (filtrage RLS silencieux)
        B-->>C : 404 Not Found
    end

    Note over B,DB : password_hash JAMAIS exposé\nni loggué (Zero-Knowledge)

Critères d'acceptation¶

• AC1 — Table créée conformément au schéma
• colonnes, types, contraintes, indexes
• trigger updated_at opérationnel
• AC2 — Confidentialité Zero-Knowledge
• password_hash = verifier SRP uniquement
• aucun mot de passe / hash classique / clé crypto stockés
• AC3 — RLS strictement isolant
• user A ne voit/modifie que sa ligne
• tentative d'accès à user B rejetée
• AC4 — Unicité email
• INSERT email existant → erreur
• UPDATE vers email existant → erreur
• AC5 — Compatibilité SRP
• lookup email rapide (index)
• aucune donnée SRP sensible stockée
• AC6 — Sécurité
• DELETE impossible pour user standard
• suppression possible uniquement via rôle admin/procédure contrôlée

Tests d'acceptation¶

• TA-1 — Création utilisateur
• création via API : entrée correcte, plan=free, password_hash présent
• TA-2 — RLS lecture
• user A lit sa ligne : OK
• user A lit user B : DENIED
• TA-3 — RLS update
• user A modifie son email : OK
• user A tente de modifier user B : DENIED
• user A tente de changer id : rejet (API + DB si garde-fou)
• TA-4 — Duplicate email
• email X déjà pris → violation unicité sur INSERT/UPDATE
• TA-5 — Conformité password_hash
• la valeur stockée correspond au verifier SRP envoyé par le client
• TA-6 — Delete utilisateur
• user standard : DELETE DENIED
• rôle admin/probatoire : DELETE OK (si activé)

Livrables¶

• Migration SQL complète (schéma + table + contraintes + indexes + trigger + RLS + policies)
• Mise en place du "RLS plumbing" applicatif (app.current_user_id)
• Tests SQL + tests API couvrant : RLS, intégrité, unicité, non-régression
• Documentation : /docs/db/vault_secure/users.md