-- v0 2020.05.07 AF
-- v1 2020.05.15 LL
-- v2 2020.05.19 LL
-- v3 2020.05.25 LL
-- v4 2020.05.27 LL
-- v5 2020.05.28 LL
-- v6 2020.05.30 LL
-- v7 2020.06.05/19 LL (asgard 0.7.0)
-- v8 2020.06.26/29 LL (asgard 0.7.1)
-- v9 2020.08.11 LL (asgard 1.0.0)
-- v10 2020.08.30 LL (asgard 1.0.0)
/*
- fonction d'import de la nomenclature : nomenclature
explicite, import pour les schémas existants dans la base,
fonctionnalité de mise à jour.
- simplification de la structuration des remontées d'erreurs pour
asgard_initialisation_gestion_schema, asgard_sortie_gestion_schema,
asgard_nettoyage_roles, asgard_import_nomenclature et
asgard_initialise_schema (ajout d'un contrôle d'erreurs).
- ajout d'un contrôle des privilèges sur l'objet dans
asgard_admin_proprietaire.
- ajout d'une fonction de réinitialisation des droits
sur l'ensemble des schémas référencés ;
- sécurisation de la fonction asgard_initialise_schema, qui,
lorsqu'elle est appliquées aux schémas d'ASGARD, n'efface
plus les privilèges essentiels au fonctionnement d'ASGARD.
 */


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

---------------------------------------
------ 4 - FONCTIONS UTILITAIRES ------
---------------------------------------
/* 4.1 - LISTES DES DROITS SUR LES OBJETS D'UN SCHEMA
   4.2 - LISTE DES DROITS SUR UN OBJET
   4.3 - MODIFICATION DU PROPRIETAIRE D'UN SCHEMA ET SON CONTENU
   4.4 - TRANSFORMATION GRANT EN REVOKE
   4.5 - INITIALISATION DE GESTION_SCHEMA
   4.6 - DEREFERENCEMENT D'UN SCHEMA
   4.7 - NETTOYAGE DES RÔLES
   4.8 - REINITIALISATION DES PRIVILEGES SUR UN SCHEMA
   4.9 - REINITIALISATION DES PRIVILEGES SUR UN OBJET
   4.10 - DEPLACEMENT D'OBJET
   4.11 - OCTROI D'UN RÔLE À TOUS LES RÔLES DE CONNEXION
   4.12 - IMPORT DE LA NOMENCLATURE DANS GESTION_SCHEMA
   4.13 - REAFFECTATION DES PRIVILEGES D'UN RÔLE
   4.14 - REINITIALISATION DES PRIVILEGES SUR TOUS LES SCHEMAS */


------ 4.1 - LISTES DES DROITS SUR LES OBJETS D'UN SCHEMA ------

-- FUNCTION: z_asgard.asgard_synthese_role(regnamespace, regrole)

CREATE OR REPLACE FUNCTION z_asgard.asgard_synthese_role(n_schema regnamespace, n_role regrole)
    RETURNS TABLE(commande text)
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction renvoie une table contenant une
           liste de commandes GRANT et REVOKE permettant de
           recréer les droits de "role_1" sur les objets du
           schéma "schema" (et le schéma lui-même).
ARGUMENTS :
- "schema" est un nom de schéma valide, casté en regnamespace ;
- "role_1" est un nom de rôle valide, casté en regrole.
SORTIE : Une table avec un unique champ nommé "commande". */
DECLARE
    n_role_trans text := n_role::text ;
BEGIN
    ------ SCHEMAS ------
    -- privilèges attribués (hors propriétaire) :
    RETURN QUERY
        WITH t_acl AS (
        SELECT unnest(nspacl)::text AS acl
            FROM pg_catalog.pg_namespace
            WHERE oid = n_schema::oid
                AND nspacl IS NOT NULL
                AND NOT n_role::oid = nspowner
        )
        SELECT 'GRANT ' || privilege || ' ON SCHEMA ' || n_schema::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['USAGE', 'CREATE'], ARRAY['U', 'C']) AS l (privilege, prvlg)
            WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
    -- privilèges révoqués du propriétaire :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(CASE WHEN nspacl::text[] = ARRAY[]::text[]
                               OR NOT array_to_string(nspacl, ',') ~ ('^' || n_role_trans || '[=]')
                                   AND NOT array_to_string(nspacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                           THEN ARRAY[NULL]::text[]
                           ELSE nspacl::text[] END) AS acl
            FROM pg_catalog.pg_namespace
            WHERE oid = n_schema::oid
                AND n_role::oid = nspowner
                AND nspacl IS NOT NULL
        )
        SELECT 'REVOKE ' || privilege || ' ON SCHEMA ' || n_schema::text || ' FROM %I'
            FROM t_acl, unnest(ARRAY['USAGE', 'CREATE'], ARRAY['U', 'C']) AS l (privilege, prvlg)
            WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
    ------ TABLES ------
    -- inclut les vues, vues matérialisées, tables étrangères et partitions
    -- privilèges attribués (hors propriétaire) :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(relacl)::text AS acl
            FROM pg_catalog.pg_class
            WHERE relnamespace = n_schema
                AND relkind IN ('r', 'v', 'm', 'f', 'p')
                AND relacl IS NOT NULL
                AND NOT n_role::oid = relowner
        )
        SELECT 'GRANT ' || privilege || ' ON TABLE ' || oid::regclass::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE',
                                     'TRUNCATE', 'REFERENCES', 'TRIGGER'],
                               ARRAY['r', 'a', 'w', 'd', 'D', 'x', 't']) AS l (privilege, prvlg)
            WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
    -- privilèges révoqués du propriétaire :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(CASE WHEN relacl::text[] = ARRAY[]::text[]
                               OR NOT array_to_string(relacl, ',') ~ ('^' || n_role_trans || '[=]')
                                   AND NOT array_to_string(relacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                           THEN ARRAY[NULL]::text[]
                           ELSE relacl::text[] END) AS acl
            FROM pg_catalog.pg_class
            WHERE relnamespace = n_schema
                AND relacl IS NOT NULL
                AND relkind IN ('r', 'v', 'm', 'f', 'p')
                AND n_role::oid = relowner
        )
        SELECT 'REVOKE ' || privilege || ' ON TABLE ' || oid::regclass::text || ' FROM %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE',
                                     'TRUNCATE', 'REFERENCES', 'TRIGGER'],
                               ARRAY['r', 'a', 'w', 'd', 'D', 'x', 't']) AS l (privilege, prvlg)
            WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
    ------ SEQUENCES ------
    -- privilèges attribués (hors propriétaire) :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(relacl)::text AS acl
            FROM pg_catalog.pg_class
            WHERE relnamespace = n_schema
                AND relkind = 'S'
                AND relacl IS NOT NULL
                AND NOT n_role::oid = relowner
        )
        SELECT 'GRANT ' || privilege || ' ON SEQUENCE ' || oid::regclass::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'USAGE', 'UPDATE'],
                               ARRAY['r', 'U', 'w']) AS l (privilege, prvlg)
            WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
    -- privilèges révoqués du propriétaire :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(CASE WHEN relacl::text[] = ARRAY[]::text[]
                               OR NOT array_to_string(relacl, ',') ~ ('^' || n_role_trans || '[=]')
                                   AND NOT array_to_string(relacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                           THEN ARRAY[NULL]::text[]
                           ELSE relacl::text[] END) AS acl
            FROM pg_catalog.pg_class
            WHERE relnamespace = n_schema
                AND relacl IS NOT NULL
                AND relkind = 'S'
                AND n_role::oid = relowner
        )
        SELECT 'REVOKE ' || privilege || ' ON SEQUENCE ' || oid::regclass::text || ' FROM %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'USAGE', 'UPDATE'],
                               ARRAY['r', 'U', 'w']) AS l (privilege, prvlg)
            WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
    ------ COLONNES ------
    -- privilèges attribués :
    RETURN QUERY
        WITH t_acl AS (
        SELECT attname, attrelid, unnest(attacl)::text AS acl
            FROM pg_catalog.pg_class JOIN pg_catalog.pg_attribute
                     ON pg_class.oid = pg_attribute.attrelid
            WHERE relnamespace = n_schema
                AND attacl IS NOT NULL
        )
        SELECT 'GRANT ' || privilege || ' (' || attname::text || ') ON TABLE '
                || attrelid::regclass::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'REFERENCES'],
                               ARRAY['r', 'a', 'w', 'x']) AS l (privilege, prvlg)
            WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
    ------ FONCTIONS ------
    -- inclut les fonctions d'agrégation
    -- privilèges attribués (hors propriétaire) :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(proacl)::text AS acl
            FROM pg_catalog.pg_proc
            WHERE pronamespace = n_schema
                AND proacl IS NOT NULL
                AND NOT n_role::oid = proowner
        )
        SELECT 'GRANT ' || privilege || ' ON FUNCTION ' || oid::regprocedure::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['EXECUTE'], ARRAY['X']) AS l (privilege, prvlg)
            WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
    -- privilèges révoqués du propriétaire :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(CASE WHEN proacl::text[] = ARRAY[]::text[]
                               OR NOT array_to_string(proacl, ',') ~ ('^' || n_role_trans || '[=]')
                                   AND NOT array_to_string(proacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                           THEN ARRAY[NULL]::text[]
                           ELSE proacl::text[] END) AS acl
            FROM pg_catalog.pg_proc
            WHERE pronamespace = n_schema
                AND n_role::oid = proowner
                AND proacl IS NOT NULL
        )
        SELECT 'REVOKE ' || privilege || ' ON FUNCTION ' || oid::regprocedure::text || ' FROM %I'
            FROM t_acl, unnest(ARRAY['EXECUTE'], ARRAY['X']) AS l (privilege, prvlg)
            WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
    ------ TYPES ------
    -- inclut les domaines
    -- privilèges attribués (hors propriétaire) :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(typacl)::text AS acl
            FROM pg_catalog.pg_type
            WHERE typnamespace = n_schema
                AND typacl IS NOT NULL
                AND NOT n_role::oid = typowner
        )
        SELECT 'GRANT ' || privilege || ' ON TYPE ' || oid::regtype::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['USAGE'], ARRAY['U']) AS l (privilege, prvlg)
            WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
    -- privilèges révoqués du propriétaire :
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(CASE WHEN typacl::text[] = ARRAY[]::text[]
                               OR NOT array_to_string(typacl, ',') ~ ('^' || n_role_trans || '[=]')
                                   AND NOT array_to_string(typacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                           THEN ARRAY[NULL]::text[]
                           ELSE typacl::text[] END) AS acl
            FROM pg_catalog.pg_type
            WHERE typnamespace = n_schema
                AND n_role::oid = typowner
                AND typacl IS NOT NULL
        )
        SELECT 'REVOKE ' || privilege || ' ON TYPE ' || oid::regtype::text || ' FROM %I'
            FROM t_acl, unnest(ARRAY['USAGE'], ARRAY['U']) AS l (privilege, prvlg)
            WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
END
$_$;

ALTER FUNCTION z_asgard.asgard_synthese_role(regnamespace, regrole)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_synthese_role(regnamespace, regrole) IS 'ASGARD. Fonction qui liste les commandes permettant de reproduire les droits d''un rôle sur les objets d''un schéma.' ;


-- FUNCTION: z_asgard.asgard_synthese_public(regnamespace)

CREATE OR REPLACE FUNCTION z_asgard.asgard_synthese_public(n_schema regnamespace)
    RETURNS TABLE(commande text)
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction renvoie une table contenant une
           liste de commandes GRANT et REVOKE permettant de
           recréer les droits de public sur les objets du
           schéma "schema" (et le schéma lui-même).
REMARQUE : La fonction ne s'intéresse pas aux objets de type
fonction (dont agrégats) et type (dont domaines), sur lesquels
public reçoit des droits par défaut qu'il n'est pas judicieux
de reproduire sur un autre rôle, ni de révoquer lors d'un
changement de lecteur/éditeur. Si des privilèges par défaut ont
été révoqués pour public, la révocation restera valable pour les
futurs lecteur/éditeurs puisqu'il n'y a pas d'attribution
de privilèges supplémentaires pour les lecteurs/éditeurs sur
ces objets.
ARGUMENT : "schema" est un nom de schéma valide, casté en
regnamespace.
SORTIE : Une table avec un unique champ nommé "commande". */
BEGIN
    ------ SCHEMAS ------
    RETURN QUERY
        WITH t_acl AS (
        SELECT unnest(nspacl)::text AS acl
            FROM pg_catalog.pg_namespace
            WHERE oid = n_schema::oid
                AND nspacl IS NOT NULL
        )
        SELECT 'GRANT ' || privilege || ' ON SCHEMA ' || n_schema::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['USAGE', 'CREATE'], ARRAY['U', 'C']) AS l (privilege, prvlg)
            WHERE acl ~ ('^[=].*' || prvlg || '.*[/]') ;
    ------ TABLES ------
    -- inclut les vues, vues matérialisées, tables étrangères et partitions
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(relacl)::text AS acl
            FROM pg_catalog.pg_class
            WHERE relnamespace = n_schema
                AND relkind IN ('r', 'v', 'm', 'f', 'p')
                AND relacl IS NOT NULL
        )
        SELECT 'GRANT ' || privilege || ' ON TABLE ' || oid::regclass::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE',
                                     'TRUNCATE', 'REFERENCES', 'TRIGGER'],
                               ARRAY['r', 'a', 'w', 'd', 'D', 'x', 't']) AS l (privilege, prvlg)
            WHERE acl ~ ('^[=].*' || prvlg || '.*[/]') ;
    ------ SEQUENCES ------
    RETURN QUERY
        WITH t_acl AS (
        SELECT oid, unnest(relacl)::text AS acl
            FROM pg_catalog.pg_class
            WHERE relnamespace = n_schema
                AND relkind = 'S'
                AND relacl IS NOT NULL
        )
        SELECT 'GRANT ' || privilege || ' ON SEQUENCE ' || oid::regclass::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'USAGE', 'UPDATE'],
                               ARRAY['r', 'U', 'w']) AS l (privilege, prvlg)
            WHERE acl ~ ('^[=].*' || prvlg || '.*[/]') ;
    ------ COLONNES ------
    RETURN QUERY
        WITH t_acl AS (
        SELECT attname, attrelid, unnest(attacl)::text AS acl
            FROM pg_catalog.pg_class JOIN pg_catalog.pg_attribute
                     ON pg_class.oid = pg_attribute.attrelid
            WHERE relnamespace = n_schema
                AND attacl IS NOT NULL
        )
        SELECT 'GRANT ' || privilege || ' (' || attname::text || ') ON TABLE '
                || attrelid::regclass::text || ' TO %I'
            FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'REFERENCES'],
                               ARRAY['r', 'a', 'w', 'x']) AS l (privilege, prvlg)
            WHERE acl ~ ('^[=].*' || prvlg || '.*[/]') ;
END
$_$;

ALTER FUNCTION z_asgard.asgard_synthese_public(regnamespace)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_synthese_public(regnamespace) IS 'ASGARD. Fonction qui liste les commandes permettant de reproduire les droits de public sur les objets d''un schéma.' ;




------ 4.2 - LISTE DES DROITS SUR UN OBJET ------

-- FUNCTION: z_asgard.asgard_synthese_role_obj(oid, text, regrole)

CREATE OR REPLACE FUNCTION z_asgard.asgard_synthese_role_obj(obj_oid oid, obj_type text, n_role regrole)
    RETURNS TABLE(commande text)
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction renvoie une table contenant une
           liste de commandes GRANT et REVOKE permettant de
           recréer les droits de "role_1" sur un objet de type
		   table, table étrangère, partition de table, vue,
           vue matérialisée, séquence, fonction (dont fonctions
           d'agrégations), type (dont domaines).
ARGUMENTS :
- "obj_oid" est l'identifiant interne de l'objet ;
- "obj_type" est le type de l'objet au format text ('table',
'view', 'materialized view', 'sequence', 'function', 'type',
'domain', 'foreign table', 'partitioned table', 'aggregate') ;
- "role_1" est un nom de rôle valide, casté en regrole.
SORTIE : Une table avec un unique champ nommé "commande". */
DECLARE
    n_role_trans text := n_role::text ;
