100 lines
5.2 KiB
SQL
100 lines
5.2 KiB
SQL
-- ============================================================
|
|
-- Omika Diagnostic Center -- 010_diagnostics_web_users.sql
|
|
-- T23 : gestion des utilisateurs de Omika.Diagnostics.Web.
|
|
-- ============================================================
|
|
-- TO RUN ON SRV-OMIKA as PostgreSQL superuser (e.g. psql -U postgres -d <base> -f 010_...),
|
|
-- AFTER 003 (rôles owner/worker/web), 008 (diagnostics_clients) and 009 (rôle api + grants clients).
|
|
--
|
|
-- Guarantees :
|
|
-- - PostgreSQL 18 compatible.
|
|
-- - Idempotent : re-running is a no-op (CREATE ... IF NOT EXISTS + GRANT idempotent).
|
|
-- - Non destructive : NO DROP, NO DELETE/TRUNCATE, no data removal.
|
|
-- - NO password, NO secret (no column stores a clear-text password).
|
|
--
|
|
-- Une ligne = une PERSONNE autorisée à ouvrir Omika.Diagnostics.Web
|
|
-- (username + password_hash + rôle). À NE PAS confondre avec
|
|
-- diagnostics_clients (serveurs clients, ClientCode + ApiKey).
|
|
--
|
|
-- username : identifiant unique (unicité insensible à la casse).
|
|
-- password_hash : hash PBKDF2-SHA256 (DiagnosticPasswordHasher). JAMAIS en clair.
|
|
-- role : 'Support' ou 'Admin' (validé par le code, pas de CHECK arbitraire).
|
|
-- enabled : FALSE => l'utilisateur ne peut plus se connecter.
|
|
-- created_at_utc : création du compte.
|
|
-- updated_at_utc : dernière mutation du compte.
|
|
-- password_changed_at_utc : dernier changement de mot de passe.
|
|
-- last_login_at_utc : dernier login réussi ; NULL si jamais.
|
|
--
|
|
-- CHOIX DE TYPE : aligné sur diagnostic_event / diagnostics_clients (TEXT, TIMESTAMPTZ,
|
|
-- BOOLEAN). Aucun VARCHAR, aucun CHECK de longueur (validation portée par le code,
|
|
-- comme 005/007/008). Aucune colonne password/plain_password/clear_password.
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS diagnostics_users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
username TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at_utc TIMESTAMPTZ NOT NULL,
|
|
updated_at_utc TIMESTAMPTZ NOT NULL,
|
|
password_changed_at_utc TIMESTAMPTZ NOT NULL,
|
|
last_login_at_utc TIMESTAMPTZ
|
|
);
|
|
|
|
-- Unicité insensible à la casse (§T23/11) : un index sur LOWER(username) garantit
|
|
-- qu'un même identifiant ne peut exister deux fois quelle que soit la casse.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ux_diagnostics_users_username
|
|
ON diagnostics_users (LOWER(username));
|
|
|
|
-- ------------------------------------------------------------
|
|
-- Ownership : omika_diagnostics_owner (comme 003/009).
|
|
-- ------------------------------------------------------------
|
|
ALTER TABLE diagnostics_users OWNER TO omika_diagnostics_owner;
|
|
ALTER SEQUENCE diagnostics_users_id_seq OWNER TO omika_diagnostics_owner;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- Défense PUBLIC (ceinture + bretelles, idempotent).
|
|
-- ------------------------------------------------------------
|
|
REVOKE ALL ON TABLE diagnostics_users FROM PUBLIC;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- Connexion explicite + schéma pour omika_diagnostics_web
|
|
-- (déjà accordés par 003 ; ré-affirmé idempotent, intention documentée).
|
|
-- ------------------------------------------------------------
|
|
DO $$
|
|
BEGIN
|
|
EXECUTE format('GRANT CONNECT ON DATABASE %I TO omika_diagnostics_web', current_database());
|
|
END
|
|
$$;
|
|
|
|
GRANT USAGE ON SCHEMA public TO omika_diagnostics_web;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- Web (Omika.Diagnostics.Web, admin /Users) : SELECT + INSERT +
|
|
-- UPDATE colonne-ciblé (moindre privilège) + USAGE séquence.
|
|
-- - INSERT : création utilisateur (id BIGSERIAL).
|
|
-- - UPDATE (password_hash, role, enabled, updated_at_utc,
|
|
-- password_changed_at_utc, last_login_at_utc) : reset mot de passe,
|
|
-- changement rôle, activation/désactivation, last login.
|
|
-- - username n'est JAMAIS mis à jour (aucun renommage en V1).
|
|
-- - AUCUN DELETE (aucune suppression physique en V1).
|
|
-- ------------------------------------------------------------
|
|
GRANT SELECT ON TABLE diagnostics_users TO omika_diagnostics_web;
|
|
GRANT INSERT ON TABLE diagnostics_users TO omika_diagnostics_web;
|
|
GRANT UPDATE (password_hash, role, enabled, updated_at_utc, password_changed_at_utc, last_login_at_utc)
|
|
ON TABLE diagnostics_users TO omika_diagnostics_web;
|
|
GRANT USAGE ON SEQUENCE diagnostics_users_id_seq TO omika_diagnostics_web;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- API (omika_diagnostics_api) : AUCUN droit sur diagnostics_users.
|
|
-- Worker (omika_diagnostics_worker) : AUCUN droit sur diagnostics_users.
|
|
-- Aucun GRANT accordé ; les rôles n'ont aucun privilège par défaut.
|
|
-- (Aucun REVOKE nécessaire : aucun privilège n'a été accordé.)
|
|
-- ------------------------------------------------------------
|
|
|
|
-- ============================================================
|
|
-- END 010. Ordre serveur : 001 -> 002 -> 003 -> 004 -> 005 -> 006
|
|
-- -> 007 -> 008 -> 009 -> 010. Mots de passe posés manuellement
|
|
-- (\password), jamais dans un script/log.
|
|
-- ============================================================
|