Forums PostgreSQL.fr

shishi

Membre

Ma vue sur une foreign table ne fonctionne pas

Bonjour a toutes et a tous,

je rencontre un problème avec les foreign data wrapper voici le contexte :

j'utilise la version 9.4 de postgres, j'ai créé un role de connexion applicatif en lui appliquant tous les droits.

Une fois ma foreign table créée en me connectant avec le compte applicatif j'arrive a accédé aux données.

Le problème survient quand j'essaye de créer une vue sur ma foreign table, la vue est bien présente mais impossible d'effectuer un select sur cette vue...

Quelqu'un aurait il une idée d'ou le problème peut venir?

Merci d'avance a toutes et a tous, si il vous manque des informations ou même un morceau de script n'hésitez pas.

Cordialement
Shishi

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Bonjour,

pouvez vous donner des précisions et notamment les requêtes effectuées et au minimum la ou les erreurs rapportées ?

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Merci pour votre réponse voici plus d'informations :

voici le script :

create extension postgres_fdw;

CREATE SERVER SERVER_test
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'test', port '5432', host 'xxx.xxx.xxx.xxx ');

CREATE USER MAPPING for user_app
SERVER SERVER_test
OPTIONS (user test', password 'msp');

CREATE FOREIGN TABLE TF_TEST_TES
	(
		id		serial	not null, 
		nom		INT4		not null,
	)
	SERVER testOPTIONS (schema_name 'data', table_name 'test');
	ALTER FOREIGN TABLE TF_TEST_TES OWNER TO proprietaire;
	GRANT SELECT ON TF_TEST_TES TO user_app;

CREATE VIEW VF_TEST_TES AS
		SELECT 
			id, 
			nom, 
		FROM 
			TF_TEST_TES;

	ALTER VIEW VF_TEST_TES OWNER TO proprietaire;
	GRANT SELECT ON VF_TEST_TES TO user_app;

je crois n'avoir rien oublié, il y a 2 role de connexion : "user_app" et "proprietaire"

Avec l'utilisateur "user_app" j'arrive a afficher les données directement de la foreign table, mais impossible a partir de la vue.

J'espère qu'il ne manque aucunes informations

Merci d'avance

Cordialement
Shishi

Dernière modification par shishi (05/02/2016 13:07:17)

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Merci pour ces 1ers éléments (je pensais surtout aux 2 requêtes SELECT sur la table étrangère et sur la vue ainsi qu'au(x) erreur(s) éventuelle(s) renvoyée(s) au client).

En l'état je vois que la table étrangère est créée dans le schéma courant et que la vue effectue sa requête sur une table qui est dans le schéma "symphonie". Les 2 schémas sont les mêmes ?

Et la requête dans la vue sélectionne une colonne TYPE que je ne vois pas dans la table.

Si vous pouvez montrer les 2 requêtes qui ne renvoient pas les mêmes résultats et les éventuelles erreurs ca aiderait probablement.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

j'utilise pgAdmin 3 et j'effectue un clique droit puis afficher les données mais je viens de tester avec ces requêtes  :

sur la foreign table : "select * from TF_TEST_TES" => je vois bien les données

sur la vue : "select * from VF_TEST_TES" => je ne vois pas les données

Je viens également de tester en attribuant comme propriétaire à la vue l'utilisateur user_app et la miracle j'arrive a afficher les données

Quelqu'un pourrait m'expliquer comment cela se fait que je doive absolument mettre user_app comme propriétaire?

PS : concernant le schéma "symphonie" j'ai oublié de l’effacer, je n'utilise que le schéma public, et concernant la colonne TYPE idem un oubli lors du copier/coller (car je ne voulais pas afficher toutes les colonnes ici afin d'éviter que le texte soit trop long.

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Il n'est pas nécessaire de mettre l'utilisateur comme propriétaire, l'octroi du privilège SELECT devrait suffire.

Je suppose que le GRANT SELECT ON VF_TEST_TES TO user_app n'a pas été pris en compte pour une raison ou une autre.

Sous psql (je ne sais pas avec pgAdmin3 que je n'utilise pas) vous pouvez faire un \dp VF_TEST_TES pour voir qui peut accèder la vue.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

merci pour l'information en faite j'utilise pgadmin3 pour me connecter à une vm qui possède une version n9.4 de postgres donc la commande psql donne le résultat suivant :

Schema   |      Name       | Type |          Access privileges           | Column access privileges
-----------+-----------------+------+--------------------------------------+--------------------------
public | vf_test_tes | view | proprietaire=arwdDxt/symphonie         +|
           |                 |      | user_app=r/symphonie       +|

donc apparemment le GRANT SELECT ON VF_TEST_TES est pris en compte

Encore merci pour tes suggestions

Dernière modification par shishi (05/02/2016 14:58:19)

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Bonjour a toutes et tous,

je reviens vers vous concernant mon problème de vue, après plusieurs tests je me suis aperçu que la vue s'exécutée comme si j'étais connectée avec le rôle proprietaire et non user_app (pourtant je me suis bien connecté avec le rôle user_app).
Et que de ce fait il fallait que le rôle proprietaire ait avoir les droits sur la ou les tables sur lesquelles la vue effectue sa requete (ce qui parait assez logique), or dans mon cas précis je ne fais aucun user mapping sur ma foreign table.
Je résume :
le user mapping sur le foreign server et fait uniquement pour le rôle user_app,
la vue a comme propriétaire le rôle : proprietaire et du coup en faisant mon select sur ma vue (celui-ci s’exécutant avec les droits du rôle propriétaire) du coup il se fait refouler par le foreign server ne trouvant pas de user mapping correspondant au rôle proprietaire.

Ma question est donc la suivante : Y aurait il un moyen pour dire a la vue de s'executer avec les droit du rôle connecté et non celui du propriétaire? Un peu comme pour les procedures stockée ou l'on peut définir le rôle utilisé pour l’exécuter.

Merci d'avance
Cordialement
Shishi

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Bonjour,

c'est un peu confus ;-)

Pour essayer de vous répondre, pouvez vous donner le résultat des commandes suivantes (sous psql et sous une même session):

\des
\deu
\det
\dp votre_vue
\dp la_table_referencee_par_la_vue
SELECT session_user, current_user;
SELECT une_colonne FROM votre_vue LIMIT 1;

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

voici les résultat :

\des :

db_symphonie=# \des
             List of foreign servers
      Name      |  Owner   | Foreign-data wrapper
----------------+----------+----------------------
server_test | postgres | postgres_fdw
(1 row)

\deu :

db_symphonie=# \deu
     List of user mappings
     Server     |   User name
----------------+---------------
server_test | user_app
(1 row)

\det :

db_symphonie=# \det
                List of foreign tables
  Schema   |          Table          |     Server
-----------+-------------------------+----------------
symphonie | tf_test_tes | server_test
(1 row)

\dp vf_test_tes :

db_symphonie=# \dp vf_test_tes
                          Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+------+-------------------+--------------------------
(0 rows)

\dp tf_test_tes :

db_symphonie=# \dp tf_test_tes
                          Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+------+-------------------+--------------------------
(0 rows)

SELECT session_user, current_user; :

db_symphonie=> SELECT session_user, current_user;
session_user  | current_user
---------------+---------------
user_app | user_app
(1 row)

SELECT id FROM vf_test_tes LIMIT 1; :

db_symphonie=> SELECT id FROM vf_test_tes LIMIT 1;
ERREUR:  correspondance utilisateur non trouvée pour « proprietaire »

Voila le "proprietaire" qui apparaît sur la dernière requête correspond au rôle propriétaire de la vue.

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Sous une session propriétaire octroyer les droits suivants:

GRANT SELECT ON tf_test_tes, vf_test_tes TO user_app;

puis réessayer les commandes \dp et SELECT sous une session user_app.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

j'ai executé la commande : GRANT SELECT ON tf_test_tes, vf_test_tes TO user_app;

j'ai bien obtenus GRANT sur la ligne dans dessous, puis le SELECT donne ceci :

db_symphonie=> SELECT id FROM vf_test_tes;
ERREUR:  correspondance utilisateur non trouvée pour « proprietaire »

En tout cas merci pour votre aide

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Mmm.... mystère....

Que donne le \dp sur la vue et la table après le GRANT ? Un \dv vf_test_tes également pourrait peut être aider.

Vous confirmez que pour l'utilisateur user_app un SELECT sur la table étrangère ramène des données (alors que la vue rapporte l'erreur ci-dessus) ?

Quelle est la définition de la vue si ce n'est pas confidentiel ? La vue n'utiliserait pas une fonction définie avec la clause SECURITY DEFINER ?

Vous indiquiez précédemment que vous n'utilisiez que le schema public. Pourtant je vois encore un schema symphonie dans la sortie du \det ci-dessus.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Tout d'abord je confirme qu'un select sur la table étrangère ramène bien les données alors que sur la vue j'obtiens l'erreur, concernant le schéma symphonie toutes mes tables et vue sont dans un schéma appelé symphonie avec les droits pour les utilisateurs (donc le soucis ne viens pas de la).

le \dp sur la table tf_test_tes donne ceci :

db_symphonie=> \dp tf_test_tes
                                              Access privileges
  Schema   |         Name          |     Type      |       Access privileges
   | Column access privileges
-----------+-----------------------+---------------+----------------------------
---+--------------------------
proprietaire | tf_test_tes | foreign table | proprietaire=arwdDxt/proprietaire
  +|
           |                       |               | user_app=r/proprietaire+|

   |

le \dp sur la vue vf_test_tes (avec un compte proprietaire) donne :

db_symphonie=# \dp vf_test_tes
                                       Access privileges
  Schema   |      Name       | Type |       Access privileges       | Column access privileges
-----------+-----------------+------+-------------------------------+--------------------------
symphonie | vf_test_tes | view | proprietaire=arwdDxt/proprietaire  +|
           |                 |      | user_app=r/proprietaire |
(1 row)

\dv vf_test_tes donne :
db_symphonie=> \dv vf_test_tes
               List of relations
  Schema   |      Name       | Type |   Owner
-----------+-----------------+------+-----------
symphonie | vf_test_tes | view | proprietaire
(1 row)

La vue est définie comme ceci :

CREATE VIEW symphonie.VF_TEST_TES AS
		SELECT 
			ID, 
			CODE, 
			TYPE, 
			NAME, 
		FROM 
			symphonie.TF_TEST_TES;

	ALTER VIEW symphonie.VF_TEST_TES OWNER TO proprietaire;
	GRANT SELECT ON symphonie.VF_TEST_TES TO user_app;

Pourriez vous m'expliquer ce que c'est la clause SECURITY DEFINER s'il vous plait?

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Le \dp tf_test_tes ci-dessus indique que la table se trouve dans le schema proprietaire. Votre vue fait référence à une table qui se trouve dans le schéma symphonie.
Ramenez vous de quelconques données, que ce soit sous user_app ou proprietaire, de cette vue ?

SECURITY DEFINER permet d'indiquer à PostgreSQL qu'il doit exécuter la fonction avec les privilèges du propiétaire de la fonction et pas, comme par défaut, avec les privilèges de l'utilisateur qui exécute la fonction.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

en ce qui concerne le schéma c'est une erreur de ma part (j'avais enlever et changé certain nom de groupe et de table ou colonne pour que cela paraisse plus simple mais apparement non lol)
je vous fournis le script complet :

\set proprietaire 'symphonie'
\set grpecriture 'symphonie_ecriture'
\set grplecture 'symphonie_lecture'
\set grpapp 'symphonie_app'

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

-- L'utilisation d'une transaction permet de créer toute la structure ou rien si une erreur survient, laissant la base de données dans un état stable
START TRANSACTION;

CREATE ROLE symphonie_app 
	WITH
		LOGIN
		ENCRYPTED PASSWORD 'mdp'
		INHERIT
		IN GROUP :grpecriture, :grplecture;

CREATE SCHEMA symphonie;
	ALTER SCHEMA symphonie OWNER TO :proprietaire;
	GRANT USAGE ON SCHEMA symphonie TO :grplecture;
	GRANT CREATE ON SCHEMA symphonie TO :grpecriture WITH GRANT OPTION;

create extension postgres_fdw;
CREATE SERVER SERVER_SAMPLES
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'SAMPLES', port '5432', host 'xxx.xxx.xxx.xxx ');

CREATE USER MAPPING for :grpapp
SERVER SERVER_SAMPLES
OPTIONS (user 'symphonie_samples', password 'mdp1');

CREATE FOREIGN TABLE symphonie.TF_SAMPLES_SAMPLE_SAM
	(
		SAMPLE_ID		VARCHAR(5)	not null, 
		BARCODE_FID		INT4		not null,
		SAMPLE_OPERATOR_FID 	INT4		null,
		PROJECT_FID 		INT4		null,
		TEAM_FID 		INT4		null,
		PAPER_FID		INT4		null,
		SAMPLE_NAME		VARCHAR(50)	null,
		NATURE_ENTITY		VARCHAR(100)	null,
		DESTROYED		BOOL		not null,
		QUANTITY		REAL		null,
		QUANTITY_USE		REAL		null,
		SAMPLE_UNIT		VARCHAR(100)	null,
		COUNT_ENTITY		VARCHAR(100)	null,
		ORIGIN_FID		INT4		null,
		GENETIC_CODE		VARCHAR(25)	null,
		IS_GM			BOOL		not null,
		INFO_IS_GM		TEXT		null,
		AGE			INT4		null,
		AGE_UNIT		VARCHAR(100)	null,
		CONDITIONING		VARCHAR(100)	null,
		CONDITIONING_STATE	VARCHAR(100)	null,
		DATE_CREATION		DATE		null, 
		COMMENT			TEXT		null,
		STORAGE_PLACE_FID	INT4		null,
		STORE_KEEPER_FID	INT4		null,
		DATE_STORAGE		DATE		null,
		COMMENT_STORAGE		TEXT		null
	)
	SERVER SERVER_SAMPLES OPTIONS (schema_name 'data', table_name 'sample');
	ALTER FOREIGN TABLE symphonie.TF_SAMPLES_SAMPLE_SAM OWNER TO :proprietaire;
	GRANT SELECT ON symphonie.TF_SAMPLES_SAMPLE_SAM TO :grplecture;

-- vue sur les échantillons (sample)
	CREATE VIEW symphonie.VF_SAMPLES_VSAM AS
		SELECT 
			SAMPLE_ID
		FROM 
			symphonie.TF_SAMPLES_SAMPLE_SAM AS SAM;

	ALTER VIEW symphonie.VF_SAMPLES_VSAM OWNER TO :proprietaire;
	GRANT SELECT ON symphonie.VF_SAMPLES_VSAM TO :grplecture;

COMMIT -- Enregistre la totalité du traitement si aucune erreur n'est survenue

Avec ce script (et en étant connécté avec le rôle symphonie_app) j'obtiens bien les données en faisant un select sur ma foreign table mais sur la vue j'obtiens l'erreur.

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Que vous arriviez à avoir sous le même utilisateur un select fonctionnel sur la table étrangère et pas sur la vue (qui fait un simple select sur la table) qui a  pourtant le droit SELECT.... est étonnant.

Désolé mais pour le moment je n'ai plus trop d'idées...

Ne désepérez pas il passera bien quelqu'un dans le coin qui saura mettre le doigt sur là ou ça coince.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

j'ai fait les test et lorsque je met symphonie_app en tant que propriétaire sur la vue le select renvoie bien les données, de plus si je définis un user mapping pour le rôle symphonie (le proprietaire de la vue) le select me renvoie également les donnée.

C'est pour cela que je pense que lorsque je fais le select sur la vue, il prend le rôle propriétaire et cherche le user mapping sur le foreign server et comme le rôle symphonie n'a pas de user mapping il dit qu'i lne trouve pas la correspondance.

Mais existe t-il un moyen pour dire a la vue d'utiliser les privilèges du rôle connecté et non celui du propriétaire de la vue?

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Mais existe t-il un moyen pour dire a la vue d'utiliser les privilèges du rôle connecté et non celui du propriétaire de la vue?

Mais c'est le cas automatiquement, c'est le contraire qui demande des instructions particulières (SET ROLE, SET SESSION AUTHORIZATION).

Attendons l'avis des autres participants au forum.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Si c'est le cas automatiquement c'est bizarre en effet, je pensais que l'on pouvait faire comme avec les procédure stocké, définir les privilèges utilisés.

pour info la version de postgres et la 9.4
En tout cas merci Eric pour ta patience

edlm

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Bon, je crois comprendre ce qu'il se passe. Le comportement constaté provient du système de réécriture des règles qu'utilise les vues. Voir la doc.

Les requêtes réécrites par le système de vue sont  exécutées sous l'utilisateur propriétaire de la vue.

Une des solutions est bien de mettre l'utilisateur qui accède la vue comme propriétaire ce qui n'est pas un problème a priori, si ?

Dans le cas présenté c'est très perturbant: l'utilisateur X peut accèder aux données de la table mais pas via une vue qui ne fait qu'accèder la table.

Tout cela devrait être confirmé par des utilisateurs plus expérimentés que moi.

---

Éric

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

C'est bien ce que je me disais après mes différents test et cela parait logique car selon moi une vue sert entre autre a montrer des donnée a des utilisateurs qui n'ont pas de privilège sur la table, la solution pour laquelle j'ai opté c'est de fournir un user mapping au rôle symphonie sur le foreign server et du coup tout fonctionne car j'avais pensé a votre solution mais j'aimerais bien que dans la boite pour laquelle je travaille on essaye de conserver le même propriétaire pour tous les objet de chaque base.
En attendant d'avoir l'avis d'utilisateurs plus expérimentés afin de savoir si il y a des solutions alternatives.

Merci pour le lien vers la doc je ne l'avais pas vu

gleu

Administrateur

Re : Ma vue sur une foreign table ne fonctionne pas

Le message "ERREUR:  correspondance utilisateur non trouvée pour « proprietaire »" indique que vous n'avez pas la correspondance utilisateur (user mapping) pour l'utilisateur "propriétaire". Donc, un CREATE USER MAPPING manquant.

---

Guillaume.

shishi

Membre

Re : Ma vue sur une foreign table ne fonctionne pas

Bonsoir,
merci pour votre réponse mais avec l'aide d'Eric et en testant on avait finit par le comprendre.
La question maintenant existe t-il un moyen de changer le comportement d'une vue?

Cordialement
Sébastien

gleu

Administrateur

Re : Ma vue sur une foreign table ne fonctionne pas

Changer à quel niveau ?

---

Guillaume.