Forums PostgreSQL.fr

caius

Membre

shell commande \copy sans être superuser

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 !!

dverite

Membre

Re : shell commande \copy sans être superuser

\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/

rjuju

Administrateur

Re : shell commande \copy sans être superuser

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/

caius

Membre

Re : shell commande \copy sans être superuser

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 !

caius

Membre

Re : shell commande \copy sans être superuser

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)

dverite

Membre

Re : shell commande \copy sans être superuser

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/

rjuju

Administrateur

Re : shell commande \copy sans être superuser

Ou un ALTER FUNCTION dev_evp.pg_file_read(text) OWNER TO postgres;

---

Julien.
https://rjuju.github.io/

caius

Membre

Re : shell commande \copy sans être superuser

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 :42883

alors 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 EXECUTE

mais 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

gleu

Administrateur

Re : shell commande \copy sans être superuser

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.

caius

Membre

Re : shell commande \copy sans être superuser

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

gleu

Administrateur

Re : shell commande \copy sans être superuser

Sans voir le contenu de votre fonction, difficile de dire ce qui ne va pas...

---

Guillaume.

caius

Membre

Re : shell commande \copy sans être superuser

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...

rjuju

Administrateur

Re : shell commande \copy sans être superuser

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 %I

---

Julien.
https://rjuju.github.io/

caius

Membre

Re : shell commande \copy sans être superuser

Bonjour,

désolé pour le retard. Merci beaucoup pour votre réponse c'est exactement ce qu'il me fallait !
Merci

dverite

Membre

Re : shell commande \copy sans être superuser

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/