Forums PostgreSQL.fr

Vinz67

Membre

Droit refusé sur une vue

Bonjour,

J'utilise Postrgre/Postgis avec QGis pour lire la donnée géographique.

J'ai créé une vue modifiable avec deux tables sous-jacentes.
Je suis propriétaire des tables et de la vue.
J'ai donné les droits select, update, insert et delete à un groupe d'utilisateur pour la vue.

Lorsque j'utilise mon compte tout se passe comme je le souhaite. Par contre lorsque j'utilise le compte utilisateur le message suivant s'affiche ; "Erreur PostGIS lors de l'ajout d'entité : ERREUR:  droit refusé pour la relation res_noeud".
Dans la doc j'ai bien vu que : "Notez que l'utilisateur réalisant l'insertion, la mise à jour ou la suppression sur la vue doit avoir les droits correspondants sur la vue. De plus, le propriétaire de la vue doit avoir les droits correspondants sur les relations sous-jacentes mais l'utilisateur réalisant la mise à jour n'a pas besoin de droits sur les relations sous-jacentes."

J'ai besoin d'aide, svp.
Je vous joins mon script SQL pour la création de la vue, de la fonction et du trigger.

 -- View: public.vue_res_eau_vanne

DROP VIEW public.vue_res_eau_vanne CASCADE;

CREATE OR REPLACE VIEW public.vue_res_eau_vanne
WITH (security_barrier=false)
 AS
 SELECT res_noeud.id_noeud,
	res_noeud.the_geom,
    res_ea_noeud_vanne.numero,
    res_ea_noeud_vanne.type,
    res_ea_noeud_vanne.code_insee,
    res_ea_noeud_vanne.adresse,
    res_ea_noeud_vanne.reseau,
    res_ea_noeud_vanne.reseau_type,
    res_ea_noeud_vanne.diametre,
    res_ea_noeud_vanne.materiau,
    res_ea_noeud_vanne.nbre_tours,
    res_ea_noeud_vanne.sens,
    res_ea_noeud_vanne.etat_normal,
    res_ea_noeud_vanne.implantation,
    res_ea_noeud_vanne.annee_pose,
    res_ea_noeud_vanne.date_controle,
    res_ea_noeud_vanne.commentaire,
    res_noeud.angle_lib
   FROM  res_noeud
   JOIN res_ea_noeud_vanne ON res_noeud.id_noeud = res_ea_noeud_vanne.id_noeud_vanne
  WHERE res_noeud.id_reseau = 5 AND res_noeud.type_noeud = 203 AND res_noeud.date_suppression IS NULL;

ALTER VIEW public.vue_res_eau_vanne
    OWNER TO smanetagis;

GRANT ALL ON TABLE public.vue_res_eau_vanne TO smanetagis;
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE public.vue_res_eau_vanne TO grp_aep_admin;



-- Crée la fonction utilisée par le déclencheur pour rendre modifiable la vue 
--DROP FUNCTION res_eau_vanne_vue_edit() CASCADE;

CREATE OR REPLACE FUNCTION res_eau_vanne_vue_edit()
RETURNS trigger AS
$BODY$

BEGIN	

    IF TG_OP = 'INSERT' THEN
        INSERT INTO public.res_noeud (
									  type_noeud,
									  id_reseau, 
									  the_geom, 
									  angle, 
									  angle_lib, 
									  date_creation, 
									  user_postgis) 
               					 VALUES (
									     203,
									 	 5, 
										 NEW.the_geom,  
										 180-NEW.angle_lib, 
										 NEW.angle_lib, 
										 CURRENT_TIMESTAMP, 
										 USER);
		INSERT INTO res_ea_noeud_vanne (id_noeud_vanne, 
										numero, 
										type, 
										code_insee, 
										adresse, 
										reseau, 
										reseau_type, 
										diametre, 
										materiau, 
										nbre_tours, 
										sens, 
										etat_normal, 
										implantation, 
										annee_pose, 
										date_controle, 
										commentaire)
								VALUES  (currval('res_noeud_id_noeud_seq'::regclass), 
										 new.numero, 
										 new.type, 
										 new.code_insee,
										 new.adresse, 
										 new.reseau, 
										 new.reseau_type, 
										 new.diametre, 
										 new.materiau, 
										 new.nbre_tours,
										 new.sens, 
										 new.etat_normal,
										 new.implantation,
										 new.annee_pose, 
										 new.date_controle,
										 new.commentaire);
        RETURN NEW;
	
    ELSIF TG_OP = 'UPDATE' THEN
		UPDATE public.res_noeud 
								SET the_geom = new.the_geom, 
								angle = 180-NEW.angle_lib, 
								angle_lib = new.angle_lib, 
								date_modification = CURRENT_TIMESTAMP, 
								user_postgis = USER
  					WHERE old.id_noeud = res_noeud.id_noeud;
					
		UPDATE public.res_ea_noeud_vanne 
								SET numero = new.numero, 
								type = new.type, 
								code_insee = new.code_insee, 
								adresse = new.adresse, 
								reseau = new.reseau, 
								reseau_type = new.reseau_type, 
								diametre = new.diametre, 
								materiau = new.materiau, 
								nbre_tours = new.nbre_tours, 
								sens = new.sens, 
								etat_normal = new.etat_normal, 
								implantation = new.implantation, 
								annee_pose = new.annee_pose, 
								date_controle = new.date_controle, 
								commentaire = new.commentaire
  					WHERE  res_ea_noeud_vanne.id_noeud_vanne = old.id_noeud ;
        RETURN NEW;
		
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM public.res_noeud WHERE  old.id_noeud = res_noeud.id_noeud;
        DELETE FROM public.res_ea_noeud_vanne WHERE  res_ea_noeud_vanne.id_noeud_vanne = old.id_noeud ;
        RETURN NULL;
    END IF;
