db_renfor

Function: ps_creer_compte

Schema

public

Owner

postgres

Descriptions

Fonction de création d'un compte utilisateur avec inscription dans des groupes prédéfinis. Si le troisième paramètre 'statut' vaut 0 alors le role sera inscrit dans le groupe lecture et s'il vaut 1 il sera inscrit dans le groupe ecriture. Le paramètre booleen ldap indique si le role sera inscrit dans le groupe ldap (mot de passe ignoré) ou dans le groupe nonldap. Enfin le parmaètre optionnel de validité au format 'YYYY-MM-JJ' positionne une éventuelle date de validité du role.

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_creer_compte (
 compte text,
 mdp text,
 statut integer = 0,
 ldap boolean = true,
 limite date = NULL::date
)
RETURNS text LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS
$body$
-- Auteur : Alain Benard
-- Date de mise à jour : 01/03/2024
-- 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
-- ldap : booleen (true ou false) qui indique si le role dispose d'un login ldap inrae
-- limite : date sous la forme 'YYYY-MM-DD' pour fixer éventuellement une date limite de validité du compte.

-- 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é.
-- - 01/03/2024 : prise en compte de l'authentification ldap et d'une dte de validité optionnelle.
-- 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
   role_ldap text; -- nom du groupe des utilisateurs s'authentifiant via le ldap inrae pour la connexion à cette base
   role_nonldap text; -- nom du groupe des utilisateurs ne pouvant s'authentifier via le ldap inrae pour la connexion à cette base
   role_connexion text; -- variable temporaire pour construire la chaine des groupes à intégrer.
   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_ldap = base || '_ldap';
role_nonldap = base || '_nonldap';
role_admin = base || '_admin';

 IF ldap = FALSE THEN
if length(mdp) < 8 THEN
    RAISE NOTICE 'Mot de passe trop court';
  return 'Création en erreur : longueur de mot de passe inférieur à 8';
   ELSE
  req := 'CREATE ROLE ' || compte || ' LOGIN PASSWORD ''' || mdp || '''' ;
    role_connexion = ' ' || role_nonldap || ',';
   end if;
 ELSE
   req := 'CREATE ROLE ' || compte || ' LOGIN ';
   role_connexion = ' ' || role_ldap || ',';
 end if;
 req := req || ' IN ROLE ' || role_connexion;

 CASE -- Préparation de la requête à exécuter
     WHEN statut = 0 THEN
         req := req || role_lecture ;
     WHEN statut = 1 THEN
         req := req || role_ecriture ;
     else
         return 'paramètre statut doit prendre la valeur 0 pour lecture ou 1 pour écriture';
 END CASE;
 
 -- Existence d'une date de validité
 if limite is not null THEN
  req := req || ' valid until ''' || limite || '''' || ';';
 else
  req := req || ';';
 end if;
 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à pose 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;
 
 --bRAISE NOTICE 'Creation du compte - % ',req;
 return 'Création OK ';

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

COMMENT ON FUNCTION public.ps_creer_compte(compte text, mdp text, statut integer, ldap boolean, limite date)
IS 'Fonction de création d''un compte utilisateur avec inscription dans des groupes prédéfinis. Si le troisième paramètre ''statut'' vaut 0 alors le role sera inscrit dans le groupe lecture et s''il vaut 1 il sera inscrit dans le groupe ecriture. Le paramètre booleen ldap indique si le role sera inscrit dans le groupe ldap (mot de passe ignoré) ou dans le groupe nonldap. Enfin le parmaètre optionnel de validité au format ''YYYY-MM-JJ'' positionne une éventuelle date de validité du role.';

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