Projet

Général

Profil

Télécharger (69,7 ko)

Anomalie #312 » a_executer.sql

Script de correction du mot de passe de contrôle - Leslie Lemaire, 27/08/2020 19:38

 

--------------------------------------------
------ 3 - CREATION DES EVENT TRIGGERS ------
--------------------------------------------

------ 3.1 - EVENT TRIGGER SUR ALTER SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_on_alter_schema()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_alter_schema() RETURNS event_trigger
LANGUAGE plpgsql
AS $BODY$
/* OBJET : Fonction exécutée par l'event trigger asgard_on_alter_schema qui
répercute dans la table z_asgard_admin.gestion_schema (via la vue
z_asgard.gestion_schema_etr) les modifications de noms
et propriétaires des schémas réalisées par des commandes
ALTER SCHEMA directes.
DECLENCHEMENT : ON DDL COMMAND END.
CONDITION : WHEN TAG IN ('ALTER SCHEMA') */
DECLARE
obj record ;
e_mssg text ;
e_hint text ;
e_detl text ;
BEGIN
------ CONTROLES DES PRIVILEGES ------
IF NOT has_schema_privilege('z_asgard', 'USAGE')
THEN
RAISE EXCEPTION 'EAS1. Echec.'
USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;
END IF ;
IF NOT has_table_privilege('z_asgard.gestion_schema_etr', 'UPDATE')
OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'SELECT')
THEN
RAISE EXCEPTION 'EAS2. Echec.'
USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;
END IF ;


FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
WHERE object_type = 'schema'
LOOP
------ RENAME ------
UPDATE z_asgard.gestion_schema_etr
SET nom_schema = replace(obj.object_identity, '"', ''),
ctrl = ARRAY['RENAME', 'x7-A;#rzo']
WHERE oid_schema = obj.objid
AND NOT quote_ident(nom_schema) = obj.object_identity ;
IF FOUND
THEN
RAISE NOTICE '... Le nom du schéma % a été mis à jour dans la table de gestion.', replace(obj.object_identity, '"', '') ;
END IF ;

------ OWNER TO ------
UPDATE z_asgard.gestion_schema_etr
SET (producteur, oid_producteur, ctrl) = (
SELECT
replace(nspowner::regrole::text, '"', ''),
nspowner,
ARRAY['OWNER', 'x7-A;#rzo']
FROM pg_catalog.pg_namespace
WHERE obj.objid = pg_namespace.oid
)
WHERE oid_schema = obj.objid
AND NOT oid_producteur = (
SELECT nspowner
FROM pg_catalog.pg_namespace
WHERE obj.objid = pg_namespace.oid
) ;
IF FOUND
THEN
RAISE NOTICE '... Le producteur du schéma % a été mis à jour dans la table de gestion.', replace(obj.object_identity, '"', '') ;
END IF ;

END LOOP ;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
e_hint = PG_EXCEPTION_HINT,
e_detl = PG_EXCEPTION_DETAIL ;
RAISE EXCEPTION 'EAS0. Opération annulée. Anomalie lors de l''enregistrement dans la table de gestion.'
USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' || e_detl END,
HINT = e_hint ;
END
$BODY$ ;


------ 3.2 - EVENT TRIGGER SUR CREATE SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_on_create_schema()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_create_schema() RETURNS event_trigger
LANGUAGE plpgsql
AS $BODY$
/* OBJET : Fonction exécutée par l'event trigger asgard_on_create_schema qui
répercute dans la table z_asgard_admin.gestion_schema (via la vue
z_asgard.gestion_schema_etr) les créations de schémas
réalisées par des commandes CREATE SCHEMA directes.
DECLENCHEMENT : ON DDL COMMAND END.
CONDITION : WHEN TAG IN ('CREATE SCHEMA') */
DECLARE
obj record ;
e_mssg text ;
e_hint text ;
e_detl text ;
BEGIN
------ CONTROLES DES PRIVILEGES ------
IF NOT has_schema_privilege('z_asgard', 'USAGE')
THEN
RAISE EXCEPTION 'ECS1. Echec.'
USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;
END IF ;
IF NOT has_table_privilege('z_asgard.gestion_schema_etr', 'UPDATE')
OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'INSERT')
OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'SELECT')
THEN
RAISE EXCEPTION 'ECS2. Echec.'
USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;
END IF ;


FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
WHERE object_type = 'schema'
LOOP
------ SCHEMA PRE-ENREGISTRE DANS GESTION_SCHEMA ------
UPDATE z_asgard.gestion_schema_etr
SET (oid_schema, producteur, oid_producteur, creation, ctrl) = (
SELECT
obj.objid,
replace(nspowner::regrole::text, '"', ''),
nspowner,
true,
ARRAY['CREATE', 'x7-A;#rzo']
FROM pg_catalog.pg_namespace
WHERE obj.objid = pg_namespace.oid
)
WHERE quote_ident(nom_schema) = obj.object_identity
AND NOT creation ; -- creation vaut true si et seulement si la création a été initiée via la table
-- de gestion dans ce cas, il n'est pas nécessaire de réintervenir dessus
IF FOUND
THEN
RAISE NOTICE '... Le schéma % apparaît désormais comme "créé" dans la table de gestion.', replace(obj.object_identity, '"', '') ;

------ SCHEMA NON REPERTORIE DANS GESTION_SCHEMA ------
ELSIF NOT obj.object_identity IN (SELECT quote_ident(nom_schema) FROM z_asgard.gestion_schema_etr)
THEN
INSERT INTO z_asgard.gestion_schema_etr (oid_schema, nom_schema, producteur, oid_producteur, creation, ctrl)(
SELECT
obj.objid,
replace(obj.object_identity, '"', ''),
replace(nspowner::regrole::text, '"', ''),
nspowner,
true,
ARRAY['CREATE', 'x7-A;#rzo']
FROM pg_catalog.pg_namespace
WHERE obj.objid = pg_namespace.oid
) ;
RAISE NOTICE '... Le schéma % a été enregistré dans la table de gestion.', replace(obj.object_identity, '"', '') ;
END IF ;
END LOOP ;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
e_hint = PG_EXCEPTION_HINT,
e_detl = PG_EXCEPTION_DETAIL ;
RAISE EXCEPTION 'ECS0. Opération annulée. Anomalie lors de l''enregistrement dans la table de gestion.'
USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' || e_detl END,
HINT = e_hint ;
END
$BODY$ ;



------ 3.3 - EVENT TRIGGER SUR DROP SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_on_drop_schema()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_drop_schema() RETURNS event_trigger
LANGUAGE plpgsql
AS $BODY$
/* OBJET : Fonction exécutée par l'event trigger asgard_on_drop_schema qui
répercute dans la table z_asgard_admin.gestion_schema (via la vue
z_asgard.gestion_schema_etr) les suppressions de schémas
réalisées par des commandes DROP SCHEMA directes.
DECLENCHEMENT : ON SQL DROP.
CONDITION : WHEN TAG IN ('DROP SCHEMA') */
DECLARE
obj record ;
e_mssg text ;
e_hint text ;
e_detl text ;
BEGIN
------ CONTROLES DES PRIVILEGES ------
IF NOT has_schema_privilege('z_asgard', 'USAGE')
THEN
RAISE EXCEPTION 'EDS1. Echec.'
USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;
END IF ;
IF NOT has_table_privilege('z_asgard.gestion_schema_etr', 'UPDATE')
OR NOT has_table_privilege('z_asgard.gestion_schema_etr', 'SELECT')
THEN
RAISE EXCEPTION 'EDS2. Echec.'
USING HINT = 'Vous devez être membre du groupe éditeur du schéma z_asgard pour réaliser cette opération.' ;
END IF ;

FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
WHERE object_type = 'schema'
LOOP
------ ENREGISTREMENT DE LA SUPPRESSION ------
UPDATE z_asgard.gestion_schema_etr
SET (creation, oid_schema, ctrl) = (False, NULL, ARRAY['DROP', 'x7-A;#rzo'])
WHERE quote_ident(nom_schema) = obj.object_identity ;
IF FOUND THEN
RAISE NOTICE '... La suppression du schéma % a été enregistrée dans la table de gestion (creation = False).', replace(obj.object_identity, '"', '');
END IF ;
END LOOP ;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
e_hint = PG_EXCEPTION_HINT,
e_detl = PG_EXCEPTION_DETAIL ;
RAISE EXCEPTION 'EDS0. Opération annulée. Anomalie lors de l''enregistrement dans la table de gestion.'
USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' || e_detl END,
HINT = e_hint ;
END
$BODY$;