BEGIN
    ------ TABLE, VUE, VUE MATERIALISEE ------
    IF obj_type IN ('table', 'view', 'materialized view', 'foreign table', 'partitioned table')
    THEN
        -- privilèges attribués (si n_role n'est pas le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(relacl)::text AS acl
                FROM pg_catalog.pg_class
                WHERE oid = obj_oid
                    AND relacl IS NOT NULL
                    AND NOT n_role::oid = relowner
            )
            SELECT 'GRANT ' || privilege || ' ON TABLE ' || oid::regclass::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE',
                                         'TRUNCATE', 'REFERENCES', 'TRIGGER'],
                                   ARRAY['r', 'a', 'w', 'd', 'D', 'x', 't']) AS l (privilege, prvlg)
                WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
        -- privilèges révoqués du propriétaire (si n_role est le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(CASE WHEN relacl::text[] = ARRAY[]::text[]
                                   OR NOT array_to_string(relacl, ',') ~ ('^' || n_role_trans || '[=]')
                                       AND NOT array_to_string(relacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                               THEN ARRAY[NULL]::text[]
                               ELSE relacl::text[] END) AS acl
                FROM pg_catalog.pg_class
                WHERE oid = obj_oid
                    AND relacl IS NOT NULL
                    AND n_role::oid = relowner
            )
            SELECT 'REVOKE ' || privilege || ' ON TABLE ' || oid::regclass::text || ' FROM %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE',
                                         'TRUNCATE', 'REFERENCES', 'TRIGGER'],
                                   ARRAY['r', 'a', 'w', 'd', 'D', 'x', 't']) AS l (privilege, prvlg)
                WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
        ------ COLONNES ------
        -- privilèges attribués :
        RETURN QUERY
            WITH t_acl AS (
            SELECT attname, attrelid, unnest(attacl)::text AS acl
                FROM pg_catalog.pg_attribute
                WHERE pg_attribute.attrelid = obj_oid
                    AND attacl IS NOT NULL
            )
            SELECT 'GRANT ' || privilege || ' (' || attname::text || ') ON TABLE '
                    || attrelid::regclass::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'REFERENCES'],
                                   ARRAY['r', 'a', 'w', 'x']) AS l (privilege, prvlg)
                WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
    ------ SEQUENCES ------
    ELSIF obj_type = 'sequence'
    THEN
        -- privilèges attribués (si n_role n'est pas le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(relacl)::text AS acl
                FROM pg_catalog.pg_class
                WHERE oid = obj_oid
                    AND relacl IS NOT NULL
                    AND NOT n_role::oid = relowner
            )
            SELECT 'GRANT ' || privilege || ' ON SEQUENCE ' || oid::regclass::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'USAGE', 'UPDATE'],
                                   ARRAY['r', 'U', 'w']) AS l (privilege, prvlg)
                WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
        -- privilèges révoqués du propriétaire (si n_role est le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(CASE WHEN relacl::text[] = ARRAY[]::text[]
                                   OR NOT array_to_string(relacl, ',') ~ ('^' || n_role_trans || '[=]')
                                       AND NOT array_to_string(relacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                               THEN ARRAY[NULL]::text[]
                               ELSE relacl::text[] END) AS acl
                FROM pg_catalog.pg_class
                WHERE oid = obj_oid
                    AND relacl IS NOT NULL
                    AND n_role::oid = relowner
            )
            SELECT 'REVOKE ' || privilege || ' ON SEQUENCE ' || oid::regclass::text || ' FROM %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'USAGE', 'UPDATE'],
                                   ARRAY['r', 'U', 'w']) AS l (privilege, prvlg)
                WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
    ------ FONCTIONS ------
    -- inclut les fonctions d'agrégation
    ELSIF obj_type IN ('function', 'aggregate')
    THEN
        -- privilèges attribués (si n_role n'est pas le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(proacl)::text AS acl
                FROM pg_catalog.pg_proc
                WHERE oid = obj_oid
                    AND proacl IS NOT NULL
                    AND NOT n_role::oid = proowner
            )
            SELECT 'GRANT ' || privilege || ' ON FUNCTION ' || oid::regprocedure::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['EXECUTE'], ARRAY['X']) AS l (privilege, prvlg)
                WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
        -- privilèges révoqués du propriétaire (si n_role est le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(CASE WHEN proacl::text[] = ARRAY[]::text[]
                                   OR NOT array_to_string(proacl, ',') ~ ('^' || n_role_trans || '[=]')
                                       AND NOT array_to_string(proacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                               THEN ARRAY[NULL]::text[]
                               ELSE proacl::text[] END) AS acl
                FROM pg_catalog.pg_proc
                WHERE oid = obj_oid
                    AND n_role::oid = proowner
                    AND proacl IS NOT NULL
            )
            SELECT 'REVOKE ' || privilege || ' ON FUNCTION ' || oid::regprocedure::text || ' FROM %I'
                FROM t_acl, unnest(ARRAY['EXECUTE'], ARRAY['X']) AS l (privilege, prvlg)
                WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
    ------ TYPES ------
    -- inclut les domaines
    ELSIF obj_type IN ('type', 'domain')
    THEN
        -- privilèges attribués (si n_role n'est pas le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(typacl)::text AS acl
                FROM pg_catalog.pg_type
                WHERE oid = obj_oid
                    AND typacl IS NOT NULL
                    AND NOT n_role::oid = typowner
            )
            SELECT 'GRANT ' || privilege || ' ON TYPE ' || oid::regtype::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['USAGE'], ARRAY['U']) AS l (privilege, prvlg)
                WHERE acl ~ ('^' || n_role_trans || '[=].*' || prvlg || '.*[/]') ;
        -- privilèges révoqués du propriétaire (si n_role est le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(CASE WHEN typacl::text[] = ARRAY[]::text[]
                                   OR NOT array_to_string(typacl, ',') ~ ('^' || n_role_trans || '[=]')
                                       AND NOT array_to_string(typacl, ',') ~ ('[,]' || n_role_trans || '[=]')
                               THEN ARRAY[NULL]::text[]
                               ELSE typacl::text[] END) AS acl
                FROM pg_catalog.pg_type
                WHERE oid = obj_oid
                    AND n_role::oid = typowner
                    AND typacl IS NOT NULL
            )
            SELECT 'REVOKE ' || privilege || ' ON TYPE ' || oid::regtype::text || ' FROM %I'
                FROM t_acl, unnest(ARRAY['USAGE'], ARRAY['U']) AS l (privilege, prvlg)
                WHERE (acl ~ ('^' || n_role_trans || '[=]')
                    AND NOT acl ~ ( '[=].*' || prvlg || '.*[/]')) OR acl IS NULL ;
    ELSE
       RAISE EXCEPTION 'FSS0. Le type d''objet % n''est pas pris en charge', obj_type ;
    END IF ;
END
$_$;

ALTER FUNCTION z_asgard.asgard_synthese_role_obj(oid, text, regrole)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_synthese_role_obj(oid, text, regrole) IS 'ASGARD. Fonction qui liste les commandes permettant de reproduire les droits d''un rôle sur un objet.' ;



-- FUNCTION: z_asgard.asgard_synthese_public_obj(oid, text)

CREATE OR REPLACE FUNCTION z_asgard.asgard_synthese_public_obj(obj_oid oid, obj_type text)
    RETURNS TABLE(commande text)
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction renvoie une table contenant une
           liste de commandes GRANT et REVOKE permettant de
           recréer les droits de public sur un objet de type
		   table, table étrangère, partition de table, vue,
           vue matérialisée ou séquence.
REMARQUE : La fonction ne s'intéresse pas aux objets de type
fonction (dont agrégats) et type (dont domaines), sur lesquels
public reçoit des droits par défaut qu'il n'est pas judicieux
de reproduire sur un autre rôle, ni de révoquer lors d'un
changement de lecteur/éditeur. Si des privilèges par défaut ont
été révoqués pour public, la révocation restera valable pour les
futurs lecteur/éditeurs puisqu'il n'y a pas d'attribution
de privilèges supplémentaires pour les lecteurs/éditeurs sur
ces objets.
ARGUMENTS :
- "obj_oid" est l'identifiant interne de l'objet ;
- "obj_type" est le type de l'objet au format text ('table',
'view', 'materialized view', 'sequence', 'foreign table',
'partitioned table').
SORTIE : Une table avec un unique champ nommé "commande". */
BEGIN
    ------ TABLE, VUE, VUE MATERIALISEE ------
    IF obj_type IN ('table', 'view', 'materialized view', 'foreign table', 'partitioned table')
    THEN
        -- privilèges attribués (si n_role n'est pas le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(relacl)::text AS acl
                FROM pg_catalog.pg_class
                WHERE oid = obj_oid
                    AND relacl IS NOT NULL
            )
            SELECT 'GRANT ' || privilege || ' ON TABLE ' || oid::regclass::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE',
                                         'TRUNCATE', 'REFERENCES', 'TRIGGER'],
                                   ARRAY['r', 'a', 'w', 'd', 'D', 'x', 't']) AS l (privilege, prvlg)
                WHERE acl ~ ('^[=].*' || prvlg || '.*[/]') ;
        ------ COLONNES ------
        -- privilèges attribués :
        RETURN QUERY
            WITH t_acl AS (
            SELECT attname, attrelid, unnest(attacl)::text AS acl
                FROM pg_catalog.pg_attribute
                WHERE pg_attribute.attrelid = obj_oid
                    AND attacl IS NOT NULL
            )
            SELECT 'GRANT ' || privilege || ' (' || attname::text || ') ON TABLE '
                    || attrelid::regclass::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'REFERENCES'],
                                   ARRAY['r', 'a', 'w', 'x']) AS l (privilege, prvlg)
                WHERE acl ~ ('^[=].*' || prvlg || '.*[/]') ;
    ------ SEQUENCES ------
    ELSIF obj_type = 'sequence'
    THEN
        -- privilèges attribués (si n_role n'est pas le propriétaire de l'objet) :
        RETURN QUERY
            WITH t_acl AS (
            SELECT oid, unnest(relacl)::text AS acl
                FROM pg_catalog.pg_class
                WHERE oid = obj_oid
                    AND relacl IS NOT NULL
            )
            SELECT 'GRANT ' || privilege || ' ON SEQUENCE ' || oid::regclass::text || ' TO %I'
                FROM t_acl, unnest(ARRAY['SELECT', 'USAGE', 'UPDATE'],
                                   ARRAY['r', 'U', 'w']) AS l (privilege, prvlg)
                WHERE acl ~ ('^[=].*' || prvlg || '.*[/]') ;
    ELSE
       RAISE EXCEPTION 'FSS0. Le type d''objet % n''est pas pris en charge', obj_type ;
    END IF ;
END
$_$;

ALTER FUNCTION z_asgard.asgard_synthese_public_obj(oid, text)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_synthese_public_obj(oid, text) IS 'ASGARD. Fonction qui liste les commandes permettant de reproduire les droits de public sur un objet.' ;





------ 4.3 - MODIFICATION DU PROPRIETAIRE D'UN SCHEMA ET SON CONTENU ------

-- FUNCTION: z_asgard.asgard_admin_proprietaire(text, text, boolean)

CREATE OR REPLACE FUNCTION z_asgard.asgard_admin_proprietaire(
                           n_schema text, n_owner text, b_setschema boolean DEFAULT True
                           )
    RETURNS int
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Gestion des droits. Cette fonction permet d''attribuer
           un schéma et tous les objets qu'il contient à un [nouveau]
           propriétaire.
AVERTISSEMENT : Les objets de type operator class, operator family
et extended planner statistic ne sont pas pris en charge pour l'heure.
ARGUMENTS :
- "n_schema" est une chaîne de caractères correspondant au nom du
  schéma à considérer ;
- "n_owner" est une chaîne de caractères correspondant au nom du
  rôle (rôle de groupe ou rôle de connexion) qui doit être
  propriétaire des objets ;
- "b_setschema" est un paramètre booléen optionnel (vrai par défaut)
  qui indique si la fonction doit changer le propriétaire du schéma
  ou seulement des objets qu'il contient.
RESULTAT : la fonction renvoie un entier correspondant au nombre
d''objets effectivement traités. Les commandes lancées sont notifiées
au fur et à mesure. */
DECLARE
    item record ;
    k int := 0 ;
    o_owner oid ;
    s_owner text ;
BEGIN
    ------ TESTS PREALABLES ------
    SELECT nspowner::regrole::text
        INTO s_owner
        FROM pg_catalog.pg_namespace
        WHERE nspname = n_schema ;
    
    -- non existance du schémas
    IF NOT FOUND
    THEN
        RAISE EXCEPTION 'FAP1. Le schéma % n''existe pas.', n_schema ;
    END IF ;
    
    -- absence de permission sur le propriétaire courant du schéma
    IF NOT pg_has_role(s_owner::regrole::oid, 'USAGE')
    THEN
        RAISE EXCEPTION 'FAP5. Vous n''êtes pas habilité à modifier le propriétaire du schéma %.', n_schema
                USING DETAIL = 'Propriétaire courant : ' || s_owner || '.' ;  
    END IF ;
    
    -- le propriétaire désigné n'existe pas
    IF NOT n_owner IN (SELECT rolname::text FROM pg_catalog.pg_roles)
    THEN
        RAISE EXCEPTION 'FAP2. Le rôle % n''existe pas.', n_owner ;
    -- absence de permission sur le propriétaire désigné
    ELSIF NOT pg_has_role(n_owner, 'USAGE')
    THEN
        RAISE EXCEPTION 'FAP6. Vous n''avez pas la permission d''utiliser le rôle %.', n_owner ;  
    ELSE
        o_owner := quote_ident(n_owner)::regrole::oid ;
    END IF ;
    
    -- le propriétaire désigné n'est pas le propriétaire courant et la fonction
    -- a été lancée avec la variante qui ne traite pas le schéma
    IF NOT b_setschema
            AND NOT quote_ident(n_owner) = s_owner
    THEN
        RAISE EXCEPTION 'FAP3. Le rôle % n''est pas propriétaire du schéma.', n_owner
            USING HINT = 'Lancez asgard_admin_proprietaire(' || quote_literal(n_schema)
                         || ', ' || quote_literal(n_owner) || ') pour changer également le propriétaire du schéma.' ;
    END IF ;
    
    ------ PROPRIÉTAIRE DU SCHEMA ------
    IF b_setschema
    THEN
        EXECUTE 'ALTER SCHEMA ' || quote_ident(n_schema) || ' OWNER TO ' || quote_ident(n_owner) ;
        RAISE NOTICE '> %', 'ALTER SCHEMA ' || quote_ident(n_schema) || ' OWNER TO ' || quote_ident(n_owner) ;
        k := k + 1 ;
    END IF ;
    
    ------ PROPRIETAIRES DES OBJETS ------
    -- uniquement ceux qui n'appartiennent pas déjà
    -- au rôle identifié
    FOR item IN
        -- tables, tables étrangères, vues, vues matérialisées,
        -- partitions, séquences :
        SELECT
            relname::text AS n_objet,
            relowner AS obj_owner,
            relkind IN ('r', 'f', 'p', 'm') AS b, -- servira à assurer que les tables
                                                  -- soient listées avant les objets qui
                                                  -- en dépendent
            'ALTER ' || kind_lg || ' ' || pg_class.oid::regclass || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM pg_catalog.pg_class,
                unnest(ARRAY['r', 'p', 'v', 'm', 'f', 'S'],
                       ARRAY['TABLE', 'TABLE', 'VIEW', 'MATERIALIZED VIEW', 'FOREIGN TABLE', 'SEQUENCE']) AS l (kind_crt, kind_lg)
            WHERE relnamespace = quote_ident(n_schema)::regnamespace
                AND relkind IN ('S', 'r', 'p', 'v', 'm', 'f')
                AND kind_crt = relkind
                AND NOT relowner = o_owner
        UNION
        -- fonctions et agrégats :
        SELECT
            proname::text AS n_objet,
            proowner AS obj_owner,
            False AS b,
            'ALTER FUNCTION ' || pg_proc.oid::regprocedure || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM pg_catalog.pg_proc
            WHERE pronamespace = quote_ident(n_schema)::regnamespace
                AND NOT proowner = o_owner
            -- à noter que les agrégats (proisagg vaut True) ont
            -- leur propre commande ALTER AGGREGATE OWNER TO, mais
            -- ALTER FUNCTION OWNER TO fonctionne également, on ne
            -- fait donc pas de distinction pour l'heure
        UNION
        -- types et domaines :
        SELECT
            typname::text AS n_objet,
            typowner AS obj_owner,
            False AS b,
            'ALTER ' || kind_lg || ' ' || typnamespace::regnamespace::text || '.'
                || quote_ident(typname) || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM unnest(ARRAY['true', 'false'],
                       ARRAY['DOMAIN', 'TYPE']) AS l (kind_crt, kind_lg),
                pg_catalog.pg_type LEFT JOIN pg_catalog.pg_class ON typrelid = pg_class.oid
            WHERE typnamespace = quote_ident(n_schema)::regnamespace
                AND kind_crt::boolean = (typtype = 'd')
                AND NOT (typelem > 0 AND typname ~ '^_')
                -- exclusion des types array générés automatiquement
                -- leur propriétaire est lié à celui du type source
                -- et ne peut être modifié directement
                AND (NOT typtype = 'c' OR relkind = 'c')
                -- idem pour les types "ligne de table", dont
                -- le propriétaire est lié à celui de la table
                AND NOT typowner = o_owner
        UNION
        -- conversions :
        SELECT
            conname::text AS n_objet,
            conowner AS obj_owner,
            False AS b,
            'ALTER CONVERSION ' || connamespace::regnamespace::text || '.'
                || quote_ident(conname) || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM pg_catalog.pg_conversion
            WHERE connamespace = quote_ident(n_schema)::regnamespace
                AND NOT conowner = o_owner
        UNION
        -- opérateurs :
        SELECT
            oprname::text AS n_objet,
            oprowner AS obj_owner,
            False AS b,
            'ALTER OPERATOR ' || pg_operator.oid::regoperator || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM pg_catalog.pg_operator
            WHERE oprnamespace = quote_ident(n_schema)::regnamespace
                AND NOT oprowner = o_owner
        UNION
        -- collations :
        SELECT
            collname::text AS n_objet,
            collowner AS obj_owner,
            False AS b,
            'ALTER COLLATION ' || collnamespace::regnamespace::text || '.'
                || quote_ident(collname) || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM pg_catalog.pg_collation
            WHERE collnamespace = quote_ident(n_schema)::regnamespace
                AND NOT collowner = o_owner
        UNION
        -- text search dictionary :
        SELECT
            dictname::text AS n_objet,
            dictowner AS obj_owner,
            False AS b,
            'ALTER TEXT SEARCH DICTIONARY ' || pg_ts_dict.oid::regdictionary || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM pg_catalog.pg_ts_dict
            WHERE dictnamespace = quote_ident(n_schema)::regnamespace
                AND NOT dictowner = o_owner
        UNION
        -- text search configuration :
        SELECT
            cfgname::text AS n_objet,
            cfgowner AS obj_owner,
            False AS b,
            'ALTER TEXT SEARCH CONFIGURATION ' || pg_ts_config.oid::regconfig || ' OWNER TO '
                || quote_ident(n_owner) AS commande
            FROM pg_catalog.pg_ts_config
            WHERE cfgnamespace = quote_ident(n_schema)::regnamespace
                AND NOT cfgowner = o_owner
            ORDER BY b DESC
    LOOP
        IF pg_has_role(item.obj_owner, 'USAGE')
        THEN
            EXECUTE item.commande ;
            RAISE NOTICE '> %', item.commande ;
            k := k + 1 ;
        ELSE
            RAISE EXCEPTION 'FAP4. Vous n''êtes pas habilité à modifier le propriétaire de l''objet %.', item.n_objet
                USING DETAIL = 'Propriétaire courant : ' || item.obj_owner::regrole::text || '.' ;    
        END IF ;
    END LOOP ;
    ------ RESULTAT ------
    RETURN k ;
