Projet

Général

Profil

Télécharger (144 ko)

Evolution #315 » asgard_4_fonctions_utilitaires_v10.sql

Leslie Lemaire, 31/08/2020 14:00

 

-- 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.' ;

    (1-1/1)