---------------------------------------
------ 4 - FONCTIONS UTILITAIRES ------
---------------------------------------


------ 4.6 - DEREFERENCEMENT D'UN SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_sortie_gestion_schema(text)

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_sortie_gestion_schema(n_schema text)
RETURNS text
LANGUAGE plpgsql
AS $_$
/* OBJET : Cette fonction permet de supprimer de la table de gestion un
schéma existant (qui échappera alors aux mécanismes de
gestion des droits), en outrepassant les règles qui veulent
que seules les lignes avec creation valant false puisse
être ciblées par des DELETE et que creation ne puisse être
mis à false si le schéma existe.
ARGUMENTS :
- n_schema : nom d'un schéma présumé référencé dans le champ
nom_schema de la table de gestion (sinon la fonction n'aura
pas d'effet).
SORTIE : '__ DEREFERENCEMENT REUSSI.' si la requête s'est exécutée normalement. */
DECLARE
e_mssg text ;
e_detl text ;
e_hint text ;
BEGIN

UPDATE z_asgard.gestion_schema_etr
SET ctrl = ARRAY['EXIT', 'x7-A;#rzo']
WHERE nom_schema = n_schema ;
DELETE FROM z_asgard.gestion_schema_etr
WHERE nom_schema = n_schema ;

RETURN '__ DEREFERENCEMENT REUSSI.' ;

EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
e_hint = PG_EXCEPTION_HINT,
e_detl = PG_EXCEPTION_DETAIL ;
RAISE EXCEPTION 'FSG0. Opération annulée. Anomalie lors du déréférencement de la table de gestion.'
USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' || e_detl END,
HINT = e_hint ;

END
$_$;




---------------------------------------------
------ 5 - TRIGGERS SUR GESTION_SCHEMA ------
---------------------------------------------

------ 5.1 - TRIGGER BEFORE ------

-- FUNCTION: z_asgard_admin.asgard_on_modify_gestion_schema_before()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_before() RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
/* OBJET : Fonction exécutée par le trigger asgard_on_modify_gestion_schema_before,
qui valide les informations saisies dans la table de gestion.
CIBLES : z_asgard_admin.gestion_schema.
PORTEE : FOR EACH ROW.
DECLENCHEMENT : BEFORE INSERT, UPDATE, DELETE.*/
DECLARE
n_role text ;
BEGIN
------ SAISIE PAR UN UTILISATEUR NON HABILITE ------
IF TG_OP = 'INSERT' AND NOT has_database_privilege(current_database(), 'CREATE')
-- même si creation vaut faux, seul un rôle habilité à créer des
-- schéma peut ajouter des lignes dans la table de gestion
THEN
RAISE EXCEPTION 'TB1. Vous devez être habilité à créer des schémas pour réaliser cette opération.' ;
END IF ;
------ APPLICATION DES VALEURS PAR DEFAUT ------
-- au tout début car de nombreux tests sont faits par la
-- suite sur "NOT NEW.creation"
IF TG_OP IN ('INSERT', 'UPDATE')
THEN
NEW.creation := coalesce(NEW.creation, False) ;
NEW.nomenclature := coalesce(NEW.nomenclature, False) ;
END IF ;
------ EFFACEMENT D'UN ENREGISTREMENT ------
IF TG_OP = 'DELETE'
THEN
-- on n'autorise pas l'effacement si creation vaut True
-- avec une exception pour les commandes envoyées par la fonction
-- de maintenance asgard_sortie_gestion_schema
IF OLD.creation AND (OLD.ctrl[1] IS NULL OR NOT OLD.ctrl[1] = 'EXIT')
THEN
RAISE EXCEPTION 'TB2. Opération interdite (schéma %). L''effacement n''est autorisé que si creation vaut False.', OLD.nom_schema
USING HINT = 'Pour déréférencer un schéma sans le supprimer, vous pouvez utiliser la fonction z_asgard_admin.asgard_sortie_gestion_schema.' ;
END IF;
-- on n'autorise pas l'effacement pour les schémas de la nomenclature
IF OLD.nomenclature
THEN
RAISE EXCEPTION 'TB3. Opération interdite (schéma %). L''effacement n''est pas autorisé pour les schémas de la nomenclature nationale.', OLD.nom_schema
USING HINT = 'Si vous tenez à supprimer définitivement cet enregistrement, basculez préalablement nomenclature sur False.' ;
END IF ;
END IF;

------ DE-CREATION D'UN SCHEMA ------
IF TG_OP = 'UPDATE'
THEN
-- si bloc valait déjà d (schéma "mis à la corbeille")
-- on exécute une commande de suppression du schéma. Toute autre modification sur
-- la ligne est ignorée.
IF OLD.bloc = 'd' AND OLD.creation AND NOT NEW.creation AND NEW.ctrl[2] IS NULL
AND OLD.nom_schema IN (SELECT nspname FROM pg_catalog.pg_namespace)
THEN
EXECUTE 'DROP SCHEMA ' || quote_ident(OLD.nom_schema) || ' CASCADE' ;
RAISE NOTICE '... Le schéma % a été supprimé.', OLD.nom_schema ;
RETURN NULL ;
-- sinon, on n'autorise creation à passer de true à false que si le schéma
-- n'existe plus (permet notamment à l'event trigger qui gère les
-- suppressions de mettre creation à false)
ELSIF OLD.creation and NOT NEW.creation
AND NEW.nom_schema IN (SELECT nspname FROM pg_catalog.pg_namespace)
THEN
RAISE EXCEPTION 'TB4. Opération interdite (schéma %). Le champ creation ne peut passer de Vrai à Faux si le schéma existe.', NEW.nom_schema
USING HINT = 'Si vous supprimez physiquement le schéma avec la commande DROP SCHEMA, creation basculera sur False automatiquement.' ;
END IF ;
END IF ;
IF TG_OP <> 'DELETE'
THEN
------ PROHIBITION DE LA SAISIE MANUELLE DES OID ------
-- vérifié grâce au champ ctrl
IF NEW.ctrl[2] IS NULL
OR NOT array_length(NEW.ctrl, 1) >= 2
OR NEW.ctrl[1] IS NULL
OR NOT NEW.ctrl[1] IN ('CREATE', 'RENAME', 'OWNER', 'DROP', 'SELF', 'EXIT')
OR NOT NEW.ctrl[2] = 'x7-A;#rzo'
-- ctrl NULL ou invalide
THEN