END
$_$ ;

ALTER FUNCTION z_asgard.asgard_admin_proprietaire(text, text, boolean)
    OWNER TO g_admin_ext ;


COMMENT ON FUNCTION z_asgard.asgard_admin_proprietaire(text, text, boolean) IS 'ASGARD. Fonction qui modifie le propriétaire d''un schéma et de tous les objets qu''il contient.' ;



------ 4.4 - TRANSFORMATION GRANT EN REVOKE ------

-- FUNCTION: z_asgard.asgard_grant_to_revoke(text)

CREATE OR REPLACE FUNCTION z_asgard.asgard_grant_to_revoke(c_grant text)
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction transforme une commande de type GRANT en
           son équivalent REVOKE, ou l'inverse.
AVERTISSEMENT : La fonction ne reconnaîtra que les mots clés écrits
en majuscules.
ARGUMENT : une commande de type GRANT/REVOKE présumée valide (chaîne de caractères).
SORTIE : une commande de type REVOKE/GRANT (chaîne de caractères). */
DECLARE
    c_revoke text ;
BEGIN
    IF c_grant ~ '^GRANT'
    THEN
        c_revoke := regexp_replace(c_grant, '^GRANT', 'REVOKE') ;
        c_revoke := regexp_replace(c_revoke, '[[:space:]]TO[[:space:]]', ' FROM ') ;
    ELSIF c_grant ~ '^REVOKE'
    THEN
        c_revoke := regexp_replace(c_grant, '^REVOKE', 'GRANT') ;
        c_revoke := regexp_replace(c_revoke, '[[:space:]]FROM[[:space:]]', ' TO ') ;
    ELSE
        RAISE EXCEPTION 'FGR1. Commande GRANT/REVOKE invalide.' ;
    END IF ;
    RETURN c_revoke ;
END
$_$;

ALTER FUNCTION z_asgard.asgard_grant_to_revoke(text)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_grant_to_revoke(text) IS 'ASGARD. Fonction qui transforme une commande GRANT en commande REVOKE.' ;


------ 4.5 - INITIALISATION DE GESTION_SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_initialisation_gestion_schema(text[], boolean)

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_initialisation_gestion_schema(
                           exceptions text[] default NULL::text[], b_gs boolean default False
                           )
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction intègre à la table de gestion des droits
           gestion_schema l'ensemble des schémas existants, hors
           schémas système et ceux qui sont (optionnellement) listés
           en argument.
ARGUMENTS :
- exceptions (optionnel) : un tableau text[] contenant les noms des schémas
à omettre, le cas échéant ;
- b_gs (optionnel) : un booléen indiquant si, dans l'hypothèse où un schéma
serait déjà référencé - nécessairement comme non créé - dans la table de gestion,
c'est le propriétaire du schéma qui doit devenir le "producteur" du schéma
(False) ou le producteur pré-renseigné dans la table de gestion qui doit
devenir le propriétaire du schéma (True). False par défaut.
SORTIE : '__ FIN INTIALISATION.' si la requête s'est exécutée normalement. */
DECLARE
    item record ;
    e_mssg text ;
    e_detl text ;
    e_hint text ;
    b_creation boolean ;
BEGIN

    FOR item IN SELECT nspname, nspowner FROM pg_catalog.pg_namespace
                    WHERE NOT nspname ~ ANY(ARRAY['^pg_toast', '^pg_temp', '^pg_catalog$',
                                            '^public$', '^information_schema$', '^topology$'])
                        AND (exceptions IS NULL OR NOT nspname = ANY(exceptions))
    LOOP
        SELECT creation INTO b_creation
            FROM z_asgard.gestion_schema_usr
            WHERE item.nspname::text = nom_schema ;
        IF b_creation IS NULL
        -- schéma non référencé dans gestion_schema
        THEN
            INSERT INTO z_asgard.gestion_schema_usr (nom_schema, producteur, creation)
                VALUES (item.nspname::text, replace(item.nspowner::regrole::text, '"', ''), true) ;
            RAISE NOTICE '... Schéma % enregistré dans la table de gestion.', item.nspname::text ;
        ELSIF NOT b_creation
        -- schéma pré-référencé dans gestion_schema
        THEN
            IF NOT b_gs
            THEN
                UPDATE z_asgard.gestion_schema_usr
                    SET creation = true,
                        producteur = replace(item.nspowner::regrole::text, '"', '')
                    WHERE item.nspname::text = nom_schema ;
            ELSE
                UPDATE z_asgard.gestion_schema_usr
                    SET creation = true
                    WHERE item.nspname::text = nom_schema ;
            END IF ;
            RAISE NOTICE '... Schéma % marqué comme créé dans la table de gestion.', item.nspname::text ;
        END IF ;
    END LOOP ;

    RETURN '__ FIN INITALISATION.' ;

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
                            e_hint = PG_EXCEPTION_HINT,
                            e_detl = PG_EXCEPTION_DETAIL ;           
    RAISE EXCEPTION 'FIG0 > %', e_mssg
        USING DETAIL = e_detl,
            HINT = e_hint ;

END
$_$;

ALTER FUNCTION z_asgard_admin.asgard_initialisation_gestion_schema(text[], boolean)
    OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_initialisation_gestion_schema(text[], boolean) IS 'ASGARD. Fonction qui initialise la table de gestion à partir des schémas existants.' ;



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

-- FUNCTION: z_asgard_admin.asgard_sortie_gestion_schema(text)

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

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

    RETURN '__ DEREFERENCEMENT REUSSI.' ;

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
                            e_hint = PG_EXCEPTION_HINT,
                            e_detl = PG_EXCEPTION_DETAIL ;
    RAISE EXCEPTION 'FSG0 > %', e_mssg
        USING DETAIL = e_detl,
            HINT = e_hint ;

END
$_$;

ALTER FUNCTION z_asgard_admin.asgard_sortie_gestion_schema(text)
    OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_sortie_gestion_schema(text) IS 'ASGARD. Fonction qui déréférence un schéma existant de la table de gestion.' ;



------ 4.7 - NETTOYAGE DES RÔLES ------

-- FUNCTION: z_asgard.asgard_nettoyage_roles()

CREATE OR REPLACE FUNCTION z_asgard.asgard_nettoyage_roles()
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction active la mise à jour des noms des rôles
           désignés dans la table de gestion comme producteur, éditeur et
           lecteur, pour prendre en compte les changements de nom
           ou suppression qui auraient pu avoir eu lieu.
ARGUMENTS : néant.
SORTIE : '__ NETTOYAGE REUSSI.' si la requête s'est exécutée normalement. */
DECLARE
    e_mssg text ;
    e_detl text ;
    e_hint text ;
BEGIN

    UPDATE z_asgard.gestion_schema_usr
        SET producteur = producteur,
            editeur = editeur,
            lecteur = lecteur ;

    RETURN '__ NETTOYAGE REUSSI.' ;

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
                            e_hint = PG_EXCEPTION_HINT,
                            e_detl = PG_EXCEPTION_DETAIL ;
    RAISE EXCEPTION 'FNR0 > %', e_mssg
        USING DETAIL = e_detl,
            HINT = e_hint ;

END
$_$;

ALTER FUNCTION z_asgard.asgard_nettoyage_roles()
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_nettoyage_roles() IS 'ASGARD. Fonction qui met à jour les noms des rôles référencés dans la table de gestion.' ;



------ 4.8 - REINITIALISATION DES PRIVILEGES SUR UN SCHEMA ------

-- FUNCTION: z_asgard.asgard_initialise_schema(text, boolean, boolean)

CREATE OR REPLACE FUNCTION z_asgard.asgard_initialise_schema(
                              n_schema text,
                              b_preserve boolean DEFAULT False,
                              b_gs boolean default False
                              )
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction permet de réinitialiser les droits
           sur un schéma selon les privilèges standards associés
           aux rôles désignés dans la table de gestion.
           Si elle est appliquée à un schéma existant non référencé
           dans la table de gestion, elle l'ajoute avec son
           propriétaire courant. Elle échoue si le schéma n'existe
           pas.
ARGUMENTS :
- n_schema : nom d'un schéma présumé existant ;
- b_preserve (optionnel) : un paramètre booléen. Pour un schéma encore
non référencé (ou pré-référencé comme non-créé) dans la table de gestion une valeur
True signifie que les privilèges des rôles lecteur et éditeur doivent être
ajoutés par dessus les droits actuels. Avec la valeur par défaut False,
les privilèges sont réinitialisés. Ce paramètre est ignoré pour un schéma déjà
référencé comme créé (et les privilèges sont réinitialisés) ;
- b_gs (optionnel) : un booléen indiquant si, dans l'hypothèse où un schéma
serait déjà référencé - nécessairement comme non créé - dans la table de gestion,
c'est le propriétaire du schéma qui doit devenir le "producteur" (False) ou le
producteur de la table de gestion qui doit devenir le propriétaire
du schéma (True). False par défaut. Ce paramètre est ignoré pour un schéma déjà
créé.
SORTIE : '__ REINITIALISATION REUSSIE.' (ou '__INITIALISATION REUSSIE.' pour
un schéma non référencé comme créé avec b_preserve = True) si la requête
s'est exécutée normalement. */
DECLARE
    roles record ;
    r record ;
    c record ;
    n_owner text ;
    k int := 0 ;
    n int ;
    e_mssg text ;
    e_detl text ;
    e_hint text ;
BEGIN
    ------ TESTS PREALABLES ------
    -- schéma système
    IF n_schema ~ ANY(ARRAY['^pg_toast', '^pg_temp', '^pg_catalog$',
                            '^public$', '^information_schema$', '^topology$'])
    THEN
        RAISE EXCEPTION 'FIS1. Opération interdite. Le schéma % est un schéma système.', n_schema ;
    END IF ;
    
    -- existence du schéma
    SELECT replace(nspowner::regrole::text, '"', '') INTO n_owner
        FROM pg_catalog.pg_namespace
        WHERE n_schema = nspname::text ;
    IF NOT FOUND
    THEN
        RAISE EXCEPTION 'FIS2. Echec. Le schéma % n''existe pas.', n_schema ;
    END IF ;
    
    -- permission sur le propriétaire
    IF NOT pg_has_role(n_owner, 'USAGE')
    THEN
        RAISE EXCEPTION 'FIS3. Echec. Vous ne disposez pas des permissions nécessaires sur le schéma % pour réaliser cette opération.', n_schema
            USING HINT = 'Il vous faut être membre du rôle propriétaire ' || n_owner || '.' ;
    END IF ;
    
    ------ RECUPERATION DES ROLES ------
    SELECT
        r1.rolname AS producteur,
        CASE WHEN editeur = 'public' THEN 'public' ELSE r2.rolname END AS editeur,
        CASE WHEN lecteur = 'public' THEN 'public' ELSE r3.rolname END AS lecteur,
        creation INTO roles
        FROM z_asgard.gestion_schema_etr
            LEFT JOIN pg_catalog.pg_roles AS r1 ON r1.oid = oid_producteur
            LEFT JOIN pg_catalog.pg_roles AS r2 ON r2.oid = oid_editeur
            LEFT JOIN pg_catalog.pg_roles AS r3 ON r3.oid = oid_lecteur
        WHERE nom_schema = n_schema ;
    
    ------ SCHEMA NON REFERENCE ------
    -- ajouté à gestion_schema
    -- le reste est pris en charge par le trigger
    -- on_modify_gestion_schema_after
    IF NOT FOUND
    THEN
        INSERT INTO z_asgard.gestion_schema_usr (nom_schema, producteur, creation)
            VALUES (n_schema, n_owner, true) ;
        RAISE NOTICE '... Le schéma % a été enregistré dans la table de gestion.', n_schema ;
        
        IF b_preserve
        THEN
            RETURN '__ INITIALISATION REUSSIE.' ;
        END IF ;
        
    ------- SCHEMA PRE-REFERENCE ------
    -- présent dans gestion_schema avec creation valant
    -- False.
    ELSIF NOT roles.creation
    THEN
        IF NOT b_gs
        THEN
            UPDATE z_asgard.gestion_schema_usr
                SET creation = true,
                    producteur = n_owner
                WHERE n_schema = nom_schema ;
        ELSE
            UPDATE z_asgard.gestion_schema_usr
                SET creation = true
                WHERE n_schema = nom_schema ;
        END IF ;
        RAISE NOTICE '... Le schéma % a été marqué comme créé dans la table de gestion.', item.nspname::text ;
        
        IF b_preserve
        THEN
            RETURN '__ INITIALISATION REUSSIE.' ;
        END IF ;
        
    ------ REMISE A PLAT DES PROPRIETAIRES ------
    -- uniquement pour les schémas qui étaient déjà
    -- référencés dans gestion_schema (pour les autres, pris en charge
    -- par le trigger on_modify_gestion_schema_after)
    
    -- schéma dont le propriétaire ne serait pas le producteur
    ELSIF NOT roles.producteur = n_owner
    THEN
        -- permission sur le producteur
        IF NOT pg_has_role(roles.producteur, 'USAGE')
        THEN
            RAISE EXCEPTION 'FIS4. Echec. Vous ne disposez pas des permissions nécessaires sur le schéma % pour réaliser cette opération.', n_schema
                USING HINT = 'Il vous faut être membre du rôle producteur ' || roles.producteur || '.' ;
        END IF ;
        -- propriétaire du schéma + contenu
        RAISE NOTICE '(ré)attribution de la propriété du schéma et des objets au rôle producteur du schéma :' ;
        PERFORM z_asgard.asgard_admin_proprietaire(n_schema, roles.producteur) ;
    
    -- schema dont le propriétaire est le producteur
    ELSE
        -- reprise uniquement des propriétaires du contenu
        RAISE NOTICE '(ré)attribution de la propriété des objets au rôle producteur du schéma :' ;
        SELECT z_asgard.asgard_admin_proprietaire(n_schema, roles.producteur, False) INTO n ;
        IF n = 0
        THEN
            RAISE NOTICE '> néant' ;
        END IF ;        
    END IF ;
    
    ------ DESTRUCTION DES PRIVILEGES ACTUELS ------
    -- hors privilèges par défaut (définis par ALTER DEFAULT PRIVILEGE)
    -- et hors révocations des privilèges par défaut de public sur
    -- les types et les fonctions
    -- pour le propriétaire, ces commandes ont pour effet
    -- de remettre les privilèges par défaut supprimés
    
    -- public
    RAISE NOTICE 'remise à zéro des privilèges manuels du pseudo-rôle public :' ;
    FOR c IN (SELECT * FROM z_asgard.asgard_synthese_public(
                    quote_ident(n_schema)::regnamespace))
    LOOP
        EXECUTE format(z_asgard.asgard_grant_to_revoke(c.commande), 'public') ;
        RAISE NOTICE '> %', format(z_asgard.asgard_grant_to_revoke(c.commande), 'public') ;
    END LOOP ;
    IF NOT FOUND
    THEN
        RAISE NOTICE '> néant' ;
    END IF ;
    
    -- autres rôles
    RAISE NOTICE 'remise à zéro des privilèges des autres rôles (pour le producteur, les éventuels privilèges manquants sont réattribués) :' ;
    FOR r IN (SELECT rolname FROM pg_roles)
    LOOP
        FOR c IN (SELECT * FROM z_asgard.asgard_synthese_role(
                       quote_ident(n_schema)::regnamespace, quote_ident(r.rolname)::regrole))
        LOOP
            EXECUTE format(z_asgard.asgard_grant_to_revoke(c.commande), r.rolname) ;
            RAISE NOTICE '> %', format(z_asgard.asgard_grant_to_revoke(c.commande), r.rolname) ;
            k := k + 1 ;
        END LOOP ;        
    END LOOP ;
    IF NOT FOUND OR k = 0
    THEN
        RAISE NOTICE '> néant' ;
    END IF ;

    ------ RECREATION DES PRIVILEGES DE L'EDITEUR ------
    IF roles.editeur IS NOT NULL
    THEN
        RAISE NOTICE 'application des privilèges standards pour le rôle éditeur du schéma :' ;
        
        EXECUTE 'GRANT USAGE ON SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.editeur) ;
        RAISE NOTICE '> %', 'GRANT USAGE ON SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.editeur) ;
        
        EXECUTE 'GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.editeur) ;
        RAISE NOTICE '> %', 'GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.editeur) ;
        
        EXECUTE 'GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.editeur) ;
        RAISE NOTICE '> %', 'GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.editeur) ;
    END IF ;
    
    ------ RECREATION DES PRIVILEGES DU LECTEUR ------
    IF roles.lecteur IS NOT NULL
    THEN
        RAISE NOTICE 'application des privilèges standards pour le rôle lecteur du schéma :' ;
        
        EXECUTE 'GRANT USAGE ON SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.lecteur) ;
        RAISE NOTICE '> %', 'GRANT USAGE ON SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.lecteur) ;
        
        EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.lecteur) ;
        RAISE NOTICE '> %', 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.lecteur) ;
        
        EXECUTE 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.lecteur) ;
        RAISE NOTICE '> %', 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA ' || quote_ident(n_schema) || ' TO ' || quote_ident(roles.lecteur) ;
    END IF ;
    
    ------ RECREATION DES PRIVILEGES SUR LES SCHEMAS D'ASGARD ------
    IF n_schema = 'z_asgard' AND (roles.lecteur IS NULL OR NOT roles.lecteur = 'g_consult')
    THEN
        -- rétablissement des droits de g_consult
        RAISE NOTICE 'rétablissement des privilèges attendus pour g_consult :' ;
        
        GRANT USAGE ON SCHEMA z_asgard TO g_consult ;
        RAISE NOTICE '> GRANT USAGE ON SCHEMA z_asgard TO g_consult' ;
        
        GRANT SELECT ON TABLE z_asgard.gestion_schema_usr TO g_consult ;
        RAISE NOTICE '> GRANT SELECT ON TABLE z_asgard.gestion_schema_usr TO g_consult' ;
        
        GRANT SELECT ON TABLE z_asgard.gestion_schema_etr TO g_consult ;
        RAISE NOTICE '> GRANT SELECT ON TABLE z_asgard.gestion_schema_etr TO g_consult' ;
        
        GRANT SELECT ON TABLE z_asgard.qgis_menubuilder_metadata TO g_consult ;
        RAISE NOTICE '> GRANT SELECT ON TABLE z_asgard.qgis_menubuilder_metadata TO g_consult' ;
    
    ELSIF n_schema = 'z_asgard_admin'
    THEN
        -- rétablissement des droits de g_admin_ext
        RAISE NOTICE 'rétablissement des privilèges attendus pour g_admin_ext :' ;
        
        GRANT USAGE ON SCHEMA z_asgard_admin TO g_admin_ext ;
        RAISE NOTICE '> GRANT USAGE ON SCHEMA z_asgard_admin TO g_admin_ext' ;
        
        GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE z_asgard_admin.gestion_schema TO g_admin_ext ;
        RAISE NOTICE '> GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE z_asgard_admin.gestion_schema TO g_admin_ext' ;
        
        GRANT SELECT ON TABLE z_asgard_admin.asgard_parametre TO g_admin_ext ;
        RAISE NOTICE '> GRANT SELECT ON TABLE z_asgard_admin.asgard_parametre TO g_admin_ext' ;
        
    END IF ;
                
    RETURN '__ REINITIALISATION REUSSIE.' ;
    
EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
                            e_hint = PG_EXCEPTION_HINT,
                            e_detl = PG_EXCEPTION_DETAIL ;
    RAISE EXCEPTION 'FIS0 > %', e_mssg
        USING DETAIL = e_detl,
            HINT = e_hint ;
    
END
$_$;

ALTER FUNCTION z_asgard.asgard_initialise_schema(text, boolean, boolean)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_initialise_schema(text, boolean, boolean) IS 'ASGARD. Fonction qui réinitialise les privilèges sur un schéma (et l''ajoute à la table de gestion s''il n''y est pas déjà).' ;


------ 4.9 - REINITIALISATION DES PRIVILEGES SUR UN OBJET ------

-- FUNCTION: z_asgard.asgard_initialise_obj(text, text, text)

CREATE OR REPLACE FUNCTION z_asgard.asgard_initialise_obj(
                              obj_schema text,
                              obj_nom text,
                              obj_typ text
                              )
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction permet de réinitialiser les droits
           sur un objet selon les privilèges standards associés
           aux rôles désignés dans la table de gestion pour son schéma.

ARGUMENTS :
- "obj_schema" est le nom du schéma contenant l'objet, au format
texte et sans guillemets ;
- "obj_nom" est le nom de l'objet, au format texte et sans
guillemets ;
- "obj_typ" est le type de l'objet au format text ('table',
'partitioned table' (assimilé à 'table'), 'view', 'materialized view',
'foreign table', 'sequence', 'function', 'aggregate', 'type', 'domain').
SORTIE : '__ REINITIALISATION REUSSIE.' si la requête s'est exécutée
normalement. */
DECLARE
    class_info record ;
    roles record ;
    obj record ;
    r record ;
    c record ;
    l text ;
    k int := 0 ;
BEGIN

    -- pour la suite, on assimile les partitions à des tables
    IF obj_typ = 'partitioned table'
    THEN
        obj_typ := 'table' ;
    END IF ;

    ------ TESTS PREALABLES ------
    -- schéma système
    IF obj_schema ~ ANY(ARRAY['^pg_toast', '^pg_temp', '^pg_catalog$',
                            '^public$', '^information_schema$', '^topology$'])
    THEN
        RAISE EXCEPTION 'FIO1. Opération interdite. Le schéma % est un schéma système.', obj_schema ;
    END IF ;
    
    -- schéma non référencé
    IF NOT obj_schema IN (SELECT nom_schema FROM z_asgard.gestion_schema_usr WHERE creation)
    THEN
        RAISE EXCEPTION 'FIO2. Echec. Le schéma % n''est pas référencé dans la table de gestion (ou marqué comme non créé).', obj_schema ;
    END IF ;
    
    -- type invalide + récupération des informations sur le catalogue contenant l'objet
    SELECT
        xtyp, xclass, xprefix || 'name' AS xname, xprefix || 'owner' AS xowner,
        xprefix || 'namespace' AS xschema
        INTO class_info
        FROM unnest(ARRAY['table', 'foreign table', 'view', 'materialized view',
                        'sequence', 'type', 'domain', 'function', 'aggregate'],
                    ARRAY['pg_class', 'pg_class', 'pg_class', 'pg_class',
                        'pg_class', 'pg_type', 'pg_type', 'pg_proc', 'pg_proc'],
                    ARRAY['rel', 'rel', 'rel', 'rel', 'rel', 'typ', 'typ',
                        'pro', 'pro']) AS typ (xtyp, xclass, xprefix)
            WHERE typ.xtyp = obj_typ ;
            
    IF NOT FOUND
    THEN
        RAISE EXCEPTION 'FIO3. Echec. Le type % n''existe pas ou n''est pas pris en charge.', obj_typ
            USING HINT = 'Types acceptés : ''table'', ''partitioned table'' (assimilé à ''table''), ''view'', ''materialized view'', ''foreign table'', ''sequence'', ''function'', ''aggregate'', ''type'', ''domain''.' ;
    END IF ;
        
    -- objet inexistant + récupération du propriétaire
    EXECUTE 'SELECT ' || class_info.xowner || '::regrole::text AS prop, '
            || class_info.xclass || '.oid FROM pg_catalog.' || class_info.xclass
            || ' WHERE ' || class_info.xname || ' = ' || quote_literal(obj_nom) 
            || ' AND ' || class_info.xschema || '::regnamespace::text = quote_ident('
            || quote_literal(obj_schema) || ')'
        INTO obj ;
            
    IF obj.prop IS NULL
    THEN
        RAISE EXCEPTION 'FIO4. Echec. L''objet % n''existe pas.', obj_nom ;
    END IF ;    
    
    ------ RECUPERATION DES ROLES ------
    SELECT
        r1.rolname AS producteur,
        CASE WHEN editeur = 'public' THEN 'public' ELSE r2.rolname END AS editeur,
        CASE WHEN lecteur = 'public' THEN 'public' ELSE r3.rolname END AS lecteur,
        creation INTO roles
        FROM z_asgard.gestion_schema_etr
            LEFT JOIN pg_catalog.pg_roles AS r1 ON r1.oid = oid_producteur
            LEFT JOIN pg_catalog.pg_roles AS r2 ON r2.oid = oid_editeur
            LEFT JOIN pg_catalog.pg_roles AS r3 ON r3.oid = oid_lecteur
        WHERE nom_schema = obj_schema ;
            
    -- permission sur le producteur
    IF NOT pg_has_role(roles.producteur, 'USAGE')
    THEN
        RAISE EXCEPTION 'FIO5. Echec. Vous ne disposez pas des permissions nécessaires sur le schéma % pour réaliser cette opération.', obj_schema
            USING HINT = 'Il vous faut être membre du rôle producteur ' || roles.producteur || '.' ;
    END IF ;
    
    ------ REMISE A PLAT DU PROPRIETAIRE ------
    IF NOT obj.prop = roles.producteur
    THEN
        -- permission sur le propriétaire de l'objet
        IF NOT pg_has_role(obj.prop, 'USAGE')
        THEN
            RAISE EXCEPTION 'FIO6. Echec. Vous ne disposez pas des permissions nécessaires sur l''objet % pour réaliser cette opération.', obj_nom
                USING HINT = 'Il vous faut être membre du rôle propriétaire de l''objet (' || obj.prop || ').' ;
        END IF ;
        
        RAISE NOTICE 'réattribution de la propriété de % au rôle producteur du schéma :', obj_nom ;
        l := 'ALTER ' || obj_typ || ' ' || quote_ident(obj_schema) || '.' || quote_ident(obj_nom)||
                ' OWNER TO '  || quote_ident(roles.producteur) ;
        EXECUTE l ;
        RAISE NOTICE '> %', l ;
    END IF ;    
    
    ------ DESTRUCTION DES PRIVILEGES ACTUELS ------
    -- hors privilèges par défaut (définis par ALTER DEFAULT PRIVILEGE)
    -- et hors révocations des privilèges par défaut de public sur
    -- les types et les fonctions
    -- pour le propriétaire, ces commandes ont pour effet
    -- de remettre les privilèges par défaut supprimés
    
    -- public
    RAISE NOTICE 'remise à zéro des privilèges manuels du pseudo-rôle public :' ;
    FOR c IN (SELECT * FROM z_asgard.asgard_synthese_public_obj(obj.oid, obj_typ))
    LOOP
        EXECUTE format(z_asgard.asgard_grant_to_revoke(c.commande), 'public') ;
        RAISE NOTICE '> %', format(z_asgard.asgard_grant_to_revoke(c.commande), 'public') ;
    END LOOP ;
    IF NOT FOUND
    THEN
        RAISE NOTICE '> néant' ;
    END IF ;

    -- autres rôles
    RAISE NOTICE 'remise à zéro des privilèges des autres rôles (pour le producteur, les éventuels privilèges manquants sont réattribués) :' ;
    FOR r IN (SELECT rolname FROM pg_roles)
    LOOP
        FOR c IN (SELECT * FROM z_asgard.asgard_synthese_role_obj(
                        obj.oid, obj_typ, quote_ident(r.rolname)::regrole))
        LOOP
            EXECUTE format(z_asgard.asgard_grant_to_revoke(c.commande), r.rolname) ;
            RAISE NOTICE '> %', format(z_asgard.asgard_grant_to_revoke(c.commande), r.rolname) ;
            k := k + 1 ;
        END LOOP ;        
    END LOOP ;
    IF NOT FOUND OR k = 0
    THEN
        RAISE NOTICE '> néant' ;
    END IF ;

    ------ RECREATION DES PRIVILEGES DE L'EDITEUR ------
    IF roles.editeur IS NOT NULL
    THEN
        -- sur les tables :
        IF obj_typ IN ('table', 'view', 'materialized view', 'foreign table')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle éditeur du schéma :' ;
            l := 'GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE '
                    || quote_ident(obj_schema) || '.' || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles.editeur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        -- sur les séquences :
        ELSIF obj_typ IN ('sequence')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle éditeur du schéma :' ;
            l := 'GRANT SELECT, USAGE ON SEQUENCE '
                    || quote_ident(obj_schema) || '.' || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles.editeur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        END IF ;        
    END IF ;
    
    ------ RECREATION DES PRIVILEGES DU LECTEUR ------
    IF roles.lecteur IS NOT NULL
    THEN
        -- sur les tables :
        IF obj_typ IN ('table', 'view', 'materialized view', 'foreign table')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle lecteur du schéma :' ;
            l := 'GRANT SELECT ON TABLE ' || quote_ident(obj_schema) || '.'
                    || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles.lecteur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        -- sur les séquences :
        ELSIF obj_typ IN ('sequence')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle lecteur du schéma :' ;
            l := 'GRANT SELECT ON SEQUENCE ' || quote_ident(obj_schema) || '.'
                    || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles.lecteur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        END IF ;
    END IF ;
                
    RETURN '__ REINITIALISATION REUSSIE.' ;
END
$_$;

ALTER FUNCTION z_asgard.asgard_initialise_obj(text, text, text)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_initialise_obj(text, text, text) IS 'ASGARD. Fonction qui réinitialise les privilèges sur un objet.' ;


------ 4.10 - DEPLACEMENT D'OBJET ------

-- FUNCTION: z_asgard.asgard_deplace_obj(text, text, text, text, int)

CREATE OR REPLACE FUNCTION z_asgard.asgard_deplace_obj(
                                obj_schema text,
                                obj_nom text,
                                obj_typ text,
                                schema_cible text,
                                variante int DEFAULT 1
                                )
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction permet de déplacer un objet vers un nouveau
           schéma en spécifiant la gestion voulue sur les droits de
           l'objet : transfert ou réinitialisation des privilèges.
           Dans le cas d'une table avec un ou plusieurs champs de
           type serial, elle prend aussi en charge les privilèges
           sur les séquences associées.
