Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
je suis en train de créer une fonction permettant de lire un fichier (via une doc trouvée sur le net). Cette fonction doit être utilisée par un utilisateur de ma bdd qui n'est pas superuser, par ex toto.
La commande COPY ne le permettant pas j'ai utilisé \copy dans ma procédure :
DECLARE
contenu text;
temp_import_file text;
BEGIN
fichier := quote_nullable(fichier_lect); -- fichier_lect étant le paramètre passé à la fonction
temp_import_file := quote_ident(temp_import_abyla);
-- création d'uen table temporaire
EXECUTE format('CREATE TEMP TABLE %I (contenu text)', 'temp_import_file');
--recopie du contenu du fichier dans la table temporaire
EXECUTE format('\copy %I FROM %L DELIMITER " " ', 'temp_import_file', fichier);
--recupère le contenu de la table temp et l'ajoute à la variable 'contenu'
EXECUTE format('SELECT contenu FROM %I INTO %L', 'temp_import_file', contenu);
--supprime la table temp
EXECUTE format('drop table %I', 'temp_import_file');
-- retourne le contenu de la variable
RETURN contenu;
END;lorsque j'appelle cette fonction (en étant connecté avec l'utilisateur toto) avec un tout simple
select pg_read_file('path/to/mon/fichier')j'obtiens le message suivant : "must be superuser to read files". pourtant il me semble que la commande \copy peut être utilisé par tous les utilisateurs..
c'est quoi mon problème dans ce cas ?
Merci pour vos lumières 
!!
Hors ligne
\copy n'est pas une commande SQL utilisable dans une requête ou du code serveur.
C' est une méta-commande dans psql qui va déclencher un COPY nomtable FROM STDIN, puis va lire le fichier en local et envoyer son contenu au serveur à travers la connexion.
Pour lire un fichier du serveur il faut être superuser en effet. Si un superuser veut donner à un non-superuser le droit de le faire, la solution classique est de faire cette opération via une fonction créée pour l'occasion et avec une clause SECURITY DEFINER. Tout ce que lance la fonction sera exécuté avec des droits superuser.
Sur les versions les plus récentes de PostgreSQL (11) il y a aussi un droit spécifique pg_read_server_files qui permet de lire n'importe quel fichier.
voir https://www.postgresql.org/docs/11/default-roles.html
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Le fichier doit vraiment se trouver sur le serveur ? Si oui, vous pouvez déclarer la fonction comme SECURITY DEFINER, et la créer en tant que super utilisateur (ou membre de pg_read_server_files). Vous donnez ainsi le droit à un non super utilisateur (ou non membre de membre de pg_read_server_files) d'utiliser cette fonction en tant que super utilisateur. Bien entendu, c'est à faire avec précautions.
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour et merci pour vos réponses, désolé du retard je n'ai pas pu répondre avant.
Malheureusement je susi en version 10 de postgres et à ce que je vois il n'y a pas le pg_read_server_file.
Je vais voir ce security definer du coup !
Hors ligne
Bon alors...
j'ai modifié ma fonction :
CREATE OR REPLACE FUNCTION dev_evp.pg_file_read(fichier text)
RETURNS text
LANGUAGE 'plpgsql'
VOLATILE SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS $BODY$ DECLARE
contenu text;
temp_import_file text;
BEGIN
fichier := quote_nullable(fichier);
temp_import_file := quote_ident(temp_import_file);
-- création d'uen table temporaire
EXECUTE format('CREATE TEMP TABLE %I (contenu text)', 'temp_import_file');
--recopie du contenu du fichier dans la table temporaire
EXECUTE format('copy %I FROM %L DELIMITER " " ', 'temp_import_file', fichier);
--recupère le contenu de la table temp et l'ajoute à la variable 'contenu'
EXECUTE format('SELECT contenu FROM %I INTO %L', 'temp_import_file', contenu);
--supprime la table temp
EXECUTE format('drop table %I', 'temp_import_file');
-- retourne le contenu de la variable
RETURN contenu;
END;
$BODY$;l'option security definer si j'ai bien compris, permet d’exécuter la fonction avec les droits de l'utilisateur propriétaire. je l'ai donc crée en tant qu'utilisateur postgres. sauf que lorsque je l'appelle en tant qu'utilisateur toto, cela me dit toujours que je dois être superuser pour lire le fichier..
Je dois modifier autre chose ?
j'ai accordé les droits "execute" à postgres et à toto dans l'onglet sécurité de la fonction sur pgadmin.
merci de votre aide
Dernière modification par caius (26/04/2019 16:42:12)
Hors ligne
Je soupçonne que vous avez créé votre fonction en tant qu'utilisateur normal puis fait un CREATE OR REPLACE FUNCTION... en tant qu'utilisateur postgres. Mais cette manip ne change pas le "owner", elle garde l'utilisateur initial et ne remplace que le corps de la fonction.
Faites plutôt un DROP FUNCTION et CREATE FUNCTION en tant que postgres.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Ou un ALTER FUNCTION dev_evp.pg_file_read(text) OWNER TO postgres;
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour et merci pour vos réponses.
Effectivement, j'ai dû mal m'y prendre en créant la fonction puisque je l'avais précédemment supprimé de l'utilisateur avant de la recréer avec postgres.
Cependant, il y a un truc que je comprend pas. Quand je l'utilise dans pgadmin, il me sort une erreur :
ERROR: function pg_file_read(unknown) does not exist
LINE 1: select pg_file_read('import_test/output.html')
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
État SQL :42883alors que lorsque je l'appelle via le shell cela fonctionne même si je rencontre d'autres erreurs :
si j'enregistre dans une variable le fichier que je souhaite ouvrir de cette façon :
fichier := fichier; alors j'obtiens
ERROR: extra data after last expected column
CONTEXT: COPY temp_import_file, line 1: "1200917 C801_03 0 ANCIENNE DOCUMENTATION DU PETI C8_03 DIGNE LES BAINS BOULEVARD SAINT..."
SQL statement "COPY temp_import_file FROM 'import_test/test_sortie_fich.html'"
PL/pgSQL function dev_evrp.pg_file_read(text) line 13 at EXECUTEmais si je l'encapsule dans quote-literal ou quote-nullable, alors il me dit que le fichier n'existe pas...
que dois-je faire ?
merci d'avance
Hors ligne
Les deux erreurs n'ont rien à voir. La première indique que la fonction n'existe pas. La fonction pg_file_read semble faire partie du schéma dev_evrp. Essayez de réexécuter votre SELECT en préfixant le nom de la fonction avec le nom du schéma.
La seconde indique qu'il y a plus de colonnes qu'attendues, donc c'est qu'il arrive à lire le fichier. C'est que le contenu du fichier ne correspond pas à ce que vous indiquez à la commande COPY.
Guillaume.
Hors ligne
Bon alors effectivement il fallait préfixer pour le 1er bug mais pour le second je ne trouve pas.
en fait j'ai un fichier qui contient :
MIME-Version: 1.0
Content-Type: text/html
Content-Disposition: inline
<html>
<head>
<style>.bat{margin-left:15px;color:#00BFFF} .eta{margin-left:50px;color:#9A2EFE} .pie{margin-left:75px;color:#FE9A2E}</style>
</head>
<body><p>Bonjour !</p><br/><pre><div class="evrp_container">tout ce que je veux faire c'est l'afficher pour commencer à partir de la fonction que j'essaie de créer mais voilà il ne me retour seulement la 1ère ligne.
J'utilise sûrement mal COPY mais comment je dois faire pour récupérer tout le contenu du fichier et l’insérer dans un champ de ma table de type text pour le retourner par la suite ?
Merci de votre aide
Hors ligne
Sans voir le contenu de votre fonction, difficile de dire ce qui ne va pas...
Guillaume.
Hors ligne
la revoici :
DECLARE
contenu text;
temp_import_file text;
BEGIN
fichier := fichier;
temp_import_file := quote_ident('temp_import_file');
-- création d'une table temporaire
EXECUTE format('CREATE TEMP TABLE %I (contenu text)', temp_import_file);
--recopie du contenu du fichier dans la table temporaire
EXECUTE format('COPY %I FROM %L ',temp_import_file, fichier);
--recupère le contenu de la table temp et l'ajoute à la variable 'contenu'
EXECUTE format('SELECT contenu FROM %I ', temp_import_file)INTO contenu;
--supprime la table temp
EXECUTE format('drop table %I', temp_import_file);
-- retourne le contenu de la variable
RETURN contenu;
END;je ne récupère que la 1ere ligne de mon fichier. Je suis désolé avec toutes mes questions mais je débute en postgres...
Hors ligne
COPY créera un enregistrement par ligne. Si vous voulez tout récupérer, vous pouvez aggréger les données au moment du SELECT, par exemple
SELECT string_agg(contenu, chr(10)) FROM %IJulien.
https://rjuju.github.io/
Hors ligne
Bonjour,
désolé pour le retard. Merci beaucoup pour votre réponse c'est exactement ce qu'il me fallait !
Merci
Hors ligne
Cependant c'est fragile parce que s'il y a des antislashes dans le fichier, ils vont être interprétés par COPY, et aussi parce qu'à la restitution, il manque un tri des lignes dans l'ordre où elles étaient dans le fichier.
Le 2nd problème peut se régler en ajoutant une colonne de numéro de ligne auto-incrémentée et une clause ORDER BY dans le string_agg, mais le premier problème est plus ennuyeux. COPY est fait pour les formats tabulaires, pas pour gober un fichier sans format particulier (d'ailleurs c'est pas faute d'avoir essayé de l'ajouter à Postgres, mais ça a été rejeté). Le plus simple c'est qu'un client SQL importe ce fichier en base en le formattant comme il faut, par opposition au fait que le serveur ait l'initiative de l'import.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Pages : 1