Discussion :

[klereth]

Bonjour!

j'ai un petit problème de copie d'un fichier dans une table avec la commande COPY de Postgres :
En fait j'ai un programme Java dans lequel j'écris :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

monstatement.executeUpdate("copy matable from 'monchemin'")

Sauf que Postgres n'est pas d'accord et me dit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ERREUR:  doit être superutilisateur pour utiliser COPY à partir ou vers un fichier
HINT:  Tout le monde peut utiliser COPY vers stdout ou partir de stdin.La commande \copy de psql fonctionne aussi pour tout le monde.

Comme je n'ai pas très envie que l'utilisateur de mon appli aie les pouvoirs d'un superutilisateur, je remplace mon code java par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

monstatement.executeUpdate("\copy matable from 'monchemin'")

Evidemment ça ne compile même pas, puisque Java me dit que sur "\c" il y a un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

illegal escape character

Il faut donc que je protège mon "\". Je continue avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

monstatement.executeUpdate("\\copy matable from 'monchemin'")

Mais là, à l'exécution j'ai l'erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"

Et rajouter des "\" n'y change rien : soit ça compile pas, soit j'ai cette erreur qui m'empêche donc totalement d'utilisater copy.
Je précise qu'écrire la commande "\copy matable from 'monchemin'" à la main dans psql marche très bien.
Est-ce que quelqu'un aurait par hasard eu le même souci que moi et serait arrivé à s'en sortir ? Ou bien si quelqu'un a une idée sur quoi faire, ça me dit bien aussi

Merci d'avance!

[GrandFather]

Bonjour,

HINT: Tout le monde peut utiliser COPY vers stdout ou partir de stdin.La commande \copy de psql fonctionne aussi pour tout le monde.

Comme te l'indique ce message de Postgres, la syntaxe \copy est destinée à être utilisée avec psql, l'utilitaire fourni avec la base de données, mais certainement pas dans une requête lancée depuis une application cliente.

Tu n'as donc pas le choix : si tu veux utiliser COPY à partir d'un front-end écrit en Java, ou quoi que ce soit d'autre, la connexion à la base doit se faire avec un compte de super-utilisateur (généralement l'utilisateur "postgres").

Il existe peut-être cependant une voie de contournement : lancer la commande COPY depuis une procédure stockée, qui peut elle être lancée par un utilisateur lambda. Ca fonctionne avec la version 7.3 de Postgres, mais je ne suis pas sûr que cette faille de sécurité existe toujours dans les versions ultérieures...

[klereth]

Merci beaucoup pour ta réponse.

Le problème c'est que je n'ai pas les droits pour insérer le langage "plpgsql' dans la base postgres, alors avant de faire tout un remue-ménage pour essayer de les avoir auprès de mes chefs, j'aurais voulu savoir si quelqu'un aurait une solution plus simple à mon problème :
j'ai une base de données de production, et une base de données de consultation. D'autre part j'ai mon appli Java, et de temps en temps depuis l'appli quelqu'un clique sur un bouton "Update" qui met à jour les données de la base de consultation depuis la base de production. Pour faire basculer les valeurs d'une base à une autre, je voulais utiliser la commande "copy" qui apparemment ne marche pas si je ne suis pas superutilisateur.
Y a-t-il un autre moyen d'effectuer cette copie géante ?

Merci encore!

[GrandFather]

Il n'y a aucune obligation à ce que ta procédure stockée soit écrite en plpgsql, elle peut l'être en SQL pur.

[klereth]

En fait je n'ai jamais écrit de procédure stockée alors je ne sais pas trop comment m'y prendre, d'autant plus qu'en cherchant sur google je n'en trouve des exemples qu'en plpgsql.
Je me demande même si cette procédure stockée marcherait : je pensais faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
create or replace function copyFromFile(varchar, varchar) returns integer as
begin
  copy $1 from '$2';
end
language 'sql';