END
$BODY$
LANGUAGE plpgsql;

-- Crée le déclencheur qui rend modifiable la vue.
-- Une vue utilisant plusieurs tables n'est pas directement modifiable.
-- Il est donc obligatoire de créer ce déclencheur pour rendre ce type de vue modifiable. 

--DROP TRIGGER res_eau_vanne_vue_edit_trig ON public.vue_res_eau_vanne;
CREATE TRIGGER res_eau_vanne_vue_trig
INSTEAD OF INSERT OR UPDATE OR DELETE 
ON public.vue_res_eau_vanne
FOR EACH ROW 
EXECUTE PROCEDURE res_eau_vanne_vue_edit(); 

Marc Cousin

Membre

Re : Droit refusé sur une vue

Je pense que le paragraphe que vous tirez de la doc, c'est pour les vues qui ne nécessitent pas de trigger instead of. Le trigger, lui, il exécute la fonction en tant que la personne connectée. À mon avis, si vous définissez la fonction comme security definer, avec comme propriétaire grp_aep_admin, ça marchera, puisque la fonction s'exécutera en tant que grp_aep_admin

---

Marc.

Vinz67

Membre

Re : Droit refusé sur une vue

Bonjour Marc et merci pour votre réponse.
Je me suis penché sur cette fonctionnalité SECURITY DEFINER mais je vous avoue que cela me dépasse un peu.
J'ai lu la doc mais je comprends pas à quel moment il faut définir le SECURITY DEFINER.
Le fait de devoir configurer search_path me déstabilise aussi. J'utilise le schéma PUBLIC donc celui-ci est parcouru un premier, non ?
Pourriez vous s'il vous plait m'indiquer les emplacements et la définition de ces paramètres dans mon script ?
Merci d'avance,
Vincent

Marc Cousin

Membre

Re : Droit refusé sur une vue

Pour le search_path vous faites référence à la doc de security definer qui vous recommande de mettre un search_path en dur ?

---

Marc.

Vinz67

Membre

Re : Droit refusé sur une vue

La doc dit :"Pour des raisons de sécurité, search_path doit être configuré pour exclure les schémas modifiables par des utilisateurs indignes de confiance."

CREATE FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT)
RETURNS BOOLEAN AS $$
DECLARE ok BOOLEAN;
BEGIN
        -- Effectuer le travail sécurisé de la fonction.
        SELECT  (motdepasse = $2) INTO ok
        FROM    motsdepasse
        WHERE   nomutilisateur = $1;

        RETURN ok;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Configure un search_path sécurisée : les schémas de confiance, puis 'pg_temp'.
    SET search_path = admin, pg_temp;

BEGIN;
CREATE FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT) TO admins;
COMMIT;

Marc Cousin

Membre

Re : Droit refusé sur une vue

C'est exactement ce que dit la doc. Quand on fait une fonction security definer, il faut mettre un search_path restrictif (idéalement, uniquement pg_catalog, si on veut vraiment être sûr), et préfixer tous les objets auxquels on accède par leur schéma pour être vraiment explicite. Vu que c'est déjà presque bon dans votre fonction (currval('res_noeud_id_noeud_seq'::regclass) par exemple ne précise pas le schéma, il faudra le corriger), il suffit de modifier l'entête de la fonction par :

CREATE OR REPLACE FUNCTION res_eau_vanne_vue_edit()
RETURNS trigger SECURITY DEFINER SET search_path= '' AS