ARGUMENTS :
- "obj_schema" est le nom du schéma contenant l'objet, au format
texte et sans guillemets ;
- "obj_nom" est le nom de l'objet, au format texte et sans
guillemets ;
- "obj_typ" est le type de l'objet au format text ('table',
'partitioned table' (assimilé à 'table'), 'view', 'materialized view',
'foreign table', 'sequence', 'function', 'aggregate', 'type', 'domain') ;
- "schema_cible" est le nom du schéma où doit être déplacé l'objet,
au format texte et sans guillemets ;
- "variante" [optionnel] est un entier qui définit le comportement
attendu par l'utilisateur vis à vis des privilèges :
    - 1 (valeur par défaut) | TRANSFERT COMPLET + CONSERVATION :
    les privilèges des rôles producteur, éditeur et lecteur de
    l'ancien schéma sont transférés sur ceux du nouveau. Si un
    éditeur ou lecteur a été désigné pour le nouveau schéma mais
    qu'aucun n'était défini pour l'ancien, le rôle reçoit les
    privilèges standards pour sa fonction. Le cas échéant,
    les privilèges des autres rôles sont conservés ;
    - 2 | REINITIALISATION COMPLETE : les nouveaux
    producteur, éditeur et lecteur reçoivent les privilèges
    standard. Les privilèges des autres rôles sont supprimés ;
    - 3 | TRANSFERT COMPLET + NETTOYAGE : les privilèges des rôles
    producteur, éditeur et lecteur de l'ancien schéma sont transférés
    sur ceux du nouveau. Si un éditeur ou lecteur a été désigné pour
    le nouveau schéma mais qu'aucun n'était défini pour l'ancien,
    le rôle reçoit les privilèges standards pour sa fonction.
    Les privilèges des autres rôles sont supprimés ;
    - 4 | TRANSFERT PRODUCTEUR + CONSERVATION : les privilèges de
    l'ancien producteur sont transférés sur le nouveau. Les privilèges
    des autres rôles sont conservés tels quels. C'est le comportement
    d'une commande ALTER [...] SET SCHEMA (interceptée par l'event
    trigger asgard_on_alter_objet) ;
    - 5 | TRANSFERT PRODUCTEUR + REINITIALISATION : les privilèges
    de l'ancien producteur sont transférés sur le nouveau. Les
    nouveaux éditeur et lecteur reçoivent les privilèges standards.
    Les privilèges des autres rôles sont supprimés ;
    - 6 | REINITIALISATION PARTIELLE : les nouveaux
    producteur, éditeur et lecteur reçoivent les privilèges
    standard. Les privilèges des autres rôles sont conservés.
SORTIE : '__ DEPLACEMENT REUSSI.' si la requête s'est exécutée normalement. */
DECLARE
    class_info record ;
    roles record ;
    roles_cible record ;
    obj record ;
    r record ;
    c record ;
    l text ;
    c_lecteur text[] ;
    c_editeur text[] ;
    c_producteur text[] ;
    c_n_lecteur text[] ;
    c_n_editeur text[] ;
    c_autres text[] ;
    seq_liste oid[] ;
    a text[] ;
    s record ;
    o oid ;
