Projet

Général

Profil

Télécharger (65,9 ko)

Anomalie #329 » asgard_5_triggers_v15.sql

Leslie Lemaire, 31/08/2020 13:35

 

-- v0 2020.05.07 AF
-- ...
-- v7 2020.05.29 LL
-- v8 2020.05.29 LL
-- v9 2020.05.30 LL
-- v10 2020.06.02 LL
-- v11 2020.06.11 LL (asgard 0.7.0)
-- v12 2020.06.25 LL (asgard 0.7.1)
-- v13 2020.08.03 LL (asgard 1.0.0)
-- v14 2020.08.27 LL (asgard 1.0.0)
-- v15 2020.08.31 LL (asgard 1.0.0)
/*
trigger AFTER :
- meilleure prise en compte des valeurs NULL comme éléments
des array de ctrl, qui auraient pu fausser certains tests -
"= ANY(array_remove(NEW.ctrl, NULL))"
au lieu de "= ANY(NEW.ctrl)" ;
- simplification de la structuration des remontées d'erreurs ;
- le trigger n'ignore plus les INSERT pour l'application des
droits du producteur, ce qui empêchait la mise en cohérence des
propriétaires lors du référencement.

trigger BEFORE :
- meilleure prise en compte des valeurs NULL comme éléments
des array de ctrl, qui auraient pu fausser certains tests -
"= ANY(array_remove(NEW.ctrl, NULL))" au lieu de "= ANY(NEW.ctrl)" ;
- ajout de contrôles pour les modifications par g_admin sur des
schémas dont un super-utilisateur est producteur, y compris référencement ;
- amélioration du message d'erreur en cas de tentative de déférencement d'un
schéma de la nomenclature ;
- quelques HINT remplacés par DETAIL, qui paraissait plus approprié.
*/


-- MOT DE PASSE DE CONTRÔLE : 'x7-A;#rzo'
-- peut être modifié sans risque par rechercher/remplacer


---------------------------------------------
------ 5 - TRIGGERS SUR GESTION_SCHEMA ------
---------------------------------------------
/* 5.1 - TRIGGER BEFORE
5.2 - TRIGGER AFTER */

------ 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
------ INSERT 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
IF OLD.ctrl[1] = 'EXIT'
THEN
RAISE EXCEPTION 'TB26. Opération interdite (schéma %). Le déréférencement n''est pas autorisé pour les schémas de la nomenclature nationale.', OLD.nom_schema
USING HINT = 'Si vous tenez à déréférencer cet enregistrement, basculez préalablement nomenclature sur False.' ;
ELSE
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 ;
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
-- on bloque tout de même les tentatives de suppression
-- par un utilisateur qui n'aurait pas des droits suffisants (a priori
-- uniquement dans le cas de g_admin avec un schéma appartenant à un
-- super-utilisateur).
IF NOT pg_has_role(OLD.producteur, 'USAGE')
THEN
RAISE EXCEPTION 'TB23. Opération interdite (schéma %).', OLD.nom_schema
USING DETAIL = 'Seul les membres du rôle producteur ' || OLD.producteur || ' peuvent supprimer ce schéma.' ;
ELSE
EXECUTE 'DROP SCHEMA ' || quote_ident(OLD.nom_schema) || ' CASCADE' ;
RAISE NOTICE '... Le schéma % a été supprimé.', OLD.nom_schema ;
RETURN NULL ;
END IF ;
-- 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 True à False 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 DETAIL = '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 DETAIL = '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 ;
------ SCHEMAS DES SUPER-UTILISATEURS ------
-- concerne uniquement les membres de g_admin, qui voient tous
-- les schémas, y compris ceux des super-utilisateurs dont ils
-- ne sont pas membres. Les contrôles suivants bloquent dans ce
-- cas les tentatives de mise à jour des champs nom_schema,
-- producteur, editeur et lecteur, ainsi que les création de schéma
-- via un INSERT ou un UPDATE.
IF TG_OP = 'UPDATE'
THEN
IF NOT pg_has_role(OLD.producteur, 'USAGE')
AND OLD.producteur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolsuper)
AND OLD.creation
AND (
NOT OLD.nom_schema = NEW.nom_schema
OR NOT OLD.producteur = NEW.producteur AND (NEW.ctrl IS NULL OR NOT 'CLEAN producteur' = ANY(array_remove(NEW.ctrl, NULL)))
OR NOT coalesce(OLD.editeur, '') = coalesce(NEW.editeur, '') AND (NEW.ctrl IS NULL OR NOT 'CLEAN editeur' = ANY(array_remove(NEW.ctrl, NULL)))
OR NOT coalesce(OLD.lecteur, '') = coalesce(NEW.lecteur, '') AND (NEW.ctrl IS NULL OR NOT 'CLEAN lecteur' = ANY(array_remove(NEW.ctrl, NULL)))
)
THEN
RAISE EXCEPTION 'TB20. Opération interdite (schéma %).', OLD.nom_schema
USING DETAIL = 'Seul le rôle producteur ' || OLD.producteur || ' (super-utilisateur) peut modifier ce schéma.' ;
ELSIF NOT pg_has_role(NEW.producteur, 'USAGE')
AND NEW.producteur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolsuper)
AND NEW.creation AND NOT OLD.creation
THEN
RAISE EXCEPTION 'TB21. Opération interdite (schéma %).', NEW.nom_schema
USING DETAIL = 'Seul le super-utilisateur ' || NEW.producteur || ' peut créer un schéma dont il est identifié comme producteur.' ;
ELSIF NOT pg_has_role(NEW.producteur, 'USAGE')
AND NEW.producteur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolsuper)
AND NEW.creation
AND NOT OLD.producteur = NEW.producteur AND (NEW.ctrl IS NULL OR NOT 'CLEAN producteur' = ANY(array_remove(NEW.ctrl, NULL)))
THEN
RAISE EXCEPTION 'TB24. Opération interdite (schéma %).', NEW.nom_schema
USING DETAIL = 'Seul le super-utilisateur ' || NEW.producteur || ' peut se désigner comme producteur d''un schéma.' ;
END IF ;
ELSIF TG_OP = 'INSERT'
THEN
IF NOT pg_has_role(NEW.producteur, 'USAGE')
AND NEW.producteur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolsuper)
AND NEW.creation
AND NOT NEW.nom_schema IN (SELECT nspname FROM pg_catalog.pg_namespace)
-- on exclut les schémas en cours de référencement, qui sont gérés
-- juste après, avec leur propre message d'erreur
THEN
RAISE EXCEPTION 'TB22. Opération interdite (schéma %).', NEW.nom_schema
USING DETAIL = 'Seul le super-utilisateur ' || NEW.producteur || ' peut créer un schéma dont il est identifié comme producteur.' ;
ELSIF NOT pg_has_role(NEW.producteur, 'USAGE')
AND NEW.producteur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolsuper)
AND NEW.nom_schema IN (SELECT nspname FROM pg_catalog.pg_namespace)
-- schéma pré-existant en cours de référencement
THEN
RAISE EXCEPTION 'TB25. Opération interdite (schéma %).', NEW.nom_schema
USING DETAIL = 'Seul le super-utilisateur ' || NEW.producteur || ' peut référencer dans ASGARD un schéma dont il est identifié comme producteur.' ;
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$ ;

