db_ecological_db

Function: ps_supprimer_compte

Schema

public

Owner

postgres

Descriptions

Suppression d'un rôle de la base en vérifiant son appartenance à certains groupes (désinscription, suppression des droits par défaut et reassign des objets détenus

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_supprimer_compte (
 compte text
)
RETURNS text AS
$span$
-- Auteur : Alain Benard
-- Date de mise à jour : 17/11/2020
-- Cette procédure sert à supprimer un rôle en vérifiant qu'il appartient bien à certains groupes.
-- Elle désinscrit le compte des groupes lecture ou ecriture de la base courante le cas échéant , supprime les droits par défaut de cet utilisateur au
-- sein de la base courante et donne la propriété des objets détenus par le compte au groupe admin de la base.
-- Si le compte n'appartient qu'aux groupes liés à la base il sera désinscrit de ces groupes puis supprimé.
-- Si le compte n'appartient à aucun groupe cela signifie qu'il n'a pas été créé par ps_creer_compte et ne sera pas supprimé.
-- Si le compte appartient à d'autres groupes que ceux liés à la base il sera désinscrit des groupes liés à la base mais pas des autres et ne sera pas supprimé.

-- paramètres :
-- compte : login de l'utilisateur à supprimer

-- Un groupe habilité à exécuter cette fonction doit être créé. (nombase_admin) et les droits associés à cette procédure doivent être positionnés tels que ci-dessous :
-- ALTER FUNCTION public.supprimer_compte(compte text) OWNER TO postgres;
-- GRANT EXECUTE ON FUNCTION public.supprimer_compte(compte text) TO base_admin;
-- REVOKE EXECUTE ON FUNCTION public.supprimer_compte(compte text) FROM PUBLIC;

-- déclaration des variables de traitements
DECLARE
   nb integer;
   nbgroupeappartient integer;
   nbgroupesupprime integer;
   req text;
   lignedata record;
base text; -- nom de la base de données courante sans le prefixe
   groupeappartenance text;
role_lecture text; -- nom du groupe bénéficiant des droits en lecture sur la base
   role_ecriture text; -- nom du groupe bénéficiant des droits en écriture sur la base
role_admin text; -- nom du groupe bénéficiant des droits d'exécution des procédures stockées d'administration sur la base


BEGIN

select into base substring(current_database() from 4);
role_lecture = base || '_lecture';
role_ecriture = base || '_ecriture';
role_admin = base || '_admin';

-- Vérifie que le compte existe
req := $$SELECT count(oid) FROM pg_roles where rolname = '$$ || compte || $$';$$;
EXECUTE req into nb;
if nb <> 1 then
return 'le compte est inexistant : ' || compte;
end if;

nbgroupeappartient :=0;
nbgroupesupprime :=0;
-- Récupère la liste des groupes de l'utilisateur et le désinscrit éventuellement des groupes lecture et écriture associés à la base courante
-- SELECT roleid as groupeid,member as utilisateur, pg_roles.rolname as user, pg_roles2.rolname as groupe
-- from pg_roles inner join pg_auth_members on pg_auth_members.member = pg_roles.oid
-- inner join pg_roles as pg_roles2 on pg_roles2.oid = pg_auth_members.roleid
-- where pg_roles.rolname = 'compteuser' ;
req := $$SELECT roleid as groupeid,member as utilisateur, pg_roles.rolname as user, pg_roles2.rolname as groupe from pg_roles inner join pg_auth_members on pg_auth_members.member = pg_roles.oid inner join pg_roles as pg_roles2 on pg_roles2.oid = pg_auth_members.roleid where pg_roles.rolname = '$$ || compte || $$';$$;
-- désinscrit le role des groupes lecture et ou ecriture (inverse de ps_creer_compte)
FOR lignedata IN EXECUTE req LOOP -- Ne fonctionne pas en mode debug
nbgroupeappartient := nbgroupeappartient + 1; -- incrémente le nombre de groupe auquel l'utilisateur appartient.
groupeappartenance = lignedata.groupe;
   if groupeappartenance = role_lecture then
req := 'REVOKE ' || role_lecture || ' FROM ' || compte;
EXECUTE req;
       nbgroupesupprime := nbgroupesupprime + 1; -- incrémente le nombre de groupe desquels la fonction a désinscrit l'utilisateur
   end if;
   if groupeappartenance = role_ecriture then
req := 'REVOKE ' || role_ecriture || ' FROM ' || compte;
EXECUTE req;
       nbgroupesupprime := nbgroupesupprime + 1; -- incrémente le nombre de groupe desquels la fonction a désinscrit l'utilisateur
   end if;
   RAISE NOTICE '%',lignedata.groupe;
END LOOP;

-- réassigne les objets possédés par le compte dans la base courante au groupe admin
req := $$ REASSIGN OWNED BY $$ || compte || $$ to $$ || role_admin || $$;$$;
EXECUTE req;
-- supprime les droits par défaut definis par les procedures ps_creer_compte ou ps_etendre_compte
req := $$ ALTER DEFAULT PRIVILEGES FOR ROLE $$ || compte || $$ REVOKE ALL ON TABLES FROM $$ || role_lecture || $$ , $$ || role_ecriture  ||$$;$$;
EXECUTE req;
-- Analyse si le compte est en condition d'être supprimé : seuls les comptes faisant partie uniquement du groupe _lecture et / ou _ecriture peuvent
-- être supprimés par ce traitement. Les comptes faisant partie d'au moins un autre groupe ou sans aucun groupe ne peuvent être supprimés par ce traitement.

if nbgroupeappartient = 0 then
return 'Le compte ne peut être supprimé - il ne fait partie d aucun groupe (_lecture ou _ecriture) de la base courante' || compte;
end if;
if nbgroupeappartient <> nbgroupesupprime then
return 'Ce compte appartient encore a des groupes non gérés par cette procédure (autres bases ou cas particulier) et ne peut être supprimé ' || compte;
end if;

req := $$DROP ROLE $$ || compte;
EXECUTE req;

RAISE NOTICE 'Suppression du compte - % ',req;
return 'Suppression OK ' || compte;

EXCEPTION
WHEN OTHERS THEN
 raise notice '% % - %', sqlstate, sqlerrm,req;

RETURN 'Suppression en erreur ' || compte || ' - ' || sqlerrm;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
PARALLEL UNSAFE
COST 100;

COMMENT ON FUNCTION public.ps_supprimer_compte(compte text)
IS 'Suppression d''un rôle de la base en vérifiant son appartenance à certains groupes (désinscription, suppression des droits par défaut et reassign des objets détenus';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com)