BEGIN

    -- pour la suite, on assimile les partitions à des tables
    IF obj_typ = 'partitioned table'
    THEN
        obj_typ := 'table' ;
    END IF ;

    ------ TESTS PREALABLES ------
    -- schéma système
    IF obj_schema ~ ANY(ARRAY['^pg_toast', '^pg_temp', '^pg_catalog$',
                            '^public$', '^information_schema$', '^topology$'])
    THEN
        RAISE EXCEPTION 'FDO1. Opération interdite. Le schéma % est un schéma système.', obj_schema ;
    END IF ;
    
    -- schéma de départ non référencé
    IF NOT obj_schema IN (SELECT nom_schema FROM z_asgard.gestion_schema_usr WHERE creation)
    THEN
        RAISE EXCEPTION 'FDO2. Echec. Le schéma % n''est pas référencé dans la table de gestion (ou marqué comme non créé).', obj_schema ;
    END IF ;
    
    -- schéma cible non référencé
    IF NOT schema_cible IN (SELECT nom_schema FROM z_asgard.gestion_schema_usr WHERE creation)
    THEN
        RAISE EXCEPTION 'FDO3. Echec. Le schéma cible % n''est pas référencé dans la table de gestion (ou marqué comme non créé).', schema_cible ;
    END IF ;
    
    -- type invalide + récupération des informations sur le catalogue contenant l'objet
    SELECT
        xtyp, xclass, xprefix || 'name' AS xname, xprefix || 'owner' AS xowner,
        xprefix || 'namespace' AS xschema
        INTO class_info
        FROM unnest(ARRAY['table', 'foreign table', 'view', 'materialized view',
                        'sequence', 'type', 'domain', 'function', 'aggregate'],
                    ARRAY['pg_class', 'pg_class', 'pg_class', 'pg_class',
                        'pg_class', 'pg_type', 'pg_type', 'pg_proc', 'pg_proc'],
                    ARRAY['rel', 'rel', 'rel', 'rel', 'rel', 'typ', 'typ',
                        'pro', 'pro']) AS typ (xtyp, xclass, xprefix)
            WHERE typ.xtyp = obj_typ ;
            
    IF NOT FOUND
    THEN
        RAISE EXCEPTION 'FDO4. Echec. Le type % n''existe pas ou n''est pas pris en charge.', obj_typ
            USING HINT = 'Types acceptés : ''table'', ''partitioned table'' (assimilé à ''table''), ''view'', ''materialized view'', ''foreign table'', ''sequence'', ''function'', ''aggregate'', ''type'', ''domain''.' ;
    END IF ;
        
    -- objet inexistant + récupération du propriétaire
    EXECUTE 'SELECT ' || class_info.xowner || '::regrole::text AS prop, '
            || class_info.xclass || '.oid FROM pg_catalog.' || class_info.xclass
            || ' WHERE ' || class_info.xname || ' = ' || quote_literal(obj_nom) 
            || ' AND ' || class_info.xschema || '::regnamespace::text = quote_ident('
            || quote_literal(obj_schema) || ')'
        INTO obj ;
            
    IF obj.prop IS NULL
    THEN
        RAISE EXCEPTION 'FDO5. Echec. L''objet % n''existe pas.', obj_nom ;
    END IF ;
    
    ------ RECUPERATION DES ROLES ------
    -- schéma de départ :
    SELECT
        r1.rolname AS producteur,
        CASE WHEN editeur = 'public' THEN 'public' ELSE r2.rolname END AS editeur,
        CASE WHEN lecteur = 'public' THEN 'public' ELSE r3.rolname END AS lecteur,
        creation INTO roles
        FROM z_asgard.gestion_schema_etr
            LEFT JOIN pg_catalog.pg_roles AS r1 ON r1.oid = oid_producteur
            LEFT JOIN pg_catalog.pg_roles AS r2 ON r2.oid = oid_editeur
            LEFT JOIN pg_catalog.pg_roles AS r3 ON r3.oid = oid_lecteur
        WHERE nom_schema = obj_schema ;
        
    -- schéma cible :
    SELECT
        r1.rolname AS producteur,
        CASE WHEN editeur = 'public' THEN 'public' ELSE r2.rolname END AS editeur,
        CASE WHEN lecteur = 'public' THEN 'public' ELSE r3.rolname END AS lecteur,
        creation INTO roles_cible
        FROM z_asgard.gestion_schema_etr
            LEFT JOIN pg_catalog.pg_roles AS r1 ON r1.oid = oid_producteur
            LEFT JOIN pg_catalog.pg_roles AS r2 ON r2.oid = oid_editeur
            LEFT JOIN pg_catalog.pg_roles AS r3 ON r3.oid = oid_lecteur
        WHERE nom_schema = schema_cible ;
            
    -- permission sur le producteur du schéma cible
    IF NOT pg_has_role(roles_cible.producteur, 'USAGE')
    THEN
        RAISE EXCEPTION 'FDO6. Echec. Vous ne disposez pas des permissions nécessaires sur le schéma cible % pour réaliser cette opération.', schema_cible
            USING HINT = 'Il vous faut être membre du rôle producteur ' || roles_cible.producteur || '.' ;
    END IF ;
    
    -- permission sur le propriétaire de l'objet
    IF NOT pg_has_role(obj.prop, 'USAGE')
    THEN
        RAISE EXCEPTION 'FDO7. Echec. Vous ne disposez pas des permissions nécessaires sur l''objet % pour réaliser cette opération.', obj_nom
            USING HINT = 'Il vous faut être membre du rôle propriétaire de l''objet (' || obj.prop || ').' ;
    END IF ;
    
    ------ MEMORISATION DES PRIVILEGES ACTUELS ------
    -- ancien producteur :
    SELECT array_agg(commande) INTO c_producteur
        FROM z_asgard.asgard_synthese_role_obj(
                obj.oid, obj_typ, quote_ident(roles.producteur)::regrole) ;
    
    -- ancien éditeur :
    IF roles.editeur = 'public'
    THEN
        SELECT array_agg(commande) INTO c_editeur
            FROM z_asgard.asgard_synthese_public_obj(obj.oid, obj_typ) ;
    ELSIF roles.editeur IS NOT NULL
    THEN
        SELECT array_agg(commande) INTO c_editeur
            FROM z_asgard.asgard_synthese_role_obj(
                    obj.oid, obj_typ, quote_ident(roles.editeur)::regrole) ;
    END IF ;
                
    -- ancien lecteur :
    IF roles.lecteur = 'public'
    THEN
        SELECT array_agg(commande) INTO c_lecteur
            FROM z_asgard.asgard_synthese_public_obj(obj.oid, obj_typ) ;
    ELSIF roles.lecteur IS NOT NULL
    THEN
        SELECT array_agg(commande) INTO c_lecteur
            FROM z_asgard.asgard_synthese_role_obj(
                    obj.oid, obj_typ, quote_ident(roles.lecteur)::regrole) ;
    END IF ;
    
    -- nouvel éditeur :
    IF roles_cible.editeur = 'public'
    THEN
        SELECT array_agg(commande) INTO c_n_editeur
            FROM z_asgard.asgard_synthese_public_obj(obj.oid, obj_typ) ;
    ELSIF roles_cible.editeur IS NOT NULL
    THEN
        SELECT array_agg(commande) INTO c_n_editeur
            FROM z_asgard.asgard_synthese_role_obj(
                    obj.oid, obj_typ, quote_ident(roles_cible.editeur)::regrole) ;
    END IF ;
                
    -- nouveau lecteur :
    IF roles_cible.lecteur = 'public'
    THEN
        SELECT array_agg(commande) INTO c_n_lecteur
            FROM z_asgard.asgard_synthese_public_obj(obj.oid, obj_typ) ;
    ELSIF roles_cible.lecteur IS NOT NULL
    THEN
        SELECT array_agg(commande) INTO c_n_lecteur
            FROM z_asgard.asgard_synthese_role_obj(
                    obj.oid, obj_typ, quote_ident(roles_cible.lecteur)::regrole) ;
    END IF ;
    
    -- autres rôles :
    -- pour ces commandes, contrairement aux précédentes, le rôle
    -- est inséré dès maintenant (avec "format")
    -- public
    IF NOT 'public' = ANY (array_remove(ARRAY[roles.producteur, roles.lecteur, roles.editeur,
            roles_cible.producteur, roles_cible.lecteur, roles_cible.editeur], NULL))
    THEN
        SELECT array_agg(format(commande, 'public')) INTO c_autres
            FROM z_asgard.asgard_synthese_public_obj(obj.oid, obj_typ) ;
    END IF ;
    -- et le reste
    FOR r IN (SELECT rolname FROM pg_roles
            WHERE NOT rolname = ANY (array_remove(ARRAY[roles.producteur, roles.lecteur, roles.editeur,
                roles_cible.producteur, roles_cible.lecteur, roles_cible.editeur], NULL)))
    LOOP
        SELECT array_agg(format(commande, r.rolname::text)) INTO a
            FROM z_asgard.asgard_synthese_role_obj(
                    obj.oid, obj_typ, quote_ident(r.rolname)::regrole) ;
        IF FOUND
        THEN
            c_autres := array_cat(c_autres, a) ;
            a := NULL ;
        END IF ;
    END LOOP ;
    
    ------ PRIVILEGES SUR LES SEQUENCES ASSOCIEES ------
    IF obj_typ = 'table'
    THEN
        -- dans le cas d'une table, on recherche les séquences
        -- utilisées par ses éventuels champs de type serial.
        -- elles sont repérées par le fait qu'il existe
        -- une dépendance de type "DEPENDENCY_AUTO" entre
        -- la séquence et un champ de la table
        FOR s IN (
            SELECT
                pg_class.oid
                FROM pg_catalog.pg_depend LEFT JOIN pg_catalog.pg_class
                    ON pg_class.oid = pg_depend.objid
                WHERE pg_depend.classid = 'pg_catalog.pg_class'::regclass::oid
                    AND pg_depend.refclassid = 'pg_catalog.pg_class'::regclass::oid
                    AND pg_depend.refobjid = obj.oid
                    AND pg_depend.refobjsubid > 0
                    AND pg_depend.deptype = 'a'
                    AND pg_class.relkind = 'S'
            )
        LOOP
            -- liste des séquences
            seq_liste := array_append(seq_liste, s.oid) ;
            
            -- récupération des privilèges
            -- ancien producteur :
            SELECT array_agg(commande) INTO a
                FROM z_asgard.asgard_synthese_role_obj(
                        s.oid, 'sequence', quote_ident(roles.producteur)::regrole) ;
            IF FOUND
            THEN
                c_producteur := array_cat(c_producteur, a) ;
                a := NULL ;
            END IF ;
        
            -- ancien éditeur :
            IF roles.editeur = 'public'
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_public_obj(s.oid, 'sequence'::text) ;
            ELSIF roles.editeur IS NOT NULL
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_role_obj(
                            s.oid, 'sequence'::text, quote_ident(roles.editeur)::regrole) ;
            END IF ;
            IF a IS NOT NULL
            THEN
                c_editeur := array_cat(c_editeur, a) ;
                a := NULL ;
            END IF ;
                        
            -- ancien lecteur :
            IF roles.lecteur = 'public'
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_public_obj(s.oid, 'sequence'::text) ;
            ELSIF roles.lecteur IS NOT NULL
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_role_obj(
                            s.oid, 'sequence'::text, quote_ident(roles.lecteur)::regrole) ;
            END IF ;
            IF a IS NOT NULL
            THEN
                c_lecteur := array_cat(c_lecteur, a) ;
                a := NULL ;
            END IF ;
            
            -- nouvel éditeur :
            IF roles_cible.editeur = 'public'
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_public_obj(s.oid, 'sequence'::text) ;
            ELSIF roles_cible.editeur IS NOT NULL
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_role_obj(
                            s.oid, 'sequence'::text, quote_ident(roles_cible.editeur)::regrole) ;
            END IF ;
            IF a IS NOT NULL
            THEN
                c_n_editeur := array_cat(c_n_editeur, a) ;
                a := NULL ;
            END IF ;
                        
            -- nouveau lecteur :
            IF roles_cible.lecteur = 'public'
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_public_obj(s.oid, 'sequence'::text) ;
            ELSIF roles_cible.lecteur IS NOT NULL
            THEN
                SELECT array_agg(commande) INTO a
                    FROM z_asgard.asgard_synthese_role_obj(
                            s.oid, 'sequence'::text, quote_ident(roles_cible.lecteur)::regrole) ;
            END IF ;
            IF a IS NOT NULL
            THEN
                c_n_lecteur := array_cat(c_n_lecteur, a) ;
                a := NULL ;
            END IF ;
            
            -- autres rôles :
            -- public
            IF NOT 'public' = ANY (array_remove(ARRAY[roles.producteur, roles.lecteur, roles.editeur,
                    roles_cible.producteur, roles_cible.lecteur, roles_cible.editeur], NULL))
            THEN
                SELECT array_agg(format(commande, 'public')) INTO a
                    FROM z_asgard.asgard_synthese_public_obj(s.oid, 'sequence'::text) ;
                IF FOUND
                THEN
                    c_autres := array_cat(c_autres, a) ;
                    a := NULL ;
                END IF ;
            END IF ;
            -- et le reste
            FOR r IN (SELECT rolname FROM pg_roles
                    WHERE NOT rolname = ANY (array_remove(ARRAY[roles.producteur, roles.lecteur, roles.editeur,
                        roles_cible.producteur, roles_cible.lecteur, roles_cible.editeur], NULL)))
            LOOP
                SELECT array_agg(format(commande, r.rolname::text)) INTO a
                    FROM z_asgard.asgard_synthese_role_obj(
                            s.oid, 'sequence'::text, quote_ident(r.rolname)::regrole) ;
                IF FOUND
                THEN
                    c_autres := array_cat(c_autres, a) ;
                    a := NULL ;
                END IF ;
            END LOOP ;
        END LOOP ;
    END IF ;
    
    ------ DEPLACEMENT DE L'OBJET ------
    EXECUTE 'ALTER ' || obj_typ || ' ' || quote_ident(obj_schema) || '.' || quote_ident(obj_nom)||
                ' SET SCHEMA '  || quote_ident(schema_cible) ;
                
    RAISE NOTICE '... Objet déplacé dans le schéma %.', schema_cible ;
  
    ------ PRIVILEGES DU PRODUCTEUR ------
    -- par défaut, ils ont été transférés
    -- lors du changement de propriétaire, il
    -- n'y a donc qu'à réinitialiser pour les
    -- variantes 2 et 6
    
    -- objet, réinitialisation pour 2 et 6
    IF variante IN (2, 6) AND (c_producteur IS NOT NULL)
    THEN
        RAISE NOTICE 'réinitialisation des privilèges du nouveau producteur, % :', roles_cible.producteur ;
        FOREACH l IN ARRAY c_producteur
        LOOP
            l := z_asgard.asgard_grant_to_revoke(replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.')) ;
            EXECUTE format(l, roles_cible.producteur) ;
            RAISE NOTICE '> %', format(l, roles_cible.producteur) ;
        END LOOP ;
    END IF ;
    
    ------- PRIVILEGES EDITEUR ------
    -- révocation des privilèges du nouvel éditeur
    IF roles_cible.editeur IS NOT NULL
            AND (roles.editeur IS NULL OR NOT roles.editeur = roles_cible.editeur)
            AND NOT roles.producteur = roles_cible.editeur
            AND NOT variante = 4
            AND c_n_editeur IS NOT NULL
    THEN
        RAISE NOTICE 'suppression des privilèges pré-existants du nouvel éditeur, % :', roles_cible.editeur ;
        FOREACH l IN ARRAY c_n_editeur
        LOOP
            l := z_asgard.asgard_grant_to_revoke(replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.')) ;
            EXECUTE format(l, roles_cible.editeur) ;
            RAISE NOTICE '> %', format(l, roles_cible.editeur) ;  
        END LOOP ;
    END IF ;
    
    -- révocation des privilèges de l'ancien éditeur
    IF roles.editeur IS NOT NULL AND NOT roles.editeur = roles_cible.producteur
            AND (roles_cible.editeur IS NULL OR NOT roles.editeur = roles_cible.editeur OR NOT variante IN (1,3))
            AND NOT variante = 4
            AND c_editeur IS NOT NULL
    THEN
        RAISE NOTICE 'suppression des privilèges de l''ancien éditeur, % :', roles.editeur ;
        FOREACH l IN ARRAY c_editeur
        LOOP
            l := z_asgard.asgard_grant_to_revoke(replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.')) ;
            EXECUTE format(l, roles.editeur) ;
            RAISE NOTICE '> %', format(l, roles.editeur) ;  
        END LOOP ;
    END IF ;
    
    -- reproduction sur le nouvel éditeur pour les variantes 1 et 3
    IF roles.editeur IS NOT NULL
            AND roles_cible.editeur IS NOT NULL
            AND variante IN (1, 3)
            AND c_editeur IS NOT NULL
            AND NOT roles.editeur = roles_cible.editeur
    THEN
        RAISE NOTICE 'transfert des privilèges de l''ancien éditeur vers le nouvel éditeur, % :', roles_cible.editeur ;
        FOREACH l IN ARRAY c_editeur
        LOOP
            l := replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.') ;
            EXECUTE format(l, roles_cible.editeur) ;
            RAISE NOTICE '> %', format(l, roles_cible.editeur) ;  
        END LOOP ;
    END IF ;
    
    -- attribution des privilèges standard au nouvel éditeur
    -- pour les variantes 2, 5, 6
    -- ou s'il n'y avait pas de lecteur sur l'ancien schéma
    IF roles_cible.editeur IS NOT NULL
          AND (variante IN (2, 5, 6) OR roles.editeur IS NULL)
          AND NOT variante = 4
    THEN
        -- sur les tables :
        IF obj_typ IN ('table', 'view', 'materialized view', 'foreign table')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle éditeur du schéma :' ;
            l := 'GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE '
                    || quote_ident(schema_cible) || '.' || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles_cible.editeur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        -- sur les séquences libres :
        ELSIF obj_typ IN ('sequence')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle éditeur du schéma :' ;
            l := 'GRANT SELECT, USAGE ON SEQUENCE '
                    || quote_ident(schema_cible) || '.' || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles_cible.editeur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        END IF ;
        -- sur les séquences des champs serial :
        IF seq_liste IS NOT NULL
        THEN
            FOREACH o IN ARRAY seq_liste
            LOOP
                l := 'GRANT SELECT, USAGE ON SEQUENCE '
                    || o::regclass::text || ' TO ' || quote_ident(roles_cible.editeur) ;
                EXECUTE l ;
                RAISE NOTICE '> %', l ;
            END LOOP ;
        END IF ;
    END IF ;
    
    ------- PRIVILEGES LECTEUR ------
    -- révocation des privilèges du nouveau lecteur
    IF roles_cible.lecteur IS NOT NULL
            AND (roles.lecteur IS NULL OR NOT roles.lecteur = roles_cible.lecteur)
            AND NOT roles.producteur = roles_cible.lecteur
            AND (roles.editeur IS NULL OR NOT roles.editeur = roles_cible.lecteur)
            AND NOT variante = 4
            AND c_n_lecteur IS NOT NULL
    THEN
        RAISE NOTICE 'suppression des privilèges pré-existants du nouveau lecteur, % :', roles_cible.lecteur ;
        FOREACH l IN ARRAY c_n_lecteur
        LOOP
            l := z_asgard.asgard_grant_to_revoke(replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.')) ;
            EXECUTE format(l, roles_cible.lecteur) ;
            RAISE NOTICE '> %', format(l, roles_cible.lecteur) ;  
        END LOOP ;
    END IF ;
    
    -- révocation des privilèges de l'ancien lecteur
    IF roles.lecteur IS NOT NULL AND NOT roles.lecteur = roles_cible.producteur
           AND (roles_cible.editeur IS NULL OR NOT roles.lecteur = roles_cible.editeur)
           AND (roles_cible.lecteur IS NULL OR NOT roles.lecteur = roles_cible.lecteur OR NOT variante IN (1,3))
           AND NOT variante = 4
           AND c_lecteur IS NOT NULL
    THEN
        RAISE NOTICE 'suppression des privilèges de l''ancien lecteur, % :', roles.lecteur ;
        FOREACH l IN ARRAY c_lecteur
        LOOP
            l := z_asgard.asgard_grant_to_revoke(replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.')) ;
            EXECUTE format(l, roles.lecteur) ;
            RAISE NOTICE '> %', format(l, roles.lecteur) ;  
        END LOOP ;
    END IF ;
    
    -- reproduction sur le nouveau lecteur pour les variantes 1 et 3
    IF roles.lecteur IS NOT NULL
            AND roles_cible.lecteur IS NOT NULL
            AND variante IN (1, 3)
            AND c_lecteur IS NOT NULL
            AND NOT roles.lecteur = roles_cible.lecteur
    THEN
        RAISE NOTICE 'transfert des privilèges de l''ancien lecteur vers le nouveau lecteur, % :', roles_cible.lecteur ;
        FOREACH l IN ARRAY c_lecteur
        LOOP
            l := replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.') ;
            EXECUTE format(l, roles_cible.lecteur) ;
            RAISE NOTICE '> %', format(l, roles_cible.lecteur) ;  
        END LOOP ;
    END IF ;
    
    -- attribution des privilèges standard au nouveau lecteur
    -- pour les variantes 2, 5, 6
    -- ou s'il n'y avait pas de lecteur sur l'ancien schéma
    IF roles_cible.lecteur IS NOT NULL
          AND (variante IN (2, 5, 6) OR roles.lecteur IS NULL)
          AND NOT variante = 4
    THEN
        -- sur les tables :
        IF obj_typ IN ('table', 'view', 'materialized view', 'foreign table')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle lecteur du schéma :' ;
            l := 'GRANT SELECT ON TABLE ' || quote_ident(schema_cible) || '.'
                    || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles_cible.lecteur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        -- sur les séquences libres :
        ELSIF obj_typ IN ('sequence')
        THEN
            RAISE NOTICE 'application des privilèges standards pour le rôle lecteur du schéma :' ;
            l := 'GRANT SELECT ON SEQUENCE ' || quote_ident(schema_cible) || '.'
                    || quote_ident(obj_nom) ||
                    ' TO ' || quote_ident(roles_cible.lecteur) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;
        END IF ; 
        -- sur les séquences des champs serial :
        IF seq_liste IS NOT NULL
        THEN
            FOREACH o IN ARRAY seq_liste
            LOOP
                l := 'GRANT SELECT ON SEQUENCE '
                    || o::regclass::text || ' TO ' || quote_ident(roles_cible.lecteur) ;
                EXECUTE l ;
                RAISE NOTICE '> %', l ;
            END LOOP ;
        END IF ;
    END IF ;
    
    ------ AUTRES ROLES ------
    -- pour les variantes 2, 3, 5, remise à zéro
    IF variante IN (2, 3, 5)
        AND c_autres IS NOT NULL
    THEN
        RAISE NOTICE 'remise à zéro des privilèges des autres rôles :' ;
        FOREACH l IN ARRAY c_autres
        LOOP
            l := z_asgard.asgard_grant_to_revoke(replace(l, quote_ident(obj_schema) || '.', quote_ident(schema_cible) || '.')) ;
            EXECUTE l ;
            RAISE NOTICE '> %', l ;  
        END LOOP ;    
    END IF ;

    RETURN '__ DEPLACEMENT REUSSI.' ;
END
$_$;

ALTER FUNCTION z_asgard.asgard_deplace_obj(text, text, text, text, int)
    OWNER TO g_admin_ext ;

COMMENT ON FUNCTION z_asgard.asgard_deplace_obj(text, text, text, text, int) IS 'ASGARD. Fonction qui prend en charge le déplacement d''un objet dans un nouveau schéma, avec une gestion propre des privilèges.' ;



------ 4.11 - OCTROI D'UN RÔLE À TOUS LES RÔLES DE CONNEXION ------

-- FUNCTION: z_asgard_admin.asgard_all_login_grant_role(text, boolean)

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_all_login_grant_role(n_role text, b boolean DEFAULT True)
    RETURNS int
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction confère à tous les rôles de connexion du
           serveur l'appartenance au rôle donné en argument.
ARGUMENTS :
- n_role : une chaîne de caractères présumée correspondre à un nom de
  rôle valide ;
- b : [optionnel] un booléen. Si b vaut False et qu'un rôle de connexion est
déjà membre du rôle considéré par héritage, la fonction ne fait rien. Si
b vaut True (défaut), la fonction ne passera un rôle de connexion que s'il est
lui-même membre du rôle considéré.
SORTIE : un entier correspondant au nombre de rôles pour lesquels
la permission a été accordée. */
DECLARE
    roles record ;
    attributeur text ;
    utilisateur text := current_user ;
    c text ;
    n int := 0 ;
BEGIN
    ------ TESTS PREALABLES -----
    -- existance du rôle
    IF NOT n_role IN (SELECT rolname FROM pg_catalog.pg_roles)
    THEN
        RAISE EXCEPTION 'FLG1. Echec. Le rôle % n''existe pas', n_role ;
    END IF ;
    
    -- on cherche un rôle dont l'utilisateur est
    -- membre et qui, soit a l'attribut CREATEROLE
    -- soit a ADMIN OPTION sur le rôle
    SELECT rolname INTO attributeur
        FROM pg_roles
        WHERE pg_has_role(rolname, 'MEMBER') AND rolcreaterole
        ORDER BY rolname = current_user DESC ;
    IF NOT FOUND
    THEN
        SELECT grantee INTO attributeur
            FROM information_schema.applicable_roles
            WHERE is_grantable = 'YES' AND role_name = n_role ;
        IF NOT FOUND
        THEN
            RAISE EXCEPTION 'FLG2. Opération interdite. Permissions insuffisantes pour le rôle %.', n_role
                USING HINT = 'Votre rôle doit être membre de ' || n_role
                            || ' avec admin option ou disposer de l''attribut CREATEROLE pour réaliser cette opération.' ;
        END IF ;
    END IF ;
    
    EXECUTE 'SET ROLE ' || quote_ident(attributeur) ;
    
    IF b
    THEN
        FOR roles IN SELECT rolname
                        FROM pg_roles LEFT JOIN pg_auth_members
                                ON member = pg_roles.oid AND roleid = n_role::regrole::oid
                        WHERE rolcanlogin AND member IS NULL
                            AND NOT rolsuper
        LOOP
            c := 'GRANT ' || quote_ident(n_role) || ' TO ' || quote_ident(roles.rolname) ;
            EXECUTE c ;
            RAISE NOTICE '> %', c ;
            n := n + 1 ;
        END LOOP ;
    ELSE
        FOR roles IN SELECT rolname FROM pg_roles
                        WHERE rolcanlogin AND NOT pg_has_role(rolname, n_role, 'MEMBER')
        LOOP
            c := 'GRANT ' || quote_ident(n_role) || ' TO ' || quote_ident(roles.rolname) ;
            EXECUTE c ;
            RAISE NOTICE '> %', c ;
            n := n + 1 ;
        END LOOP ;
    END IF ;
    
    EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
    
    RETURN n ;
END
$_$;

ALTER FUNCTION z_asgard_admin.asgard_all_login_grant_role(text, boolean)
    OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_all_login_grant_role(text, boolean) IS 'ASGARD. Fonction qui confère à tous les rôles de connexion du serveur l''appartenance au rôle donné en argument.' ;



------ 4.12 - IMPORT DE LA NOMENCLATURE DANS GESTION_SCHEMA ------

-- FUNCTION: z_asgard_admin.asgard_import_nomenclature(text[])

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_import_nomenclature(
                           domaines text[] default NULL::text[]
                           )
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Fonction qui importe dans la table de gestion les schémas manquants
           de la nomenclature nationale - ou de certains domaines
           de la nomenclature nationale listés en argument - toujours avec
           creation valant False, même si le schéma existe (mais n'a pas été
           référencé).
           Des messages informent l'opérateur des schémas effectivement ajoutés.
           Lorsque le schéma est déjà référencé dans la table de gestion, réappliquer
           la fonction a pour effet de mettre à jour les champs relatifs à la
           nomenclature.
ARGUMENT : domaines (optionnel) : un tableau text[] contenant les noms des
domaines à importer, soit le "niveau 1"/niv1 ou niv1_abr des schémas. Si non renseigné,
toute la nomenclature est importée (hors schémas déjà référencés).
SORTIE : '__ FIN IMPORT NOMENCLATURE.' si la requête s'est exécutée normalement. */
DECLARE
    item record ;
    e_mssg text ;
    e_detl text ;
    e_hint text ;
BEGIN
    FOR item IN SELECT * FROM (
            VALUES
                ('c', true, 'Données génériques', 'donnees_generique', 'Découpage électoral', 'decoupage_electoral', 'c_don_gen_decoupage_electoral', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Données génériques', 'donnees_generique', 'Démographie', 'demographie', 'c_don_gen_demographie', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Données génériques', 'donnees_generique', 'Habillage des cartes', 'habillage', 'c_don_gen_habillage', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Données génériques', 'donnees_generique', 'Intercommunalité', 'intercommunalite', 'c_don_gen_intercommunalite', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Données génériques', 'donnees_generique', 'Milieu physique', 'milieu_physique', 'c_don_gen_milieu_physique', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Alimentation en eau potable', 'aep', 'c_eau_aep', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Assainissement', 'assainissement', 'c_eau_assainissement', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Masses d’eau', 'masse_eau', 'c_eau_masse_eau', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Ouvrages', 'ouvrage', 'c_eau_ouvrage', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Pêche', 'peche', 'c_eau_peche', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Surveillance', 'surveillance', 'c_eau_surveillance', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Agriculture', 'agriculture', 'Environnement', 'agri_environnement', 'c_agri_environnement', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Agriculture', 'agriculture', 'Agro-alimentaire', 'agro_alimentaire', 'c_agri_agroalimentaire', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Agriculture', 'agriculture', 'Exploitation & élevage', 'exploitation_elevage', 'c_agri_exploi_elevage', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Agriculture', 'agriculture', 'Parcellaire agricole', 'parcellaire_agricole', 'c_agri_parcellaire_agricole', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Agriculture', 'agriculture', 'Santé animale', 'sante_animale', 'c_agri_sante_animale', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Agriculture', 'agriculture', 'Santé végétale', 'sante_vegetale', 'c_agri_sante_vegetale', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Séismes', 'seisme', 'c_risque_seisme', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Agriculture', 'agriculture', 'Zonages agricoles', 'zonages_agricoles', 'c_agri_zonages_agricoles', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Air & climat', 'air_climat', 'Changement climatique', 'changement_climatique', 'c_air_clim_changement', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Air & climat', 'air_climat', 'Météorologie', 'meteo', 'c_air_clim_meteo', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Air & climat', 'air_climat', 'Qualité de l’air & pollution', 'qualité_pollution', 'c_air_clim_qual_polu', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Aménagement & urbanisme', 'amenagement_urbanisme', 'Assiettes des servitudes', 'assiette_servitude', 'c_amgt_urb_servitude', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Aménagement & urbanisme', 'amenagement_urbanisme', 'Politique européenne', 'politique_europeenne', 'c_amgt_urb_pol_euro', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Aménagement & urbanisme', 'amenagement_urbanisme', 'Zonages d’aménagement', 'zonages_amenagement', 'c_amgt_urb_zon_amgt', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Aménagement & urbanisme', 'amenagement_urbanisme', 'Zonages d’études', 'zonages_etudes', 'c_amgt_urb_zon_etudes', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Aménagement & urbanisme', 'amenagement_urbanisme', 'Zonages de planification', 'zonages_planification', 'c_amgt_urb_zon_plan', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Culture, société & services', 'culture_societe_service', 'Enseignement', 'enseignement', 'c_cult_soc_ser_enseignement', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Culture, société & services', 'culture_societe_service', 'Équipements sportifs et culturels', 'equipement_sportif_culturel', 'c_cult_soc_ser_equip_sport_cult', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Culture, société & services', 'culture_societe_service', 'Autres établissements', 'erp_autre', 'c_cult_soc_ser_erp_autre', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Culture, société & services', 'culture_societe_service', 'Patrimoine culturel', 'patrimoine_culturel', 'c_cult_soc_ser_patrim_cult', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Culture, société & services', 'culture_societe_service', 'Santé & social', 'sante_social', 'c_cult_soc_ser_sante_social', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Culture, société & services', 'culture_societe_service', 'Tourisme', 'tourisme', 'c_cult_soc_ser_tourisme', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Données génériques', 'donnees_generique', 'Action publique', 'action_publique', 'c_don_gen_action_publique', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Données génériques', 'donnees_generique', 'Découpage administratif', 'administratif', 'c_don_gen_administratif', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Travaux & entretien', 'travail_action', 'c_eau_travail_action', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Autres utilisations', 'utilisation_autre', 'c_eau_utilisation_autre', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Eau', 'eau', 'Zonages eau', 'zonages_eau', 'c_eau_zonages', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Foncier & sol', 'foncier_sol', 'Foncier agricole', 'foncier_agricole', 'c_fon_sol_agricole', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Foncier & sol', 'foncier_sol', 'Mutations foncières', 'mutation_fonciere', 'c_fon_sol_mutation', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Foncier & sol', 'foncier_sol', 'Occupation du sol', 'occupation_sol', 'c_fon_sol_occupation', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Foncier & sol', 'foncier_sol', 'Propriétés foncières', 'propriete_fonciere', 'c_fon_sol_propriete', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Forêt', 'foret', 'Description', 'description', 'c_foret_description', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Forêt', 'foret', 'Défense de la forêt contre les incendies', 'dfci', 'c_foret_dfci', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Forêt', 'foret', 'Gestion', 'gestion', 'c_foret_gestion', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Forêt', 'foret', 'Règlement', 'reglement', 'c_foret_reglement', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Forêt', 'foret', 'Transformation', 'transformation', 'c_foret_transformation', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Accession à la propriété', 'accession_propriete', 'c_hab_vil_access_propriete', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Besoin en logements', 'besoin_en_logement', 'c_hab_vil_besoin_logt', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Construction', 'construction', 'c_hab_vil_construction', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Habitat indigne', 'habitat_indigne', 'c_hab_vil_habitat_indigne', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Occupation des logements', 'occupation_logements', 'c_hab_vil_occupation_logt', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Parc locatif social', 'parc_locatif_social', 'c_hab_vil_parc_loc_social', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Parc de logements', 'parc_logements', 'c_hab_vil_parc_logt', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Politique', 'politique', 'c_hab_vil_politique', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Habitat & politique de la ville', 'habitat_politique_de_la_ville', 'Rénovation', 'renovation', 'c_hab_vil_renovation', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Autres activités', 'autres_activites', 'c_mer_litt_autres_activites', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Chasse maritime', 'chasse_maritime', 'c_mer_litt_chasse_maritime', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Culture marine', 'culture_marine', 'c_mer_litt_culture_marine', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Écologie du littoral', 'ecologie_littoral', 'c_mer_litt_ecol_littoral', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Limites administratives spéciales', 'lim_admin_speciale', 'c_mer_litt_lim_admin_spe', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Lutte anti-pollution', 'lutte_anti_pollution', 'c_mer_litt_lutte_anti_pollu', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Navigation maritime', 'navigation_maritime', 'c_mer_litt_nav_maritime', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Pêche maritime', 'peche_maritime', 'c_mer_litt_peche_maritime', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Mer & littoral', 'mer_littoral', 'Topographie', 'topographie', 'c_mer_litt_topographie', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nature, paysage & biodiversité', 'nature_paysage_biodiversite', 'Chasse', 'chasse', 'c_nat_pays_bio_chasse', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nature, paysage & biodiversité', 'nature_paysage_biodiversite', 'Inventaires nature & biodiversité', 'inventaire_nature_biodiversite', 'c_nat_pays_bio_invent_nat_bio', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nature, paysage & biodiversité', 'nature_paysage_biodiversite', 'Inventaires paysages', 'inventaire_paysage', 'c_nat_pays_bio_invent_pays', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nature, paysage & biodiversité', 'nature_paysage_biodiversite', 'Zonages nature', 'zonage_nature', 'c_nat_pays_bio_zonage_nat', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nature, paysage & biodiversité', 'nature_paysage_biodiversite', 'Zonages paysages', 'zonage_paysage', 'c_nat_pays_bio_zonage_pays', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nuisances', 'nuisance', 'Bruit', 'bruit', 'c_nuis_bruit', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nuisances', 'nuisance', 'Déchets', 'dechet', 'c_nuis_dechet', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nuisances', 'nuisance', 'Nuisances électromagnétiques', 'nuisance_electromagnetique', 'c_nuis_electromag', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Nuisances', 'nuisance', 'Pollution des sols', 'pollution_sol', 'c_nuis_pollu_sol', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Réseaux & énergie', 'reseau_energie_divers', 'Aménagement numérique du territoire', 'amenagement_numerique_territoire', 'c_res_energ_amgt_num_terri', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Réseaux & énergie', 'reseau_energie_divers', 'Autre', 'autre', 'c_res_energ_autre', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Réseaux & énergie', 'reseau_energie_divers', 'Électricité', 'electricite', 'c_res_energ_electricite', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Réseaux & énergie', 'reseau_energie_divers', 'Hydrocarbures', 'hydrocarbure', 'c_res_energ_hydrocarbure', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Réseaux & énergie', 'reseau_energie_divers', 'Télécommunications', 'telecommunication', 'c_res_energ_telecom', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Avalanche', 'avalanche', 'c_risque_avalanche', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Éruptions volcaniques', 'eruption_volcanique', 'c_risque_eruption_volcanique', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Gestion des risques', 'gestion_risque', 'c_risque_gestion_risque', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Inondations', 'inondation', 'c_risque_inondation', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Mouvements de terrain', 'mouvement_terrain', 'c_risque_mouvement_terrain', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Radon', 'radon', 'c_risque_radon', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Risques miniers', 'risque_minier', 'c_risque_minier', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Risques technologiques', 'risque_technologique', 'c_risque_techno', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Zonages risques naturels', 'zonages_risque_naturel', 'c_risque_zonages_naturel', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Risques', 'risque', 'Zonages risques technologiques', 'zonages_risque_technologique', 'c_risque_zonages_techno', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Sites industriels & production', 'site_industriel_production', 'Mines, carrières & granulats', 'mine_carriere_granulats', 'c_indus_prod_mine_carriere_granul', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Sites industriels & production', 'site_industriel_production', 'Sites éoliens', 'site_eolien', 'c_indus_prod_eolien', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Sites industriels & production', 'site_industriel_production', 'Sites industriels', 'site_industriel', 'c_indus_prod_industriel', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Sites industriels & production', 'site_industriel_production', 'Sites de production d’énergie', 'site_production_energie', 'c_indus_prod_prod_energ', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Socio-économie', 'socio_economie', ' ', ' ', 'c_socio_eco_', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Déplacements', 'transport_deplacement', 'Sécurité routière', 'securite_routiere', 'c_tr_depl_securite_routiere', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Déplacements', 'transport_deplacement', 'Transport collectif', 'tr_collectif', 'c_tr_depl_collectif', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Déplacements', 'transport_deplacement', 'Transport exceptionnel', 'tr_exceptionnel', 'c_tr_depl_exceptionnel', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Déplacements', 'transport_deplacement', 'Transport de marchandises', 'tr_marchandise', 'c_tr_depl_marchandise', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Déplacements', 'transport_deplacement', 'Transport de matières dangereuses', 'tr_matiere_dangereuse', 'c_tr_depl_mat_dangereuse', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Déplacements', 'transport_deplacement', 'Trafic', 'trafic', 'c_tr_depl_trafic', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Infrastructures de transport', 'transport_infrastructure', 'Aérien', 'aerien', 'c_tr_infra_aerien', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Infrastructures de transport', 'transport_infrastructure', 'Circulation douce', 'circulation_douce', 'c_tr_infra_circulation_douce', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Infrastructures de transport', 'transport_infrastructure', 'Ferroviaire', 'ferroviaire', 'c_tr_infra_ferroviaire', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Infrastructures de transport', 'transport_infrastructure', 'Fluvial', 'fluvial', 'c_tr_infra_fluvial', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Infrastructures de transport', 'transport_infrastructure', 'Maritime', 'maritime', 'c_tr_infra_maritime', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Infrastructures de transport', 'transport_infrastructure', 'Plateformes multimodales', 'plateforme_multimodale', 'c_tr_infra_plateforme_multimod', false, 'g_admin', NULL, 'g_consult'),
                ('c', true, 'Infrastructures de transport', 'transport_infrastructure', 'Routier', 'routier', 'c_tr_infra_routier', false, 'g_admin', NULL, 'g_consult')
            ) AS t (bloc, nomenclature, niv1, niv1_abr, niv2, niv2_abr, nom_schema, creation, producteur, editeur, lecteur)
            WHERE domaines IS NULL OR niv1 = ANY(domaines) OR niv1_abr = ANY(domaines)
    LOOP
        -- si le schéma n'était pas déjà référencé, il est ajouté
        -- (toujours comme non créé, même s'il existe par ailleurs dans la base)
        IF NOT item.nom_schema IN (SELECT gestion_schema_usr.nom_schema FROM z_asgard.gestion_schema_usr)
        THEN
            INSERT INTO z_asgard.gestion_schema_usr
                (bloc, nomenclature, niv1, niv1_abr, niv2, niv2_abr, nom_schema, creation, producteur, editeur, lecteur) VALUES
                (item.bloc, item.nomenclature, item.niv1, item.niv1_abr, item.niv2, item.niv2_abr, item.nom_schema, item.creation, item.producteur, item.editeur, item.lecteur) ;
            RAISE NOTICE 'Le schéma % a été ajouté à la table de gestion.', item.nom_schema ;
        
        -- sinon les champs de la nomenclature sont simplement mis à jour, le cas échéant
        ELSIF item.nom_schema IN (SELECT gestion_schema_usr.nom_schema FROM z_asgard.gestion_schema_usr)
        THEN
            UPDATE z_asgard.gestion_schema_usr
                SET nomenclature = item.nomenclature,
                    niv1 = item.niv1,
                    niv1_abr = item.niv1_abr,
                    niv2 = item.niv2,
                    niv2_abr = item.niv2_abr
                WHERE gestion_schema_usr.nom_schema = item.nom_schema
                    AND (NOT nomenclature = item.nomenclature
                        OR NOT coalesce(gestion_schema_usr.niv1, '') = coalesce(item.niv1, '')
                        OR NOT coalesce(gestion_schema_usr.niv1_abr, '') = coalesce(item.niv1_abr, '')
                        OR NOT coalesce(gestion_schema_usr.niv2, '') = coalesce(item.niv2, '')
                        OR NOT coalesce(gestion_schema_usr.niv2_abr, '') = coalesce(item.niv2_abr, '')) ;
            IF FOUND
            THEN
                RAISE NOTICE 'Les champs de la nomenclature ont été mis à jour pour le schéma %.', item.nom_schema ;
            END IF ;
    
        END IF ;
    END LOOP ;

    RETURN '__ FIN IMPORT NOMENCLATURE.' ;

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
                            e_hint = PG_EXCEPTION_HINT,
                            e_detl = PG_EXCEPTION_DETAIL ;
    RAISE EXCEPTION 'FIN0 > %', e_mssg
        USING DETAIL = e_detl,
            HINT = e_hint ;
    

END
$_$;

ALTER FUNCTION z_asgard_admin.asgard_import_nomenclature(text[])
    OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_import_nomenclature(text[]) IS 'ASGARD. Fonction qui importe dans la table de gestion les schémas manquants de la nomenclature nationale - ou de certains domaines de la nomenclature nationale listés en argument.' ;


------ 4.13 - REAFFECTATION DES PRIVILEGES D'UN RÔLE ------

-- FUNCTION: z_asgard_admin.asgard_reaffecte_role(text, text, boolean, boolean)

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_reaffecte_role(
                                n_role text,
                                n_role_cible text DEFAULT NULL,
                                b_hors_asgard boolean DEFAULT False,
                                b_privileges boolean DEFAULT True)
    RETURNS text
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction transfère tous les privilèges d'un rôle
           à un autre, et en premier lieu ses fonctions de producteur,
           éditeur et lecteur. Si aucun rôle cible n'est spécifié, les
           privilèges sont simplement supprimés et g_admin devient
           producteur des schémas, le cas échéant.
ARGUMENTS :
- n_role : une chaîne de caractères présumée correspondre à un nom de
  rôle valide ;
- n_role_cible : une chaîne de caractères présumée correspondre à un
  nom de rôle valide ;
- b_hors_asgard : un booléen, valeur par défaut False. Si ce paramètre
  vaut True, la propriété et les privilèges sur les objets des schémas
  non gérés par ASGARD ou hors schémas (par ex la base), sont pris en
  compte. La propriété des objets reviendra à g_admin si aucun
  rôle cible n'est spécifié ;
- b_privileges : un booléen, valeur par défaut True. Indique si, dans
  l'hypothèse où le rôle cible est spécifié, celui-ci doit recevoir
  les privilèges et propriétés du rôle (True) ou seulement ses propriétés
  (False).
SORTIE : '__ REAFFECTATION REUSSIE' si la fonction s'est exécutée sans
erreur. */
DECLARE
    item record ;
    n_producteur_cible text := coalesce(n_role_cible, 'g_admin') ;
    c record ;
    k int ;
BEGIN

    ------ TESTS PREALABLES -----
    -- existance du rôle
    IF NOT n_role IN (SELECT rolname FROM pg_catalog.pg_roles)
    THEN
        RAISE EXCEPTION 'FRR1. Echec. Le rôle % n''existe pas', n_role ;
    END IF ;
    
    -- existance du rôle cible
    IF n_role_cible IS NOT NULL AND NOT n_role_cible IN (SELECT rolname FROM pg_catalog.pg_roles)
    THEN
        RAISE EXCEPTION 'FRR2. Echec. Le rôle % n''existe pas', n_role_cible ;
    END IF ;
    
    
    IF NOT b_privileges
    THEN
        n_role_cible := NULL ;
    END IF ;
    
    ------ FONCTION DE PRODUCTEUR ------
    FOR item IN (SELECT * FROM z_asgard.gestion_schema_usr WHERE producteur = n_role)
    LOOP
        IF item.editeur = n_producteur_cible
        THEN
            UPDATE z_asgard.gestion_schema_usr
                SET editeur = NULL
                WHERE nom_schema = item.nom_schema ;
            RAISE NOTICE '... L''éditeur du schéma % a été supprimé.', item.nom_schema ;
        END IF ;
        
        IF item.lecteur = n_producteur_cible
        THEN
            UPDATE z_asgard.gestion_schema_usr
                SET lecteur = NULL
                WHERE nom_schema = item.nom_schema ;
            RAISE NOTICE '... Le lecteur du schéma % a été supprimé.', item.nom_schema ;
        END IF ;
        
        UPDATE z_asgard.gestion_schema_usr
            SET producteur = n_role_cible
            WHERE nom_schema = item.nom_schema ;
            RAISE NOTICE '... Le producteur du schéma % a été redéfini.', item.nom_schema ;
    END LOOP ;
    
    ------ FONCTION D'EDITEUR ------
    -- seulement si le rôle cible n'est pas déjà producteur du schéma
    FOR item IN (SELECT * FROM z_asgard.gestion_schema_usr WHERE editeur = n_role)
    LOOP
        IF item.producteur = n_role_cible
        THEN
            RAISE NOTICE 'Le rôle cible est actuellement producteur du schéma %.', item.nom_schema ;
            UPDATE z_asgard.gestion_schema_usr
                SET editeur = NULL
                WHERE nom_schema = item.nom_schema ;
            RAISE NOTICE '... L''éditeur du schéma % a été supprimé.', item.nom_schema ;
        ELSE
        
            IF item.lecteur = n_role_cible
                THEN
                UPDATE z_asgard.gestion_schema_usr
                    SET lecteur = NULL
                    WHERE nom_schema = item.nom_schema ;
                RAISE NOTICE '... Le lecteur du schéma % a été supprimé.', item.nom_schema ;
            END IF ;
            
            UPDATE z_asgard.gestion_schema_usr
                SET editeur = n_role_cible
                WHERE nom_schema = item.nom_schema ;
                RAISE NOTICE '... L''éditeur du schéma % a été redéfini.', item.nom_schema ;
        
        END IF ;
    END LOOP ;
    
    ------ FONCTION DE LECTEUR ------
    -- seulement si le rôle cible n'est pas déjà producteur ou éditeur du schéma
    FOR item IN (SELECT * FROM z_asgard.gestion_schema_usr WHERE lecteur = n_role)
    LOOP
        IF item.producteur = n_role_cible
        THEN
            RAISE NOTICE 'Le rôle cible est actuellement producteur du schéma %.', item.nom_schema ;
            UPDATE z_asgard.gestion_schema_usr
                SET lecteur = NULL
                WHERE nom_schema = item.nom_schema ;
            RAISE NOTICE '... Le lecteur du schéma % a été supprimé.', item.nom_schema ;
        ELSIF item.editeur = n_role_cible
        THEN
            RAISE NOTICE 'Le rôle cible est actuellement éditeur du schéma %.', item.nom_schema ;
            UPDATE z_asgard.gestion_schema_usr
                SET lecteur = NULL
                WHERE nom_schema = item.nom_schema ;
            RAISE NOTICE '... Le lecteur du schéma % a été supprimé.', item.nom_schema ;
        ELSE
            
            UPDATE z_asgard.gestion_schema_usr
                SET lecteur = n_role_cible
                WHERE nom_schema = item.nom_schema ;
                RAISE NOTICE '... Le lecteur du schéma % a été redéfini.', item.nom_schema ;
        
        END IF ;
    END LOOP ;
    
    ------ PROPRIETES HORS ASGARD ------
    IF b_hors_asgard
    THEN
        EXECUTE 'REASSIGN OWNED BY ' || quote_ident(n_role) || ' TO ' || quote_ident(n_producteur_cible) ;
        RAISE NOTICE '> %', 'REASSIGN OWNED BY ' || quote_ident(n_role) || ' TO ' || quote_ident(n_producteur_cible) ;
        RAISE NOTICE '... Le cas échéant, la propriété des objets hors schémas référencés par ASGARD a été réaffectée.' ;
    END IF ;
    
    ------ PRIVILEGES RESIDUELS SUR LES SCHEMAS D'ASGARD -------
    k := 0 ;
    FOR item IN (SELECT * FROM z_asgard.gestion_schema_usr WHERE creation)
    LOOP
        FOR c IN (SELECT * FROM z_asgard.asgard_synthese_role(
                       quote_ident(item.nom_schema)::regnamespace, quote_ident(n_role)::regrole))
        LOOP
            EXECUTE format(z_asgard.asgard_grant_to_revoke(c.commande), n_role) ;
            RAISE NOTICE '> %', format(z_asgard.asgard_grant_to_revoke(c.commande), n_role) ;
            
            IF n_role_cible IS NOT NULL
            THEN
                EXECUTE format(c.commande, n_role_cible) ;
                RAISE NOTICE '> %', format(c.commande, n_role_cible) ;
            END IF ;
            
            k := k + 1 ;
        END LOOP ;        
    END LOOP ;
    IF k > 0
    THEN
        IF n_role_cible IS NULL
        THEN
            RAISE NOTICE '... Les privilèges résiduels du rôle % sur les schémas référencés par ASGARD ont été révoqués.', n_role ;
        ELSE
            RAISE NOTICE '... Les privilèges résiduels du rôle % sur les schémas référencés par ASGARD ont été réaffectés.', n_role ;
        END IF ;
    END IF ;
    
    ------ PRIVILEGES RESIDUELS SUR LES SCHEMAS HORS ASGARD ------
    IF b_hors_asgard
    THEN
        k := 0 ;
        FOR item IN (SELECT * FROM pg_catalog.pg_namespace
                         LEFT JOIN z_asgard.gestion_schema_usr
                             ON nspname::text = nom_schema AND creation
                         WHERE nom_schema IS NULL)
        LOOP
            FOR c IN (SELECT * FROM z_asgard.asgard_synthese_role(
                           quote_ident(item.nspname::text)::regnamespace, quote_ident(n_role)::regrole))
            LOOP
                EXECUTE format(z_asgard.asgard_grant_to_revoke(c.commande), n_role) ;
                RAISE NOTICE '> %', format(z_asgard.asgard_grant_to_revoke(c.commande), n_role) ;
                
                IF n_role_cible IS NOT NULL
                THEN
                    EXECUTE format(c.commande, n_role_cible) ;
                    RAISE NOTICE '> %', format(c.commande, n_role_cible) ;
                END IF ;
                
                k := k + 1 ;
            END LOOP ;        
        END LOOP ;
        IF k > 0
        THEN
            IF n_role_cible IS NULL
        THEN
            RAISE NOTICE '... Les privilèges résiduels du rôle % sur les schémas non référencés par ASGARD ont été révoqués.', n_role ;
        ELSE
            RAISE NOTICE '... Les privilèges résiduels du rôle % sur les schémas non référencés par ASGARD ont été réaffectés.', n_role ;
        END IF ;
        END IF ;
    END IF ;
    
    ------- OBJETS HORS SCHEMAS ------
    IF b_hors_asgard
    THEN
        k := 0 ;
        FOR c IN (
            WITH t_acl AS (
            -- bases de données
            SELECT 'DATABASE'::text AS type_obj, datname::text AS n_obj, unnest(datacl)::text AS acl
                FROM pg_catalog.pg_database
                WHERE datacl IS NOT NULL
            UNION
            -- tablespaces
            SELECT 'TABLESPACE'::text AS type_obj, spcname::text AS n_obj, unnest(spcacl)::text AS acl
                FROM pg_catalog.pg_tablespace
                WHERE spcacl IS NOT NULL
            UNION
            -- foreign data wrappers
            SELECT 'FOREIGN DATA WRAPPER'::text AS type_obj, fdwname::text AS n_obj, unnest(fdwacl)::text AS acl
                FROM pg_catalog.pg_foreign_data_wrapper
                WHERE fdwacl IS NOT NULL
            UNION
            -- foreign servers
            SELECT 'FOREIGN SERVER'::text AS type_obj, srvname::text AS n_obj, unnest(srvacl)::text AS acl
                FROM pg_catalog.pg_foreign_server
                WHERE srvacl IS NOT NULL
            UNION
            -- langages
            SELECT 'LANGUAGE'::text AS type_obj, lanname::text AS n_obj, unnest(lanacl)::text AS acl
                FROM pg_catalog.pg_language
                WHERE lanacl IS NOT NULL
            UNION            
            -- large objects
            SELECT 'LARGE OBJECT'::text AS type_obj, pg_largeobject_metadata.oid::text AS n_obj, unnest(lomacl)::text AS acl
                FROM pg_catalog.pg_largeobject_metadata
                WHERE lomacl IS NOT NULL           
            )
            SELECT 'GRANT ' || privilege || ' ON ' || type_obj || ' ' || quote_ident(n_obj) || ' TO %I' AS commande
                FROM t_acl, unnest(ARRAY['CREATE', 'CONNECT', 'TEMPORARY', 'USAGE', 'SELECT', 'UPDATE'],
                                   ARRAY['C', 'c', 'T', 'U', 'r', 'w']) AS l (privilege, prvlg)
                WHERE acl ~ ('^' || quote_ident(n_role)::regrole::text || '[=].*' || prvlg || '.*[/]')
        ) LOOP
            EXECUTE format(z_asgard.asgard_grant_to_revoke(c.commande), n_role) ;
            RAISE NOTICE '> %', format(z_asgard.asgard_grant_to_revoke(c.commande), n_role) ;
            
            IF n_role_cible IS NOT NULL
            THEN
                EXECUTE format(c.commande, n_role_cible) ;
                RAISE NOTICE '> %', format(c.commande, n_role_cible) ;
            END IF ;
            
            k := k + 1 ;
        END LOOP ;
        IF k > 0
        THEN
            IF n_role_cible IS NULL
            THEN
                RAISE NOTICE '... Les privilèges résiduels du rôle % sur les objets hors schémas ont été révoqués.', n_role ;
            ELSE
                RAISE NOTICE '... Les privilèges résiduels du rôle % sur les objets hors schémas ont été réaffectés.', n_role ;
            END IF ;
        END IF ;
    END IF ;

    RETURN '__ REAFFECTATION REUSSIE' ;
END
$_$;

ALTER FUNCTION z_asgard_admin.asgard_reaffecte_role(text, text, boolean, boolean)
    OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_reaffecte_role(text, text, boolean, boolean) IS 'ASGARD. Fonction qui réaffecte les privilèges et propriétés d''un rôle à un autre.' ;


------ 4.14 - REINITIALISATION DES PRIVILEGES SUR TOUS LES SCHEMAS ------

-- FUNCTION: z_asgard_admin.asgard_initialise_all_schemas(integer)

CREATE OR REPLACE FUNCTION z_asgard_admin.asgard_initialise_all_schemas(variante integer DEFAULT 0)
    RETURNS varchar[]
    LANGUAGE plpgsql
    AS $_$
/* OBJET : Cette fonction réinitialise les privilèges sur tous les
           schémas référencés par ASGARD en une seule commande.
           Pour les schémas d'ASGARD, même s'ils n'ont pas été référencés,
           les droits nécessaires au bon fonctionnement du système seront
           rétablis. 
ARGUMENTS : un entier optionnel - 0 par défaut.
Si 1, la fonction ne fera que s'assurer que tous les objets appartiennent
au propriétaire du schéma. Si 2, la fonction ne s'exécutera que sur les
schémas d'ASGARD. 
SORTIE : NULL si la requête s'est exécutée normalement, sinon la liste
des schémas qui n'ont pas pu être traités. Se reporter dans ce cas à
l'onglet des messages pour le détail des erreurs. */
DECLARE
    s record ;
    l varchar[] ;
    b boolean ;
    k integer ;
    e_mssg text ;
    e_detl text ;
    e_hint text ;
    utilisateur text := current_user::text ;
    v_prop oid ;
    t text ;
BEGIN

    ------ CONTROLES PREALABLES ------
    -- la fonction est dans z_asgard_admin, donc seuls les membres de
    -- g_admin devraient pouvoir y accéder, mais au cas où :
    IF NOT pg_has_role('g_admin', 'USAGE')
    THEN
        RAISE EXCEPTION 'FAS1. Opération interdite. Vous devez être membre de g_admin pour exécuter cette fonction.' ;
    END IF ;
    
    IF NOT utilisateur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolsuper)
    THEN
        SET ROLE g_admin ;
    END IF ;
    
    -- permission manquante du propriétaire de la vue gestion_schema_usr
    -- (en principe g_admin_ext) sur le schéma z_asgard_admin ou la table
    -- gestion_schema :
    SELECT relowner INTO v_prop
        FROM pg_catalog.pg_class
        WHERE relname = 'gestion_schema_usr' AND relnamespace = 'z_asgard'::regnamespace::oid ;
        
    IF NOT FOUND
    THEN
        RAISE EXCEPTION 'FAS2. Echec. La vue gestion_schema_usr est introuvable.' ;
    END IF ;
    
    IF NOT has_schema_privilege(v_prop, 'z_asgard_admin', 'USAGE')
            OR NOT has_table_privilege(v_prop, 'z_asgard_admin.gestion_schema', 'SELECT')
    THEN
        RAISE NOTICE '(temporaire) droits a minima pour le propriétaire de la vue gestion_schema_usr :' ;
    
        IF NOT has_schema_privilege(v_prop, 'z_asgard_admin', 'USAGE')
        THEN
            t := 'GRANT USAGE ON SCHEMA z_asgard_admin TO ' || v_prop::regrole::text ;
            EXECUTE t ;
            RAISE NOTICE '> %', t ;
        END IF ;
        
        IF NOT has_table_privilege(v_prop, 'z_asgard_admin.gestion_schema', 'SELECT')
        THEN
            t := 'GRANT SELECT ON TABLE z_asgard_admin.gestion_schema TO ' || v_prop::regrole::text ;
            EXECUTE t ;
            RAISE NOTICE '> %', t ;
        END IF ;
        
        RAISE NOTICE '---------------------------------' ;
    END IF ;
    
    ------ NETTOYAGE ------
    FOR s IN (
            SELECT 2 AS n, nom_schema, producteur
                FROM z_asgard.gestion_schema_usr
                WHERE creation AND NOT nom_schema IN ('z_asgard', 'z_asgard_admin')
            UNION VALUES (1, 'z_asgard', 'g_admin_ext'), (0, 'z_asgard_admin', 'g_admin')
            ORDER BY n, nom_schema
            )
    LOOP
        b := True ;
        
        IF s.n < 2 OR variante < 2
        THEN
        
            ------ CONTROLE DES PRIVILEGES DE G_ADMIN SUR LE PRODUCTEUR ------
            -- si g_admin n'est pas membre du producteur, alors on l'en rend
            -- membre, sous réserve que ce ne soit pas un super-utilisateur ou
            -- un rôle de connexion (ce dernier cas n'étant pas supposé arriver,
            -- sauf désactivation temporaire de triggers ou à avoir donné
            -- LOGIN au rôle après l'avoir désigné comme producteur).
            IF NOT pg_has_role(s.producteur, 'USAGE')
            THEN
                -- propriétaire super-utilisateur
                IF s.producteur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolsuper)
                THEN
                    RAISE NOTICE '... ECHEC. Schéma % non traité.', s.nom_schema
                        USING DETAIL = 'Seul un super-utilisateur est habilité à intervenir sur ce schéma. Producteur : ' || s.producteur || '.',
                            HINT = 'Veuillez relancer la fonction en tant que super-utilisateur.' ;
                    b := False ;
                    l := array_append(l, s.nom_schema) ;
                
                -- propriétaire rôle de connexion
                ELSIF s.producteur IN (SELECT rolname FROM pg_catalog.pg_roles WHERE rolcanlogin)
                THEN
                    RAISE NOTICE '... ECHEC. Schéma % non traité.', s.nom_schema
                        USING DETAIL = 'Le producteur du schéma est un rôle de connexion. Producteur : ' || s.producteur || '.',
                            HINT = 'Veuillez relancer la fonction en tant que super-utilisateur ou après avoir désigné un rôle de groupe comme producteur.' ;
                    b := False ;
                    l := array_append(l, s.nom_schema) ;
                    
                -- rôle de groupe lambda sur lequel g_admin n'a pas de permission
                -- on la lui donne et on continue
                ELSE            
                    EXECUTE 'GRANT ' || quote_ident(s.producteur) || ' TO g_admin' ;
                    RAISE NOTICE '... Permission accordée à g_admin sur le rôle %.', s.producteur ;
                END IF ;
            END IF ;
            
            IF b
            THEN             
                BEGIN
                    IF variante = 1
                    THEN
                        -- lancement de la fonction de nettoyage des propriétaires
                        IF quote_ident(s.producteur) IN (SELECT nspowner::regrole::text FROM pg_catalog.pg_namespace WHERE nspname = s.nom_schema)
                        THEN
                            -- version objets seuls si le propriétaire du schéma est bon
                            RAISE NOTICE '(ré)attribution de la propriété des objets au rôle producteur du schéma :' ;
                            SELECT z_asgard.asgard_admin_proprietaire(s.nom_schema, s.producteur, False) INTO k ;
                            IF k = 0
                            THEN
                                RAISE NOTICE '> néant' ;
                            END IF ;
                        ELSE
                            -- version schéma + objets sinon
                            RAISE NOTICE '(ré)attribution de la propriété du schéma et des objets au rôle producteur du schéma :' ;
                            PERFORM z_asgard.asgard_admin_proprietaire(s.nom_schema, s.producteur) ;
                        END IF ;
                            
                    ELSE
                        -- lancement de la fonction de réinitialisation des droits
                        PERFORM z_asgard.asgard_initialise_schema(s.nom_schema) ;
                            
                    END IF ;
                    
                    RAISE NOTICE '... Le schéma % a été traité', s.nom_schema ;
                    
                EXCEPTION WHEN OTHERS THEN
                    GET STACKED DIAGNOSTICS e_mssg = MESSAGE_TEXT,
                                            e_hint = PG_EXCEPTION_HINT,
                                            e_detl = PG_EXCEPTION_DETAIL ;
                    RAISE NOTICE '... ECHEC. Schéma % non traité.', s.nom_schema ;
                    RAISE NOTICE 'FAS0 > %', e_mssg
                        USING DETAIL = e_detl,
                            HINT = e_hint ;
                    l := array_append(l, s.nom_schema) ;
                END ;
            END IF ;    
        
        RAISE NOTICE '---------------------------------' ;
    END IF ;
    
    END LOOP ;
    
    EXECUTE 'SET ROLE ' || quote_ident(utilisateur) ;
    
    ------ RESULTAT ------
    RETURN l ;

END
$_$;

ALTER FUNCTION z_asgard_admin.asgard_initialise_all_schemas(integer)
    OWNER TO g_admin ;

COMMENT ON FUNCTION z_asgard_admin.asgard_initialise_all_schemas(integer) IS 'ASGARD. Fonction qui réinitialise les droits sur l''ensemble des schémas référencés.' ;