db_venik

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 LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS'
-- 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;
';

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)