IF NEW.ctrl[1] = 'EXIT'
THEN
RAISE EXCEPTION 'TB17. Opération interdite (schéma %).', coalesce(NEW.nom_schema, '?')
USING HINT = 'Pour déréférencer un schéma, veuillez utiliser la fonction z_asgard_admin.asgard_sortie_gestion_schema.' ;
END IF ;
-- réinitialisation du champ ctrl, qui peut contenir des informations
-- issues de commandes antérieures (dans ctrl[1])
NEW.ctrl := ARRAY['MANUEL', NULL]::text[] ;
IF TG_OP = 'INSERT' AND (
NEW.oid_producteur IS NOT NULL
OR NEW.oid_lecteur IS NOT NULL
OR NEW.oid_editeur IS NOT NULL
OR NEW.oid_schema IS NOT NULL
)
-- cas d'un INSERT manuel pour lequel des OID ont été saisis
-- on les remet à NULL
THEN
NEW.oid_producteur = NULL ;
NEW.oid_editeur = NULL ;
NEW.oid_lecteur = NULL ;
NEW.oid_schema = NULL ;
ELSIF TG_OP = 'UPDATE'
THEN
IF NOT coalesce(NEW.oid_producteur, -1) = coalesce(OLD.oid_producteur, -1)
OR NOT coalesce(NEW.oid_editeur, -1) = coalesce(OLD.oid_editeur, -1)
OR NOT coalesce(NEW.oid_lecteur, -1) = coalesce(OLD.oid_lecteur, -1)
OR NOT coalesce(NEW.oid_schema, -1) = coalesce(OLD.oid_schema, -1)
-- cas d'un UPDATE avec modification des OID
-- on les remet à OLD
THEN
NEW.oid_producteur = OLD.oid_producteur ;
NEW.oid_editeur = OLD.oid_editeur ;
NEW.oid_lecteur = OLD.oid_lecteur ;
NEW.oid_schema = OLD.oid_schema ;
END IF ;
END IF ;
ELSE
-- suppression du mot de passe de contrôle.
-- ctrl[1] est par contre conservé - il sera utilisé
-- par le trigger AFTER pour connaître l'opération
-- à l'origine de son déclenchement.
NEW.ctrl[2] := NULL ;
END IF ;
------ REQUETES AUTO A IGNORER ------
-- les remontées du trigger AFTER (SELF)
-- sont exclues, car les contraintes ont déjà
-- été validées (et pose problèmes avec les
-- contrôles d'OID sur les UPDATE, car ceux-ci
-- ne seront pas nécessairement déjà remplis) ;
-- les requêtes EXIT de même, car c'est un
-- pré-requis à la suppression qui ne fait
-- que modifier le champ ctrl
IF NEW.ctrl[1] IN ('SELF', 'EXIT')
THEN
-- aucune action
RETURN NEW ;
END IF ;
------ VERROUILLAGE DES CHAMPS LIES A LA NOMENCLATURE ------
-- modifiables uniquement par l'ADL
IF TG_OP = 'UPDATE'
THEN
IF (OLD.nomenclature OR NEW.nomenclature) AND NOT pg_has_role('g_admin', 'MEMBER') AND (
NOT coalesce(OLD.nomenclature, False) = coalesce(NEW.nomenclature, False)
OR NOT coalesce(OLD.niv1, '') = coalesce(NEW.niv1, '')
OR NOT coalesce(OLD.niv1_abr, '') = coalesce(NEW.niv1_abr, '')
OR NOT coalesce(OLD.niv2, '') = coalesce(NEW.niv2, '')
OR NOT coalesce(OLD.niv2_abr, '') = coalesce(NEW.niv2_abr, '')
OR NOT coalesce(OLD.nom_schema, '') = coalesce(NEW.nom_schema, '')
OR NOT coalesce(OLD.bloc, '') = coalesce(NEW.bloc, '')
)
THEN
RAISE EXCEPTION 'TB18. Opération interdite (schéma %).', NEW.nom_schema
USING HINT = 'Seuls les membres de g_admin sont habilités à modifier les champs nomenclature et - pour les schémas de la nomenclature - bloc, niv1, niv1_abr, niv2, niv2_abr et nom_schema.' ;
END IF ;
ELSIF TG_OP = 'INSERT'
THEN
IF NEW.nomenclature AND NOT pg_has_role('g_admin', 'MEMBER')
THEN
RAISE EXCEPTION 'TB19. Opération interdite (schéma %).', NEW.nom_schema
USING HINT = 'Seuls les membres de g_admin sont autorisés à ajouter des schémas à la nomenclature (nomenclature = True).' ;
END IF ;
END IF ;
------ NETTOYAGE DES CHAÎNES VIDES ------
-- si l'utilisateur a entré des chaînes vides on met des NULL
NEW.editeur := nullif(NEW.editeur, '') ;
NEW.lecteur := nullif(NEW.lecteur, '') ;
NEW.bloc := nullif(NEW.bloc, '') ;
NEW.niv1 := nullif(NEW.niv1, '') ;
NEW.niv1_abr := nullif(NEW.niv1_abr, '') ;
NEW.niv2 := nullif(NEW.niv2, '') ;
NEW.niv2_abr := nullif(NEW.niv2_abr, '') ;
NEW.nom_schema := nullif(NEW.nom_schema, '') ;
-- si producteur est vide on met par défaut g_admin
NEW.producteur := coalesce(nullif(NEW.producteur, ''), 'g_admin') ;
------ NETTOYAGE DES CHAMPS OID ------
-- pour les rôles de lecteur et éditeur,
-- si le champ de nom est vidé par l'utilisateur,
-- on vide en conséquence l'OID
IF NEW.editeur IS NULL
THEN
NEW.oid_editeur := NULL ;
END IF ;
IF NEW.lecteur IS NULL
THEN
NEW.oid_lecteur := NULL ;
END IF ;
-- si le schéma n'est pas créé, on s'assure que les champs
-- d'OID restent vides
-- à noter que l'event trigger sur DROP SCHEMA vide
-- déjà le champ oid_schema
IF NOT NEW.creation
THEN
NEW.oid_schema := NULL ;
NEW.oid_lecteur := NULL ;
NEW.oid_editeur := NULL ;
NEW.oid_producteur := NULL ;
END IF ;
------ VALIDITE DES NOMS DE ROLES ------
-- dans le cas d'un schéma pré-existant, on s'assure que les rôles qui
-- ne changent pas sont toujours valides (qu'ils existent et que le nom
-- n'a pas été modifié entre temps)
-- si tel est le cas, on les met à jour et on le note dans
-- ctrl, pour que le trigger AFTER sache qu'il ne s'agit
-- pas réellement de nouveaux rôles sur lesquels les droits
-- devraient être réappliqués
IF TG_OP = 'UPDATE' AND NEW.creation
THEN
-- producteur
IF OLD.creation AND OLD.producteur = NEW.producteur
THEN
SELECT rolname INTO n_role
FROM pg_catalog.pg_roles
WHERE pg_roles.oid = NEW.oid_producteur ;
IF NOT FOUND
-- le rôle producteur n'existe pas
THEN
-- cas invraisemblable, car un rôle ne peut pas être
-- supprimé alors qu'il est propriétaire d'un schéma, et la
-- commande ALTER SCHEMA OWNER TO aurait été interceptée
-- mais, s'il advient, on repart du propriétaire
-- renseigné dans pg_namespace
SELECT replace(nspowner::regrole::text, '"', ''), nspowner
INTO NEW.producteur, NEW.oid_producteur
FROM pg_catalog.pg_namespace
WHERE pg_namespace.oid = NEW.oid_schema ;
RAISE NOTICE '[table de gestion] ANOMALIE. Schéma %. L''OID actuellement renseigné pour le producteur est invalide. Poursuite avec l''OID du propriétaire courant du schéma.', NEW.nom_schema ;
NEW.ctrl := array_append(NEW.ctrl, 'CLEAN producteur') ;
ELSIF NOT n_role = NEW.producteur
-- libellé obsolète du producteur
THEN
NEW.producteur := n_role ;
RAISE NOTICE '[table de gestion] Schéma %. Mise à jour du libellé du rôle producteur, renommé entre temps.', NEW.nom_schema
USING DETAIL = 'Ancien nom "' || OLD.producteur || '", nouveau nom "' || NEW.producteur || '".' ;
NEW.ctrl := array_append(NEW.ctrl, 'CLEAN producteur') ;
END IF ;
END IF ;
-- éditeur
IF OLD.creation AND OLD.editeur = NEW.editeur
AND NOT NEW.editeur = 'public'
THEN
SELECT rolname INTO n_role
FROM pg_catalog.pg_roles
WHERE pg_roles.oid = NEW.oid_editeur ;
IF NOT FOUND
-- le rôle éditeur n'existe pas
THEN
NEW.editeur := NULL ;
NEW.oid_editeur := NULL ;
RAISE NOTICE '[table de gestion] Schéma %. Le rôle éditeur n''existant plus, il est déréférencé.', NEW.nom_schema
USING DETAIL = 'Ancien nom "' || OLD.editeur || '".' ;
NEW.ctrl := array_append(NEW.ctrl, 'CLEAN editeur') ;
ELSIF NOT n_role = NEW.editeur
-- libellé obsolète de l'éditeur
THEN
NEW.editeur := n_role ;
RAISE NOTICE '[table de gestion] Schéma %. Mise à jour du libellé du rôle éditeur, renommé entre temps.', NEW.nom_schema
USING DETAIL = 'Ancien nom "' || OLD.editeur || '", nouveau nom "' || NEW.editeur || '".' ;
NEW.ctrl := array_append(NEW.ctrl, 'CLEAN editeur') ;
END IF ;
END IF ;
-- lecteur
IF OLD.creation AND OLD.lecteur = NEW.lecteur
AND NOT NEW.lecteur = 'public'
THEN
SELECT rolname INTO n_role
FROM pg_catalog.pg_roles
WHERE pg_roles.oid = NEW.oid_lecteur ;
IF NOT FOUND
-- le rôle lecteur n'existe pas
THEN
NEW.lecteur := NULL ;
NEW.oid_lecteur := NULL ;
RAISE NOTICE '[table de gestion] Schéma %. Le rôle lecteur n''existant plus, il est déréférencé.', NEW.nom_schema
USING DETAIL = 'Ancien nom "' || OLD.lecteur || '".' ;
NEW.ctrl := array_append(NEW.ctrl, 'CLEAN lecteur') ;
ELSIF NOT n_role = NEW.lecteur
-- libellé obsolète du lecteur
THEN
NEW.lecteur := n_role ;
RAISE NOTICE '[table de gestion] Schéma %. Mise à jour du libellé du rôle lecteur, renommé entre temps.', NEW.nom_schema
USING DETAIL = 'Ancien nom "' || OLD.lecteur || '", nouveau nom "' || NEW.lecteur || '".' ;
NEW.ctrl := array_append(NEW.ctrl, 'CLEAN lecteur') ;
END IF ;
END IF ;
END IF ;

------ NON RESPECT DES CONTRAINTES ------
-- non nullité de nom_schema
IF NEW.nom_schema IS NULL
THEN
RAISE EXCEPTION 'TB8. Saisie incorrecte. Le nom du schéma doit être renseigné (champ nom_schema).' ;
END IF ;
-- unicité de nom_schema
-- -> contrôlé après les manipulations sur les blocs de
-- la partie suivante.
-- unicité de oid_schema
IF TG_OP = 'INSERT' AND NEW.oid_schema IN (SELECT gestion_schema_etr.oid_schema FROM z_asgard.gestion_schema_etr
WHERE gestion_schema_etr.oid_schema IS NOT NULL)
THEN
RAISE EXCEPTION 'TB11. Saisie incorrecte (schéma %). Un schéma de même OID est déjà répertorié dans la table de gestion.', NEW.nom_schema ;
ELSIF TG_OP = 'UPDATE'
THEN
-- cas (très hypothétique) d'une modification d'OID
IF NOT coalesce(NEW.oid_schema, -1) = coalesce(OLD.oid_schema, -1)
AND NEW.oid_schema IN (SELECT gestion_schema_etr.oid_schema FROM z_asgard.gestion_schema_etr
WHERE gestion_schema_etr.oid_schema IS NOT NULL)
THEN
RAISE EXCEPTION 'TB12. Saisie incorrecte (schéma %). Un schéma de même OID est déjà répertorié dans la table de gestion.', NEW.nom_schema ;
END IF ;
END IF ;
-- non répétition des rôles
IF NOT ((NEW.oid_lecteur IS NULL OR NOT NEW.oid_lecteur = NEW.oid_producteur)
AND (NEW.oid_editeur IS NULL OR NOT NEW.oid_editeur = NEW.oid_producteur)
AND (NEW.oid_lecteur IS NULL OR NEW.oid_editeur IS NULL OR NOT NEW.oid_lecteur = NEW.oid_editeur))
THEN
RAISE EXCEPTION 'TB13. Saisie incorrecte (schéma %). Les rôles producteur, lecteur et éditeur doivent être distincts.', NEW.nom_schema ;
END IF ;
END IF ;
------ COHERENCE BLOC/NOM DU SCHEMA ------
IF TG_OP IN ('INSERT', 'UPDATE')
THEN
IF NEW.bloc IS NULL AND NEW.nom_schema ~ '^[a-z]_'
-- si bloc est NULL, mais que le nom du schéma
-- comporte un préfixe, on met ce préfixe dans bloc
THEN
NEW.bloc := substring(NEW.nom_schema, '^([a-z])_') ;
RAISE NOTICE '[table de gestion] Mise à jour du bloc pour le schéma %.', NEW.nom_schema || ' (' || NEW.bloc || ')' ;
ELSIF NEW.bloc IS NULL
-- si bloc est NULL, et que (sous-entendu) le nom du schéma ne
-- respecte pas la nomenclature, on avertit l'utilisateur
THEN
RAISE NOTICE '[table de gestion] Le nom du schéma % ne respecte pas la nomenclature.', NEW.nom_schema
USING HINT = 'Si vous saisissez un préfixe dans le champ bloc, il sera automatiquement ajouté au nom du schéma.' ;
ELSIF NOT NEW.nom_schema ~ ('^'|| NEW.bloc || '_') AND NOT NEW.bloc = 'd'
-- le bloc est renseigné mais le nom du schéma ne correspond pas
-- (et il ne s'agit pas d'un schéma mis à la corbeille) :
-- si le nom est de la forme 'a_...', alors :
-- - dans le cas d'un UPDATE avec modification du nom
-- du schéma et pas du bloc, on se fie au nom du schéma
-- et on change le bloc ;
-- - si bloc n'est pas une lettre, on renvoie une erreur ;
-- - dans les autres cas, on se fie au bloc et change le
-- préfixe.
-- Si le nom ne comporte pas de préfixe :
-- - si le bloc est une lettre, on l'ajoute au début du
-- nom (sans doubler l'underscore, si le nom commençait par
-- un underscore) ;
-- - sinon on renvoie une erreur.
THEN
IF NEW.nom_schema ~ '^([a-z])?_'
THEN
IF TG_OP = 'UPDATE'
THEN
IF NOT NEW.nom_schema = OLD.nom_schema AND NEW.bloc = OLD.bloc
THEN
NEW.bloc := substring(NEW.nom_schema, '^([a-z])_') ;
RAISE NOTICE '[table de gestion] Mise à jour du bloc pour le schéma %.', NEW.nom_schema || ' (' || NEW.bloc || ')' ;
ELSIF NOT NEW.bloc ~ '^[a-z]$'
THEN
RAISE EXCEPTION 'TB14. Saisie invalide (schéma %). Le bloc doit être une lettre minuscule ou rien.', NEW.nom_schema ;
ELSE
NEW.nom_schema := regexp_replace(NEW.nom_schema, '^([a-z])?_', NEW.bloc || '_') ;
RAISE NOTICE '[table de gestion] Mise à jour du préfixe du schéma %.', NEW.nom_schema || ' d''après son bloc (' || NEW.bloc || ')' ;
END IF ;
ELSIF NOT NEW.bloc ~ '^[a-z]$'
THEN
RAISE EXCEPTION 'TB15. Saisie invalide (schéma %). Le bloc doit être une lettre minuscule ou rien.', NEW.nom_schema ;
ELSE
NEW.nom_schema := regexp_replace(NEW.nom_schema, '^([a-z])?_', NEW.bloc || '_') ;
RAISE NOTICE '[table de gestion] Mise à jour du préfixe du schéma %.', NEW.nom_schema || ' d''après son bloc (' || NEW.bloc || ')' ;
END IF ;
ELSIF NOT NEW.bloc ~ '^[a-z]$'
THEN
RAISE EXCEPTION 'TB16. Saisie invalide (schéma %). Le bloc doit être une lettre minuscule ou rien.', NEW.nom_schema ;
ELSE
NEW.nom_schema := NEW.bloc || '_' || NEW.nom_schema ;
RAISE NOTICE '[table de gestion] Mise à jour du préfixe du schéma %.', NEW.nom_schema || ' d''après son bloc (' || NEW.bloc || ')' ;
END IF ;
-- le trigger AFTER se chargera de renommer physiquement le
-- schéma d'autant que de besoin
END IF ;
END IF ;
------ NON RESPECT DES CONTRAINTES (SUITE) ------
-- unicité de nom_schema
IF TG_OP IN ('INSERT', 'UPDATE')
THEN
IF TG_OP = 'INSERT' AND NEW.nom_schema IN (SELECT gestion_schema_etr.nom_schema FROM z_asgard.gestion_schema_etr)
THEN
RAISE EXCEPTION 'TB9. Saisie incorrecte (schéma %). Un schéma de même nom est déjà répertorié dans la table de gestion.', NEW.nom_schema ;
ELSIF TG_OP = 'UPDATE'
THEN
-- cas d'un changement de nom
IF NOT NEW.nom_schema = OLD.nom_schema
AND NEW.nom_schema IN (SELECT gestion_schema_etr.nom_schema FROM z_asgard.gestion_schema_etr)
THEN
RAISE EXCEPTION 'TB10. Saisie incorrecte (schéma %). Un schéma de même nom est déjà répertorié dans la table de gestion.', NEW.nom_schema ;
END IF ;
END IF ;
END IF ;
------ MISE À LA CORBEILLE ------
-- notification de l'utilisateur
IF TG_OP = 'UPDATE'
THEN
-- schéma existant dont bloc bascule sur 'd'
-- ou schéma créé par bascule de creation sur True dans bloc vaut 'd'
IF NEW.creation AND NEW.bloc = 'd' AND (NOT OLD.bloc = 'd' OR OLD.bloc IS NULL)
OR NEW.creation AND NOT OLD.creation AND NEW.bloc = 'd'
THEN
RAISE NOTICE '[table de gestion] Le schéma % a été mis à la corbeille (bloc = ''d'').', NEW.nom_schema
USING HINT = 'Si vous basculez creation sur False, le schéma et son contenu seront automatiquement supprimés.' ;
-- restauration
ELSIF NEW.creation AND OLD.bloc = 'd' AND (NOT NEW.bloc = 'd' OR NEW.bloc IS NULL)
THEN
RAISE NOTICE '[table de gestion] Le schéma % a été retiré de la corbeille (bloc ne vaut plus ''d'').', NEW.nom_schema ;
END IF ;
ELSIF TG_OP = 'INSERT'
THEN
-- nouveau schéma dont bloc vaut 'd'
IF NEW.creation AND NEW.bloc = 'd'
THEN
RAISE NOTICE '[table de gestion] Le schéma % a été mis à la corbeille (bloc = ''d'').', NEW.nom_schema
USING HINT = 'Si vous basculez creation sur False, le schéma et son contenu seront automatiquement supprimés.' ;
END IF ;
END IF ;
------ RETURN ------
IF TG_OP IN ('UPDATE', 'INSERT')
THEN
RETURN NEW ;
ELSIF TG_OP = 'DELETE'
THEN
RETURN OLD ;
END IF ;
END
$BODY$ ;


------ 5.2 - TRIGGER AFTER ------

-- FUNCTION: z_asgard_admin.asgard_on_modify_gestion_schema_after()

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_after() RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
/* OBJET : Fonction exécutée par le trigger asgard_on_modify_gestion_schema_after,
qui répercute physiquement les modifications de la table de gestion.
CIBLES : z_asgard_admin.gestion_schema.
PORTEE : FOR EACH ROW.
DECLENCHEMENT : AFTER INSERT OR UPDATE.*/
DECLARE
utilisateur text ;
createur text ;
administrateur text ;
e_mssg text ;
e_hint text ;
e_detl text ;
b_superuser boolean ;
b_test boolean ;
l_commande text[] ;
c text ;
c_reverse text ;
a_producteur text ;
a_editeur text ;
a_lecteur text ;
n int ;
BEGIN

------ REQUETES AUTO A IGNORER ------
-- les remontées du trigger lui-même (SELF),
-- ainsi que des event triggers sur les
-- suppressions de schémas (DROP), n'appellent
-- aucune action, elles sont donc exclues dès
-- le départ
-- les remontées des changements de noms sont
-- conservées, pour le cas où la mise en
-- cohérence avec "bloc" aurait conduit à une
-- modification du nom par le trigger BEFORE
-- (géré au point suivant)
-- les remontées des créations et changements
-- de propriétaire (CREATE et OWNER) appellent
-- des opérations sur les droits plus lourdes
-- qui ne permettent pas de les exclure en
-- amont
IF NEW.ctrl[1] IN ('SELF', 'DROP')
THEN
-- aucune action
RETURN NULL ;
END IF ;

------ MANIPULATIONS PREALABLES ------
utilisateur := current_user ;
-- si besoin pour les futures opérations sur les rôles,
-- récupération du nom d'un rôle dont current_user est membre
-- et qui a l'attribut CREATEROLE. Autant que possible, la
-- requête renvoie current_user lui-même. On exclut d'office les
-- rôles NOINHERIT qui ne pourront pas avoir simultanément les
-- droits du propriétaire de NEW et OLD.producteur
SELECT rolname INTO createur FROM pg_roles
WHERE pg_has_role(rolname, 'MEMBER') AND rolcreaterole AND rolinherit
ORDER BY rolname = current_user DESC ;
IF TG_OP = 'UPDATE'
THEN
-- la validité de OLD.producteur n'ayant
-- pas été contrôlée par le trigger BEFORE,
-- on le fait maintenant
SELECT rolname INTO a_producteur
FROM pg_catalog.pg_roles
WHERE pg_roles.oid = OLD.oid_producteur ;
-- pour la suite, on emploira toujours
-- a_producteur à la place de OLD.producteur
-- pour les opérations sur les droits.
-- Il est réputé non NULL pour un schéma
-- pré-existant (OLD.creation vaut True),
-- dans la mesure où un rôle ne peut être
-- supprimé s'il est propriétaire d'un
-- schéma et où tous les changements de
-- propriétaires sont remontés par event
-- triggers (+ contrôles pour assurer la
-- non-modification manuelle des OID).
IF NOT FOUND AND OLD.creation AND NOT 'CLEAN producteur' = ANY(NEW.ctrl)
THEN
RAISE NOTICE '[table de gestion] ANOMALIE. Schéma %. L''OID actuellement renseigné pour le producteur dans la table de gestion est invalide. Poursuite avec l''OID du propriétaire courant du schéma.', OLD.nom_schema ;
SELECT replace(nspowner::regrole::text, '"', '') INTO a_producteur
FROM pg_catalog.pg_namespace
WHERE pg_namespace.oid = NEW.oid_schema ;
IF NOT FOUND
THEN
RAISE EXCEPTION 'TA1. Anomalie critique (schéma %). Le propriétaire du schéma est introuvable.', OLD.nom_schema ;
END IF ;
END IF ;
END IF ;

------ MISE EN APPLICATION D'UN CHANGEMENT DE NOM DE SCHEMA ------
IF NOT NEW.oid_schema::regnamespace::text = quote_ident(NEW.nom_schema)
-- le schéma existe et ne porte pas déjà le nom NEW.nom_schema
THEN
EXECUTE 'ALTER SCHEMA '|| NEW.oid_schema::regnamespace::text ||
' RENAME TO ' || quote_ident(NEW.nom_schema) ;
RAISE NOTICE '... Le schéma % a été renommé.', NEW.nom_schema ;
END IF ;
-- exclusion des remontées d'event trigger correspondant
-- à des changements de noms
IF NEW.ctrl[1] = 'RENAME'
THEN
-- aucune action
RETURN NULL ;
END IF ;

------ PREPARATION DU PRODUCTEUR ------
-- on ne s'intéresse pas aux cas :
-- - d'un schéma qui n'a pas/plus vocation à exister
-- (creation vaut False) ;
-- - d'un schéma pré-existant dont les rôles ne changent pas
-- ou dont le libellé a juste été nettoyé par le trigger
-- BEFORE.
-- ils sont donc exclus au préalable
-- si le moindre rôle a changé, il faudra être membre du
-- groupe propriétaire/producteur pour pouvoir modifier
-- les privilèges en conséquence
b_test := False ;
IF NOT NEW.creation
THEN
b_test := True ;
ELSIF TG_OP = 'UPDATE'
THEN
IF OLD.creation
AND (NEW.producteur = OLD.producteur OR 'CLEAN producteur' = ANY(NEW.ctrl))
AND (coalesce(NEW.editeur, '') = coalesce(OLD.editeur, '') OR 'CLEAN editeur' = ANY(NEW.ctrl))
AND (coalesce(NEW.lecteur, '') = coalesce(OLD.lecteur, '') OR 'CLEAN lecteur' = ANY(NEW.ctrl))
THEN
b_test := True ;
END IF ;
END IF ;
IF NOT b_test
THEN
IF NOT NEW.producteur IN (SELECT rolname FROM pg_catalog.pg_roles)
-- si le producteur désigné n'existe pas, on le crée
-- ou renvoie une erreur si les privilèges de l'utilisateur
-- sont insuffisants
THEN
IF createur IS NULL
THEN
RAISE EXCEPTION 'TA2. Opération interdite. Vous n''êtes pas habilité à créer le rôle %.', NEW.producteur
USING HINT = 'Être membre d''un rôle disposant des attributs CREATEROLE et INHERIT est nécessaire pour créer de nouveaux producteurs.' ;
END IF ;
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
EXECUTE 'CREATE ROLE ' || quote_ident(NEW.producteur) ;
RAISE NOTICE '... Le rôle de groupe % a été créé.', NEW.producteur ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
ELSE
-- si le rôle producteur existe, on vérifie qu'il n'a pas l'option LOGIN
-- les superusers avec LOGIN (comme postgres) sont tolérés
-- paradoxe ou non, dans l'état actuel des choses, cette erreur se
-- déclenche aussi lorsque la modification ne porte que sur les rôles
-- lecteur/éditeur
SELECT rolsuper INTO b_superuser
FROM pg_roles WHERE rolname = NEW.producteur AND rolcanlogin ;
IF NOT b_superuser
THEN
RAISE EXCEPTION 'TA3. Opération interdite (schéma %). Le producteur/propriétaire du schéma ne doit pas être un rôle de connexion.', NEW.nom_schema ;
END IF ;
END IF ;
b_superuser := coalesce(b_superuser, False) ;
-- mise à jour du champ d'OID du producteur
IF NEW.ctrl[1] IS NULL OR NOT NEW.ctrl[1] IN ('OWNER', 'CREATE')
-- pas dans le cas d'une remontée de commande directe
-- où l'OID du producteur sera déjà renseigné
-- et uniquement s'il a réellement été modifié (ce
-- qui n'est pas le cas si les changements ne portent
-- que sur les rôles lecteur/éditeur)
THEN
UPDATE z_asgard.gestion_schema_etr
SET oid_producteur = quote_ident(NEW.producteur)::regrole::oid,
ctrl = ARRAY['SELF', 'x7-A;#rzo']
WHERE nom_schema = NEW.nom_schema AND (
oid_producteur IS NULL
OR NOT oid_producteur = quote_ident(NEW.producteur)::regrole::oid
) ;
END IF ;

