db_venik

Function: ps_creer_compte

Schema

public

Owner

postgres

Descriptions

Fonction de création d'un compte utilisateur avec inscription dans un groupe prédéfini et positionnement des droits par défaut

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_creer_compte (
 compte text,
 mdp text,
 statut integer = 0
)
RETURNS text LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS'
-- Auteur : Alain Benard
-- Date de mise à jour : 29/06/2022
-- Cette procédure sert à créer un rôle et à le placer dans un groupe adapté et prédéfini.
--
-- paramètres :
-- compte : login de l''utilisateur à créer
-- mdp : mot de passe à initialiser pour le compte.
-- statut : 0 pour une inscription dans le groupe de lecture
--  1 pour une inscription dans le groupe d''écriture

-- Mises à jour :
-- - 10/11/2020 : inscription du groupe base_admin comme membre du nouveau role pour faciliter la délégation notamment la suppression d''un compte qui aurait créé des objets.
-- - 17/11/2020 : définition des droits par défaut pour les groupes lecture et ecriture.
-- - 29/06/2022 : mise en commentaire de la modification du 10/11/2020 qui introduit un effet de bord sur la sécurité.
-- 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.ps_creer_compte(compte text, mdp text, statut integer) OWNER TO postgres;
-- GRANT EXECUTE ON FUNCTION public.ps_creer_compte(compte text, mdp text, statut integer) TO base_admin;
-- REVOKE EXECUTE ON FUNCTION public.ps_creer_compte(compte text, mdp text, statut integer) FROM PUBLIC;


-- déclaration des variables de traitements
DECLARE
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
   req text;
base text; -- nom de la base de données courante sans le prefixe

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

 CASE -- Préparation de la requête à exécuter
     WHEN statut = 0 THEN
         req := $$CREATE ROLE $$ || compte || $$ LOGIN PASSWORD ''$$ || mdp || $$'' IN ROLE $$ || role_lecture || $$ ;$$;
     WHEN statut = 1 THEN
         req := $$CREATE ROLE $$ || compte || $$ LOGIN PASSWORD ''$$ || mdp || $$'' IN ROLE $$ || role_ecriture || $$ ;$$;
     else
         return ''paramètre statut doit prendre la valeur 0 pour lecture ou 1 pour écriture'';
 END CASE;

 EXECUTE req;
 
 -- Inscription du groupe des gestionnaires comme membre du role cree pour permettre  appropriation et gestion des droits sur les objets que le nouveau role pourra creer.
 -- Ce code est finalement commenté car il introduit une faille. En effet le role admin étant membre du role utilisateur il hérite de l''appartenance
 -- de ce role à des roles concernant des bases ( celà ose problème notamment dans le cas où il y a des role admin de différentes bases
 -- qui ne se recouvrent pas et que chacun veut accorder des droits à un même usager.
 -- Au final la procédure s''exécutant en tant que postgres les droits par défaut peuvent bien être définis. Seule une future appropriation
 -- des objets possédés ne sera pas possible.
 --req := $$GRANT $$ || compte || $$ to $$ || role_admin || $$ WITH ADMIN OPTION;$$;
 --EXECUTE req;
 
 -- Definition des droits par defaut sur les tables et vues
 req := $$ ALTER DEFAULT PRIVILEGES FOR ROLE $$ || compte || $$ GRANT SELECT ON TABLES TO $$ || role_lecture || $$;$$;
 EXECUTE req;
 req := $$ ALTER DEFAULT PRIVILEGES FOR ROLE $$ || compte || $$ GRANT ALL ON TABLES TO $$ || role_ecriture || $$;$$;
 EXECUTE req;
 
 RAISE NOTICE ''Creation du compte - % '',req;
 return ''Création OK '' || req;

EXCEPTION
WHEN OTHERS THEN
raise notice ''% % - %'', sqlstate, sqlerrm,req;
RETURN ''Création en erreur '' || req || '' - '' || sqlerrm;
END;
';

COMMENT ON FUNCTION public.ps_creer_compte(compte text, mdp text, statut integer)
IS 'Fonction de création d''un compte utilisateur avec inscription dans un groupe prédéfini et positionnement des droits par défaut';

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