Vous n'êtes pas identifié(e).
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
Hors ligne
Bonjour,
pouvez vous donner des précisions et notamment les requêtes effectuées et au minimum la ou les erreurs rapportées ?
Éric
Hors ligne
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)
Hors ligne
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
Hors ligne
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.
Hors ligne
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
Hors ligne
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)
Hors ligne
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
Hors ligne
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
Hors ligne
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.
Hors ligne
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
Hors ligne
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
Hors ligne
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
Hors ligne
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?
Hors ligne
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
Hors ligne
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 survenueAvec 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.
Hors ligne
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
Hors ligne
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?
Hors ligne
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
Hors ligne
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
Hors ligne
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
Hors ligne
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
Hors ligne
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.
Hors ligne
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
Hors ligne
Changer à quel niveau ?
Guillaume.
Hors ligne