-- ============================================================
-- Omika Diagnostic Center -- 003_diagnostics_roles.sql (Phase 5B2B.3)
-- PostgreSQL hardening : least privilege (Worker / Web / Owner).
-- ============================================================
-- TO RUN ON SRV-OMIKA as PostgreSQL superuser (e.g. psql -U postgres),
-- BEFORE Phase 5C. DO NOT run from the development PC.
--
-- Guarantees :
--   - PostgreSQL 18 compatible.
--   - Idempotent : re-running is a no-op.
--   - Non destructive : NO DROP, no role/object/data removal.
--   - NO password, NO secret (LOGIN role passwords are set manually via
--     \password, never in this file).
--
-- Role model :
--   omika_diagnostics_owner  NOLOGIN : owner of DB/schema/tables/sequences/indexes.
--   omika_diagnostics_worker LOGIN   : Worker runtime (JSONL import).
--   omika_diagnostics_web    LOGIN   : Web runtime (read only).
--   omika_diagnostics_app    (existing) : KEPT (controlled transition),
--     NOT disabled here.
--
-- Server apply order : 001 (schema) -> 002 (actor_key) -> 003 (roles).
-- ============================================================

-- ------------------------------------------------------------
-- 1. Create roles if absent (NO password here).
-- ------------------------------------------------------------
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'omika_diagnostics_owner') THEN
        CREATE ROLE omika_diagnostics_owner;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'omika_diagnostics_worker') THEN
        CREATE ROLE omika_diagnostics_worker;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'omika_diagnostics_web') THEN
        CREATE ROLE omika_diagnostics_web;
    END IF;
END
$$;

-- ------------------------------------------------------------
-- 2. Secure attributes (idempotent).
--    All: NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS.
-- ------------------------------------------------------------
ALTER ROLE omika_diagnostics_owner  NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS NOLOGIN;
ALTER ROLE omika_diagnostics_worker NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS LOGIN;
ALTER ROLE omika_diagnostics_web    NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS LOGIN;

-- ------------------------------------------------------------
-- 3. Ownership transfer (explicit -- no global REASSIGN OWNED).
--    Only Omika Diagnostics objects are targeted.
-- ------------------------------------------------------------
ALTER DATABASE omika_diagnostics OWNER TO omika_diagnostics_owner;
ALTER SCHEMA public OWNER TO omika_diagnostics_owner;

ALTER TABLE diagnostic_event OWNER TO omika_diagnostics_owner;
ALTER TABLE telemetry_metric_hourly OWNER TO omika_diagnostics_owner;

ALTER SEQUENCE diagnostic_event_id_seq OWNER TO omika_diagnostics_owner;
ALTER SEQUENCE telemetry_metric_hourly_id_seq OWNER TO omika_diagnostics_owner;

-- PostgreSQL 18:
-- Index ownership follows table ownership.
-- No ALTER INDEX OWNER is required.
-- (Sequences are NOT auto-transferred by ALTER TABLE: the ALTER SEQUENCE
--  OWNER commands above are therefore kept and required.)

-- ------------------------------------------------------------
-- 4. Schema "public" hardening.
--    Decision : YES, REVOKE CREATE -- the database is dedicated to the
--    Diagnostic Center; only the owner/admin should create objects.
--    (PostgreSQL 15+ already revokes it for new databases; this is an
--    idempotent belt-and-suspenders.)
-- ------------------------------------------------------------
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- ------------------------------------------------------------
-- 5. MINIMUM runtime privileges (from real code audit).
--
--    WORKER (omika_diagnostics_worker):
--      * diagnostic_event : INSERT only
--        (INSERT ... ON CONFLICT (event_id) DO NOTHING -- dedup).
--      * sequence diagnostic_event_id_seq : USAGE (id BIGSERIAL).
--      * NO SELECT/UPDATE/DELETE (60-day retention is NOT implemented;
--        telemetry_metric_hourly upsert is registered but has NO caller).
--
--    WEB (omika_diagnostics_web): SELECT ONLY on diagnostic_event
--      (QueryAsync count+page, GetByTraceId, GetByOperationId, IsAvailableAsync).
--
--    NO privilege granted on telemetry_metric_hourly while no code uses it
--    (least privilege).
-- ------------------------------------------------------------

-- Explicit connection intent (PUBLIC keeps default CONNECT; documented only,
-- not hardened beyond requested scope).
GRANT CONNECT ON DATABASE omika_diagnostics TO omika_diagnostics_worker, omika_diagnostics_web;

GRANT USAGE ON SCHEMA public TO omika_diagnostics_worker, omika_diagnostics_web;

-- Worker : JSONL import -> diagnostic_event.
GRANT INSERT ON TABLE diagnostic_event TO omika_diagnostics_worker;
GRANT USAGE ON SEQUENCE diagnostic_event_id_seq TO omika_diagnostics_worker;

-- Web : read only.
GRANT SELECT ON TABLE diagnostic_event TO omika_diagnostics_web;

-- ------------------------------------------------------------
-- 6. Legacy role omika_diagnostics_app : KEPT (no DROP, no NOLOGIN here).
--    After transfer it owns no Diagnostics object and has no object privilege;
--    disabling it (ALTER ROLE ... NOLOGIN) is an EXPLICIT administrator action
--    AFTER validating the new roles.
-- ------------------------------------------------------------

-- ============================================================
-- END 003. Manual next steps (outside this script):
--   1. Set passwords: psql -> \password omika_diagnostics_worker
--      then \password omika_diagnostics_web (never in script/log/history).
--   2. Point Worker: Username=omika_diagnostics_worker ; Web:
--      Username=omika_diagnostics_web (ConnectionStrings__DiagnosticsDatabase).
--   3. Validate Worker (INSERT) and Web (SELECT), then only after,
--      disable omika_diagnostics_app (ALTER ROLE ... NOLOGIN) if desired.
-- ============================================================