QGIS » ASGARD

-- 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) ;