ou quelque chose de très approchant.

Vous pouvez le mettre avant ou après le bloc AS, ça n'a aucune importance.

Une fois que vous aurez fait ça, la fonction s'exécutera en tant que son propriétaire, et non plus en tant que l'utilisateur appelant. Faites donc bien attention à ce qu'elle appartienne à un utilisateur (role) ayant accès aux objets en question en écriture.

Si l'utilisateur n'est pas bon, vous pouvez faire un ALTER FUNCTION ... OWNER TO.

Et il faudra peut-être faire un GRANT EXECUTE sur cette fonction aux utilisateurs ayant accès à la vue (par défaut ça devrait être bon).

---

Marc.

Marc Cousin

Membre

Re : Droit refusé sur une vue

La raison pour se protéger du search_path, c'est que c'est une variable de session. Donc si vous ne la forcez pas dans la fonction, un utilisateur mal intentionné pourra remplacer son search_path et essayer d'exploiter les noms d'objets sans schéma dans votre fonction, avec les droits améliorés de la fonction, vu qu'elle est security definer.

---

Marc.

Vinz67

Membre

Re : Droit refusé sur une vue

Ok j'avance.
J'ai du rajouter "public" pour le search_path parce qu'il y avait d'autres déclencheurs sur une table sous-jacente.
Le groupe grp_aep_admin est bien propriétaire de la vue et ça fonctionne comme je le souhaite … enfin presque !
En effet, en disant de mettre USER dans le champs user_postgis j'espérais pouvoir identifier le créateur de la donnée et/ou le modificateur. Avec cette sécurité c'est le groupe qui est le USER.
Avez vous un moyen de contourner ce problème et de connaitre quel utilisateur du groupe à fait la création/modifiaction ?

Marc Cousin

Membre

Re : Droit refusé sur une vue

Je pense que ce que vous voulez c'est session_user, pas current_user: https://www.postgresql.org/docs/current … -info.html

---

Marc.

Vinz67

Membre

Re : Droit refusé sur une vue

C'est nickel pour session_user.
Malheureusement, je crois que dans mon post précédent je me suis un peu enflammé.
En effet, le security definer fonctionne mais je suis pas sûre que c'est ce que je souhaitais. En relisant mon premier post je crois que je n'étais pas assez précis.
En fait, je souhaite que les utilisateurs ayant les droits puissent uniquement insert, update et delete la donnée via la vue et pas directement dans les tables sous-jacentes (res_noeud et res_ea_noeud_vanne). Ces dernières sont filtrées et conditionnées en fonction des métiers des utilisateurs.
En résumer, je veux que le user puisse insert, update et delete depuis la vue et pas depuis les tables sous-jacentes.

Mais là je crois que j'en demande un peu trop ...

Marc Cousin

Membre

Re : Droit refusé sur une vue

Non, ça doit le faire, il faut que l'utilisateur propriétaire de la fonction trigger soit autorisé à manipuler les tables en dessous, mais pas l'utliisateur qui travaille sur les vues. Lui devrait juste avoir des droits sur la vue. Ça ne marche pas ?

---

Marc.

Vinz67

Membre

Re : Droit refusé sur une vue

il faut que l'utilisateur propriétaire de la fonction trigger soit autorisé à manipuler les tables en dessous,

Ok smanetagis est propriétaire de la fonction res_eau_vanne_vue_edit() et a tous les droits sur les tables res_noeud et res_ea_noeud_vanne.

mais pas l'utliisateur qui travaille sur les vues

Ok grp_aep_admin n'a aucun droit sur les tables res_noeud et res_ea_noeud_vanne.

Lui devrait juste avoir des droits sur la vue

Ok grp_aep_admin a les droits insert, select, update et delete sur la vue.

Ça ne marche pas ?

Si, j'ai l'impression que ça fonctionne. L'erreur que j'avais faite est de donner des droits à l'utilisateur sur les deux tables.
Merci beaucoup !

Marc Cousin

Membre

Re : Droit refusé sur une vue

Ok. Un dernier piège… un utilisateur pourrait tenter, par la vue, de changer id_reseau ¸ res_noeud.type_noeud ou res_noeud.date_suppression IS NULL. Et donc faire "disparaître" des données de la vue. Il vaudrait probablement mieux se prémunir de ça dans la fonction PL (un contrôle sur les insert/update dès le départ je pense)

---

Marc.

Vinz67

Membre

Re : Droit refusé sur une vue

Ok.
Merci, c'est une bonne idée.
J'en prends note et essayerai de faire le nécessaire.