ALTER FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_before()
OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_before() IS 'ASGARD. Fonction appelée par le trigger qui valide les modifications de la table de gestion.';


-- Trigger: asgard_on_modify_gestion_schema_before

CREATE TRIGGER asgard_on_modify_gestion_schema_before
BEFORE INSERT OR DELETE OR UPDATE
ON z_asgard_admin.gestion_schema
FOR EACH ROW
EXECUTE PROCEDURE z_asgard_admin.asgard_on_modify_gestion_schema_before() ;
COMMENT ON TRIGGER asgard_on_modify_gestion_schema_before ON z_asgard_admin.gestion_schema IS 'ASGARD. Trigger qui valide les modifications de la table de gestion.';


------ 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 (NEW.ctrl IS NULL OR NOT 'CLEAN producteur' = ANY(array_remove(NEW.ctrl, NULL)))
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(array_remove(NEW.ctrl, NULL)))
AND (coalesce(NEW.editeur, '') = coalesce(OLD.editeur, '') OR 'CLEAN editeur' = ANY(array_remove(NEW.ctrl, NULL)))
AND (coalesce(NEW.lecteur, '') = coalesce(OLD.lecteur, '') OR 'CLEAN lecteur' = ANY(array_remove(NEW.ctrl, NULL)))
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(array_remove(NEW.ctrl, NULL)))
-- 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(array_remove(NEW.ctrl, NULL))
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(array_remove(NEW.ctrl, NULL))
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'une remontée de l'event trigger asgard_on_create_schema,
-- car le producteur sera déjà propriétaire du schéma
-- et de son éventuel contenu. Par contre on garde les INSERT,
-- pour les cas de référencements ;
-- - 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(array_remove(NEW.ctrl, NULL))
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(array_remove(NEW.ctrl, NULL))
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(array_remove(NEW.ctrl, NULL))
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 > %', e_mssg
USING DETAIL = e_detl,
HINT = e_hint ;
END
$BODY$ ;

ALTER FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_after()
OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_on_modify_gestion_schema_after() IS 'ASGARD. Fonction appelée par le trigger qui répercute physiquement les modifications de la table de gestion.' ;


-- Trigger: asgard_on_modify_gestion_schema_after

CREATE TRIGGER asgard_on_modify_gestion_schema_after
AFTER INSERT OR UPDATE
ON z_asgard_admin.gestion_schema
FOR EACH ROW
EXECUTE PROCEDURE z_asgard_admin.asgard_on_modify_gestion_schema_after();

COMMENT ON TRIGGER asgard_on_modify_gestion_schema_after ON z_asgard_admin.gestion_schema IS 'ASGARD. Trigger qui répercute physiquement les modifications de la table de gestion.' ;
(2-2/2)