63 lines
2.9 KiB
SQL
63 lines
2.9 KiB
SQL
-- ============================================================
|
|
-- Omika Diagnostic Center -- 003_diagnostics_roles_preprod.sql
|
|
-- VARIANTE PRÉPROD LOCALE de 003_diagnostics_roles.sql (T21).
|
|
-- ============================================================
|
|
-- ⚠️ EXCLUSIVEMENT LOCAL / PRÉPROD — jamais en Production.
|
|
--
|
|
-- TRANSFORMATION UNIQUE par rapport à l'original 003 :
|
|
-- « ALTER DATABASE omika_diagnostics OWNER TO ... »
|
|
-- « GRANT CONNECT ON DATABASE omika_diagnostics TO ... »
|
|
-- → base cible renommée « omika_diagnostics_preprod ».
|
|
-- Rôles, privilèges, ownership et idempotence STRICTEMENT identiques.
|
|
-- (L'original code en dur le nom de la base centrale Production
|
|
-- « omika_diagnostics » ; cette variante ne change QUE ce nom.)
|
|
--
|
|
-- Teste réellement, sur une base locale jetable, l'exécutabilité des
|
|
-- rôles/grants 003 : création de rôles cluster-wide, ownership, REVOKE
|
|
-- public, GRANT worker/web. Aucun mot de passe (posé manuellement via
|
|
-- \password, jamais dans ce fichier).
|
|
-- ============================================================
|
|
|
|
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
|
|
$$;
|
|
|
|
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;
|
|
|
|
ALTER DATABASE omika_diagnostics_preprod 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;
|
|
|
|
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
|
|
|
|
GRANT CONNECT ON DATABASE omika_diagnostics_preprod TO omika_diagnostics_worker, omika_diagnostics_web;
|
|
|
|
GRANT USAGE ON SCHEMA public TO omika_diagnostics_worker, omika_diagnostics_web;
|
|
|
|
GRANT INSERT ON TABLE diagnostic_event TO omika_diagnostics_worker;
|
|
GRANT USAGE ON SEQUENCE diagnostic_event_id_seq TO omika_diagnostics_worker;
|
|
|
|
GRANT SELECT ON TABLE diagnostic_event TO omika_diagnostics_web;
|
|
|
|
-- ============================================================
|
|
-- END 003 (variante preprod). Les mots de passe des rôles LOGIN
|
|
-- (worker/web) sont posés manuellement via \password si nécessaire.
|
|
-- ============================================================
|