-- implémentation des permissions manquantes sur NEW.producteur
IF NOT pg_has_role(utilisateur, NEW.producteur, 'USAGE')
THEN
b_test := True ;
IF createur IS NULL OR b_superuser
THEN
RAISE EXCEPTION 'TA4. Opération interdite. Permissions insuffisantes pour le rôle %.', NEW.producteur
USING HINT = 'Votre rôle doit être membre de ' || NEW.producteur
|| ' ou disposer de l''attribut CREATEROLE pour réaliser cette opération.' ;
END IF ;
END IF ;
IF TG_OP = 'UPDATE'
THEN
IF OLD.creation AND NOT pg_has_role(utilisateur, a_producteur, 'USAGE')
AND NOT (NEW.producteur = OLD.producteur OR 'CLEAN producteur' = ANY(NEW.ctrl))
-- les permissions sur OLD.producteur ne sont contrôlées que si le producteur
-- a effectivement été modifié
THEN
b_test := True ;
IF createur IS NULL OR b_superuser
THEN
RAISE EXCEPTION 'TA5. Opération interdite. Permissions insuffisantes pour le rôle %.', a_producteur
USING HINT = 'Votre rôle doit être membre de ' || a_producteur
|| ' ou disposer de l''attribut CREATEROLE pour réaliser cette opération.' ;
END IF ;
END IF ;
END IF ;
IF b_test
THEN
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
-- par commodité, on rend createur membre à la fois de NEW et (si besoin)
-- de OLD.producteur, même si l'utilisateur avait déjà accès à
-- l'un des deux par ailleurs :
IF NOT pg_has_role(createur, NEW.producteur, 'USAGE') AND NOT b_superuser
THEN
EXECUTE 'GRANT ' || quote_ident(NEW.producteur) || ' TO ' || quote_ident(createur) ;
RAISE NOTICE '... Permission accordée à %.', createur || ' sur le rôle ' || NEW.producteur ;
END IF ;
IF TG_OP = 'UPDATE'
THEN
IF NOT pg_has_role(createur, a_producteur, 'USAGE') AND NOT b_superuser
THEN
EXECUTE 'GRANT ' || quote_ident(a_producteur) || ' TO ' || quote_ident(createur) ;
RAISE NOTICE '... Permission accordée à %.', createur || ' sur le rôle ' || a_producteur ;
END IF ;
END IF ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
END IF ;
-- permission de g_admin sur le producteur, s'il y a encore lieu
-- à noter que, dans le cas où le producteur n'a pas été modifié, g_admin
-- devrait déjà avoir une permission sur NEW.producteur, sauf à ce qu'elle
-- lui ait été retirée manuellement entre temps. Les requêtes suivantes
-- génèreraient alors une erreur même dans le cas où la modification ne
-- porte que sur les rôles lecteur/éditeur - ce qui peut-être perçu comme
-- discutable.
IF NOT pg_has_role('g_admin', NEW.producteur, 'USAGE') AND NOT b_superuser
THEN
IF createur IS NOT NULL
THEN
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
EXECUTE 'GRANT ' || quote_ident(NEW.producteur) || ' TO g_admin' ;
RAISE NOTICE '... Permission accordée à g_admin sur le rôle %.', NEW.producteur ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
ELSE
SELECT grantee INTO administrateur
FROM information_schema.applicable_roles
WHERE is_grantable = 'YES' AND role_name = NEW.producteur ;
IF FOUND
THEN
EXECUTE 'SET ROLE ' || quote_ident(administrateur) ;
EXECUTE 'GRANT ' || quote_ident(NEW.producteur) || ' TO g_admin' ;
RAISE NOTICE '... Permission accordée à g_admin sur le rôle %.', NEW.producteur ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
ELSE
RAISE EXCEPTION 'TA6. Opération interdite. Permissions insuffisantes pour le rôle %.', NEW.producteur
USING DETAIL = 'GRANT ' || quote_ident(NEW.producteur) || ' TO g_admin',
HINT = 'Votre rôle doit être membre de ' || NEW.producteur
|| ' avec admin option ou disposer de l''attribut CREATEROLE pour réaliser cette opération.' ;
END IF ;
END IF ;
END IF ;
END IF ;
------ PREPARATION DE L'EDITEUR ------
-- limitée ici à la création du rôle et l'implémentation
-- de son OID. On ne s'intéresse donc pas aux cas :
-- - où il y a pas d'éditeur ;
-- - d'un schéma qui n'a pas/plus vocation à exister ;
-- - d'un schéma pré-existant dont l'éditeur ne change pas
-- ou dont le libellé a seulement été nettoyé par le
-- trigger BEFORE.
-- ils sont donc exclus au préalable
b_test := False ;
IF NOT NEW.creation OR NEW.editeur IS NULL
OR 'CLEAN editeur' = ANY(NEW.ctrl)
THEN
b_test := True ;
ELSIF TG_OP = 'UPDATE'
THEN
IF OLD.creation AND NEW.editeur = OLD.editeur
THEN
b_test := True ;
END IF ;
END IF ;
IF NOT b_test
THEN
IF NOT NEW.editeur IN (SELECT rolname FROM pg_catalog.pg_roles)
AND NOT NEW.editeur = 'public'
-- si l'éditeur désigné n'existe pas, on le crée
-- ou renvoie une erreur si les privilèges de l'utilisateur
-- sont insuffisants
THEN
IF createur IS NULL
THEN
RAISE EXCEPTION 'TA7. Opération interdite. Vous n''êtes pas habilité à créer le rôle %.', NEW.editeur
USING HINT = 'Être membre d''un rôle disposant des attributs CREATEROLE et INHERIT est nécessaire pour créer de nouveaux éditeurs.' ;
END IF ;
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
EXECUTE 'CREATE ROLE ' || quote_ident(NEW.editeur) ;
RAISE NOTICE '... Le rôle de groupe % a été créé.', NEW.editeur ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
END IF ;
-- mise à jour du champ d'OID de l'éditeur
IF NEW.editeur = 'public'
THEN
UPDATE z_asgard.gestion_schema_etr
SET oid_editeur = 0,
ctrl = ARRAY['SELF', 'x7-A;#rzo']
WHERE nom_schema = NEW.nom_schema AND (
oid_editeur IS NULL
OR NOT oid_editeur = 0
) ;
ELSE
UPDATE z_asgard.gestion_schema_etr
SET oid_editeur = quote_ident(NEW.editeur)::regrole::oid,
ctrl = ARRAY['SELF', 'x7-A;#rzo']
WHERE nom_schema = NEW.nom_schema AND (
oid_editeur IS NULL
OR NOT oid_editeur = quote_ident(NEW.editeur)::regrole::oid
) ;
END IF ;
END IF ;
------ PREPARATION DU LECTEUR ------
-- limitée ici à la création du rôle et l'implémentation
-- de son OID. On ne s'intéresse donc pas aux cas :
-- - où il y a pas de lecteur ;
-- - d'un schéma qui n'a pas/plus vocation à exister ;
-- - d'un schéma pré-existant dont l'éditeur ne change pas
-- ou dont le libellé a seulement été nettoyé par le
-- trigger BEFORE.
-- ils sont donc exclus au préalable
b_test := False ;
IF NOT NEW.creation OR NEW.lecteur IS NULL
OR 'CLEAN lecteur' = ANY(NEW.ctrl)
THEN
b_test := True ;
ELSIF TG_OP = 'UPDATE'
THEN
IF OLD.creation AND NEW.lecteur = OLD.lecteur
THEN
b_test := True ;
END IF ;
END IF ;
IF NOT b_test
THEN
IF NOT NEW.lecteur IN (SELECT rolname FROM pg_catalog.pg_roles)
AND NOT NEW.lecteur = 'public'
-- si le lecteur désigné n'existe pas, on le crée
-- ou renvoie une erreur si les privilèges de l'utilisateur
-- sont insuffisants
THEN
IF createur IS NULL
THEN
RAISE EXCEPTION 'TA8. Opération interdite. Vous n''êtes pas habilité à créer le rôle %.', NEW.lecteur
USING HINT = 'Être membre d''un rôle disposant des attributs CREATEROLE et INHERIT est nécessaire pour créer de nouveaux éditeurs.' ;
END IF ;
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
EXECUTE 'CREATE ROLE ' || quote_ident(NEW.lecteur) ;
RAISE NOTICE '... Le rôle de groupe % a été créé.', NEW.lecteur ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
END IF ;
-- mise à jour du champ d'OID du lecteur
IF NEW.lecteur = 'public'
THEN
UPDATE z_asgard.gestion_schema_etr
SET oid_lecteur = 0,
ctrl = ARRAY['SELF', 'x7-A;#rzo']
WHERE nom_schema = NEW.nom_schema AND (
oid_lecteur IS NULL
OR NOT oid_lecteur = 0
) ;
ELSE
UPDATE z_asgard.gestion_schema_etr
SET oid_lecteur = quote_ident(NEW.lecteur)::regrole::oid,
ctrl = ARRAY['SELF', 'x7-A;#rzo']
WHERE nom_schema = NEW.nom_schema AND (
oid_lecteur IS NULL
OR NOT oid_lecteur = quote_ident(NEW.lecteur)::regrole::oid
) ;
END IF ;
END IF ;
------ CREATION DU SCHEMA ------
-- on exclut au préalable les cas qui ne
-- correspondent pas à des créations, ainsi que les
-- remontées de l'event trigger sur CREATE SCHEMA,
-- car le schéma existe alors déjà
b_test := False ;
IF NOT NEW.creation OR NEW.ctrl[1] = 'CREATE'
THEN
b_test := True ;
ELSIF TG_OP = 'UPDATE'
THEN
IF OLD.creation
THEN
b_test := True ;
END IF ;
END IF ;
IF NOT b_test
THEN
-- le schéma est créé s'il n'existe pas déjà (cas d'ajout
-- d'un schéma pré-existant qui n'était pas référencé dans
-- gestion_schema jusque-là), sinon on alerte juste
-- l'utilisateur
IF NOT NEW.nom_schema IN (SELECT nspname FROM pg_catalog.pg_namespace)
THEN
IF NOT has_database_privilege(current_database(), 'CREATE')
OR NOT pg_has_role(NEW.producteur, 'USAGE')
THEN
-- si le rôle courant n'a pas les privilèges nécessaires pour
-- créer le schéma, on tente avec le rôle createur [de rôles]
-- pré-identifié, dont on sait au moins qu'il aura les
-- permissions nécessaires sur le rôle producteur - mais pas
-- s'il est habilité à créer des schémas
IF createur IS NOT NULL
THEN
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
END IF ;
IF NOT has_database_privilege(current_database(), 'CREATE')
OR NOT pg_has_role(NEW.producteur, 'USAGE')
THEN
RAISE EXCEPTION 'TA9. Opération interdite. Vous n''êtes pas habilité à créer le schéma %.', NEW.nom_schema
USING HINT = 'Être membre d''un rôle disposant du privilège CREATE sur la base de données est nécessaire pour créer des schémas.' ;
END IF ;
END IF ;
EXECUTE 'CREATE SCHEMA ' || quote_ident(NEW.nom_schema) || ' AUTHORIZATION ' || quote_ident(NEW.producteur) ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
RAISE NOTICE '... Le schéma % a été créé.', NEW.nom_schema ;
ELSE
RAISE NOTICE '(schéma % pré-existant)', NEW.nom_schema ;
END IF ;
-- récupération de l'OID du schéma
UPDATE z_asgard.gestion_schema_etr
SET oid_schema = quote_ident(NEW.nom_schema)::regnamespace::oid,
ctrl = ARRAY['SELF', 'x7-A;#rzo']
WHERE nom_schema = NEW.nom_schema AND (
oid_schema IS NULL
OR NOT oid_schema = quote_ident(NEW.nom_schema)::regnamespace::oid
) ;
END IF ;
------ APPLICATION DES DROITS DU PRODUCTEUR ------
-- comme précédemment pour la préparation du producteur,
-- on ne s'intéresse pas aux cas :
-- - d'un schéma qui n'a pas/plus vocation à exister
-- (creation vaut False) ;
-- - d'un schéma pré-existant dont le producteur ne change pas
-- ou dont le libellé a juste été nettoyé par le trigger
-- BEFORE ;
-- - d'un schéma qui vient d'être créé, car le producteur
-- sera déjà propriétaire du schéma et de son éventuel
-- contenu (INSERT ou remontée de l'event trigger
-- asgard_on_create_schema) ;
-- - de z_asgard_admin (pour permettre sa saisie initiale
-- dans la table de gestion, étant entendu qu'il est
-- impossible au trigger sur gestion_schema de lancer
-- un ALTER TABLE OWNER TO sur cette même table).
-- ils sont donc exclus au préalable
b_test := False ;
IF NOT NEW.creation
OR 'CLEAN producteur' = ANY(NEW.ctrl)
OR TG_OP = 'INSERT'
OR NEW.ctrl[1] = 'CREATE'
OR NEW.nom_schema = 'z_asgard_admin'
THEN
b_test := True ;
ELSIF TG_OP = 'UPDATE'
THEN
IF OLD.creation AND NEW.producteur = OLD.producteur
THEN
b_test := True ;
END IF ;
END IF ;
IF NOT b_test
THEN
-- si besoin, on bascule sur le rôle createur. À ce stade,
-- il est garanti que soit l'utilisateur courant soit
-- createur (pour le cas d'un utilisateur courant
-- NOINHERIT) aura les privilèges nécessaires
IF NOT pg_has_role(NEW.producteur, 'USAGE')
THEN
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
ELSIF TG_OP = 'UPDATE'
THEN
IF NOT pg_has_role(a_producteur, 'USAGE')
THEN
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
END IF ;
END IF ;
-- changements de propriétaires
IF (NEW.nom_schema, NEW.producteur)
IN (SELECT schema_name, schema_owner FROM information_schema.schemata)
THEN
-- si producteur est déjà propriétaire du schéma (cas d'une remontée de l'event trigger,
-- principalement), on ne change que les propriétaires des objets éventuels
IF quote_ident(NEW.nom_schema)::regnamespace::oid
IN (SELECT refobjid FROM pg_catalog.pg_depend WHERE deptype = 'n')
THEN
-- la commande n'est cependant lancée que s'il existe des dépendances de type
-- DEPENDENCY_NORMAL sur le schéma, ce qui est une condition nécessaire à
-- l'existence d'objets dans le schéma
RAISE NOTICE 'attribution de la propriété des objets au rôle producteur du schéma % :', NEW.nom_schema ;
SELECT z_asgard.asgard_admin_proprietaire(NEW.nom_schema, NEW.producteur, False)
INTO n ;
IF n = 0
THEN
RAISE NOTICE '> néant' ;
END IF ;
END IF ;
ELSE
-- sinon schéma + objets
RAISE NOTICE 'attribution de la propriété du schéma et des objets au rôle producteur du schéma % :', NEW.nom_schema ;
PERFORM z_asgard.asgard_admin_proprietaire(NEW.nom_schema, NEW.producteur) ;
END IF ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
END IF ;
------ APPLICATION DES DROITS DE L'EDITEUR ------
-- on ne s'intéresse pas aux cas :
-- - d'un schéma qui n'a pas/plus vocation à exister ;
-- - d'un schéma pré-existant dont l'éditeur ne change pas
-- (y compris pour rester vide) ou dont le libellé
-- a seulement été nettoyé par le trigger BEFORE.
-- ils sont donc exclus au préalable
b_test := False ;
IF NOT NEW.creation OR 'CLEAN editeur' = ANY(NEW.ctrl)
THEN
b_test := True ;
ELSIF TG_OP = 'UPDATE'
THEN
IF OLD.creation
AND coalesce(NEW.editeur, '') = coalesce(OLD.editeur, '')
THEN
b_test := True ;
END IF ;
END IF ;
IF NOT b_test
THEN
-- si besoin, on bascule sur le rôle createur. À ce stade,
-- il est garanti que soit l'utilisateur courant soit
-- createur (pour le cas d'un utilisateur courant
-- NOINHERIT) aura les privilèges nécessaires
IF NOT pg_has_role(NEW.producteur, 'USAGE')
THEN
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
END IF ;
IF TG_OP = 'UPDATE'
THEN
-- la validité de OLD.editeur n'ayant
-- pas été contrôlée par le trigger BEFORE,
-- on le fait maintenant
IF OLD.editeur = 'public'
THEN
a_editeur := 'public' ;
-- récupération des modifications manuelles des
-- droits de OLD.editeur/public, grâce à la fonction
-- asgard_synthese_public
SELECT array_agg(commande) INTO l_commande
FROM z_asgard.asgard_synthese_public(
quote_ident(NEW.nom_schema)::regnamespace
) ;
ELSE
SELECT rolname INTO a_editeur
FROM pg_catalog.pg_roles
WHERE pg_roles.oid = OLD.oid_editeur ;
IF FOUND
THEN
-- récupération des modifications manuelles des
-- droits de OLD.editeur, grâce à la fonction
-- asgard_synthese_role
SELECT array_agg(commande) INTO l_commande
FROM z_asgard.asgard_synthese_role(
quote_ident(NEW.nom_schema)::regnamespace,
quote_ident(a_editeur)::regrole
) ;
END IF ;
END IF ;
END IF ;