J'ai une syntax error rien qu'au niveau de begin, que je mette l'ensemble à partir de "as" entre apostrophes ou non. Peut-être que je m'y prends mal ? Pourrais-tu m'aider un peu là-dessus ?[/code]

[GrandFather]

En fait, je me suis un peu avancé : ta fonction ne peut pas être écrite en SQL pur si la table et/ou le fichier à intégrer sont paramétrables, cela empêchant la compilation de la requête. Il faudra donc obligatoirement passer par plpgsql.

[klereth]

Je suis arrivée à installer plpgsql chez moi, et comme je tatonne un peu une aide serait la bienvenue
J'ai écrit la procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
create or replace function copyFromFile(varchar, varchar) returns int4 as '
begin
  \copy $1 from \'$2\'
  return 1;
end;
' language 'plpgsql';

Puis je fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
select copyFromFile(site,'/tmp/test.dmp') as res;
ERROR:  column "site" does not exist

Je ne comprends pas pourquoi il s'attend à ce que 'site' soit une colonne ???
Et puis puis-je vraiment utiliser la commande \copy dans une procédure stockée ? (si j'utilise copy tout court j'imagine que ça ne marchera pas avec quelqu'un qui n'est pas super utilisateur)
Si quelqu'un peut m'éclairer...

[GrandFather]

Bon, je me répète un peu : \copy ne doit pas être utilisé dans une requête, mais uniquement comme une commande de psql !

D'autre part, on ne peut, avec plpgsql, exécuter une requête prenant des paramètres comme nom de table, car cela l'empêche de compiler et planifier l'exécution de cette requête. On peut cependant lancer une requête constituée dynamiquement, en utilisant PERFORM. Cette solution a également l'avantage (?) de court-circuiter la restriction de sécurité liée à COPY.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
create or replace function copyFromFile(varchar, varchar) returns int4 as ' 
begin 
  PERFORM ''COPY '' ||  $1 || '' FROM '''' || $2 || ''''''; 
  return 1; 
end; 
' language 'plpgsql';

Il est possible que ce code pose des problèmes au niveau du nombre des quotes, je n'ai pas la possibilité de le tester.

[kryskool]

Bonjour

le \copy est réservé à l'outil psql donc non utilisable dans une requête, par contre le mot cléf COPY existe.

Extrait de la doc.

COPY nomtable [ ( colonne [, ...] ) ]
FROM { 'nomfichier' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'délimiteur' ]
[ NULL [ AS ] 'chaîne
NULL' ] ]

COPY nomtable [ ( colonne [, ...] ) ]
TO { 'nomfichier' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'délimiteur' ]
[ NULL [ AS ] 'chaîne
NULL' ] ]

Donc en tant que requête tu peux lancer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

COPY matable FROM '/mon_chemin/mon_fichier.sql' WITH DELIMITER ';';

Comme cette exemple de la doc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

COPY pays FROM '/usr1/proj/bray/sql/pays_donnees';

[klereth]

Merci beaucoup pour votre aide!
Grâce à la procédure stockée, tout marche très bien.
J'ai donc créé la procédure :

create or replace function copyFromFile(text,text) returns int4 as '
begin
EXECUTE 'COPY \' || $1 || \' FROM \'\'\' || $2 || \'\'\' with delimiter \'\';\'\'\';
return 1;
end
' language 'plpgsql';

Et en lançant depuis mon code Java la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select copyFromFile(\'" + table + "\',\'" + fichier + "\') as res

ça marche à merveille!

Donc merci pour l'astuce

[klereth]

Hello,
un petit up parce qu'en fait avec postgres 8 la faille de sécurité (celle qui faisait qu'auparavant n'importe qui pouvait lancer une procédure stockée contenant des requêtes à exécution protégée) n'existe plus!!!
donc il n'y a plus moyen de lancer "copy" depuis une procédure stockée par quelqu'un qui n'est pas superutilisateur...
si quelqu'un a donc une idée de rechange, je prends!!!
merci d'avance...