IF l_commande IS NOT NULL
-- transfert sur NEW.editeur des droits de
-- OLD.editeur, le cas échéant
THEN
IF NEW.editeur IS NOT NULL
THEN
RAISE NOTICE 'suppression et transfert vers le nouvel éditeur des privilèges de l''ancien éditeur du schéma % :', NEW.nom_schema ;
ELSE
RAISE NOTICE 'suppression des privilèges de l''ancien éditeur du schéma % :', NEW.nom_schema ;
END IF ;
FOREACH c IN ARRAY l_commande
LOOP
IF NEW.editeur IS NOT NULL
THEN
EXECUTE format(c, NEW.editeur) ;
RAISE NOTICE '> %', format(c, NEW.editeur) ;
END IF ;
IF c ~ '^GRANT'
THEN
SELECT z_asgard.asgard_grant_to_revoke(c) INTO c_reverse ;
EXECUTE format(c_reverse, a_editeur) ;
RAISE NOTICE '> %', format(c_reverse, a_editeur) ;
END IF ;
END LOOP ;
-- sinon, application des privilèges standards de l'éditeur
ELSIF NEW.editeur IS NOT NULL
THEN
RAISE NOTICE 'application des privilèges standards pour le rôle éditeur du schéma % :', NEW.nom_schema ;
EXECUTE 'GRANT USAGE ON SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.editeur) ;
RAISE NOTICE '> %', 'GRANT USAGE ON SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.editeur) ;
EXECUTE 'GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.editeur) ;
RAISE NOTICE '> %', 'GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.editeur) ;
EXECUTE 'GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.editeur) ;
RAISE NOTICE '> %', 'GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.editeur) ;
END IF ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
END IF ;
------ APPLICATION DES DROITS DU LECTEUR ------
-- on ne s'intéresse pas aux cas :
-- - d'un schéma qui n'a pas/plus vocation à exister ;
-- - d'un schéma pré-existant dont le lecteur ne change pas
-- (y compris pour rester vide) ou dont le libellé
-- a seulement été nettoyé par le trigger BEFORE.
-- ils sont donc exclus au préalable
b_test := False ;
l_commande := NULL ;
IF NOT NEW.creation OR 'CLEAN lecteur' = ANY(NEW.ctrl)
THEN
b_test := True ;
ELSIF TG_OP = 'UPDATE'
THEN
IF OLD.creation
AND coalesce(NEW.lecteur, '') = coalesce(OLD.lecteur, '')
THEN
b_test := True ;
END IF ;
END IF ;
IF NOT b_test
THEN
-- si besoin, on bascule sur le rôle createur. À ce stade,
-- il est garanti que soit l'utilisateur courant soit
-- createur (pour le cas d'un utilisateur courant
-- NOINHERIT) aura les privilèges nécessaires
IF NOT pg_has_role(NEW.producteur, 'USAGE')
THEN
EXECUTE 'SET ROLE ' || quote_ident(createur) ;
END IF ;
IF TG_OP = 'UPDATE'
THEN
-- la validité de OLD.lecteur n'ayant
-- pas été contrôlée par le trigger BEFORE,
-- on le fait maintenant
IF OLD.lecteur = 'public'
THEN
a_lecteur := 'public' ;
-- récupération des modifications manuelles des
-- droits de OLD.lecteur/public, grâce à la fonction
-- asgard_synthese_public
SELECT array_agg(commande) INTO l_commande
FROM z_asgard.asgard_synthese_public(
quote_ident(NEW.nom_schema)::regnamespace
) ;
ELSE
SELECT rolname INTO a_lecteur
FROM pg_catalog.pg_roles
WHERE pg_roles.oid = OLD.oid_lecteur ;
IF FOUND
THEN
-- récupération des modifications manuelles des
-- droits de OLD.lecteur, grâce à la fonction
-- asgard_synthese_role
SELECT array_agg(commande) INTO l_commande
FROM z_asgard.asgard_synthese_role(
quote_ident(NEW.nom_schema)::regnamespace,
quote_ident(a_lecteur)::regrole
) ;
END IF ;
END IF ;
END IF ;

IF l_commande IS NOT NULL
-- transfert sur NEW.lecteur des droits de
-- OLD.lecteur, le cas échéant
THEN
IF NEW.lecteur IS NOT NULL
THEN
RAISE NOTICE 'suppression et transfert vers le nouveau lecteur des privilèges de l''ancien lecteur du schéma % :', NEW.nom_schema ;
ELSE
RAISE NOTICE 'suppression des privilèges de l''ancien lecteur du schéma % :', NEW.nom_schema ;
END IF ;
FOREACH c IN ARRAY l_commande
LOOP
IF NEW.lecteur IS NOT NULL
THEN
EXECUTE format(c, NEW.lecteur) ;
RAISE NOTICE '> %', format(c, NEW.lecteur) ;
END IF ;
IF c ~ '^GRANT'
THEN
SELECT z_asgard.asgard_grant_to_revoke(c) INTO c_reverse ;
EXECUTE format(c_reverse, a_lecteur) ;
RAISE NOTICE '> %', format(c_reverse, a_lecteur) ;
END IF ;
END LOOP ;
-- sinon, application des privilèges standards du lecteur
ELSIF NEW.lecteur IS NOT NULL
THEN
RAISE NOTICE 'application des privilèges standards pour le rôle lecteur du schéma % :', NEW.nom_schema ;
EXECUTE 'GRANT USAGE ON SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.lecteur) ;
RAISE NOTICE '> %', 'GRANT USAGE ON SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.lecteur) ;
EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.lecteur) ;
RAISE NOTICE '> %', 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.lecteur) ;
EXECUTE 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.lecteur) ;
RAISE NOTICE '> %', 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA ' || quote_ident(NEW.nom_schema) || ' TO ' || quote_ident(NEW.lecteur) ;
END IF ;
EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
END IF ;
RETURN NULL ;

EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
e_hint = PG_EXCEPTION_HINT,
e_detl = PG_EXCEPTION_DETAIL ;
RAISE EXCEPTION 'TA0. Opération annulée. Anomalie lors de la traduction physique des modifications de la table de gestion.'
USING DETAIL = CASE WHEN e_detl = '' THEN e_mssg ELSE e_mssg || ' / ' || e_detl END,
HINT = e_hint ;
END
$BODY$ ;
(4-4/6)