pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedTR: Re: TR: interface PERL and return results
6+ messages / 3 participants
[nested] [flat]
* TR: Re: TR: interface PERL and return results
@ 2002-10-10 22:29 victor3.lopes@voila.fr <victor3.lopes@voila.fr>
0 siblings, 1 reply; 6+ messages in thread
From: victor3.lopes@voila.fr @ 2002-10-10 22:29 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: nandrews@investsystems.co.uk
Hello, anyone have a response of my problem ?
---------- Entête Initiale -----------
De : "victor3.lopes@voila.fr" <victor3.lopes@voila.fr>
A : "nandrews" <nandrews@investsystems.co.uk>
Copie : "pgsql-interfaces" <pgsql-interfaces@postgresql.org>
Date : Wed, 9 Oct 2002 13:38:40 +0200
Objet : Re: [INTERFACES] TR: interface PERL and return results
Bonjour (you speak french ? ;-) )
Thanks for your response,
Indeed I confused two commands copy (libpq) and \copy (psql).
I have try two methods for charge my result.res in my table.
the first method :
$res = $db->exec(" COPY temp FROM "full path\result.res ");
Unfortunately I did not succeed in using this method, I had always an
error ( While placing postgres in debug mode ).
DEBUG: StartTransactionCommand
DEBUG: query: COPY temp FROM '-- my path -- /result.res'
DEBUG: parse tree: { QUERY :command 5 :utility ? :resultRelation 0 :into <>
:isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
<> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()}
DEBUG: ProcessUtility: COPY temp FROM '-- my path -- /result.res'
ERROR: You must have Postgres superuser privilege to do a COPY directly to or from a
file. Anyone can COPY to stdout or from stdin. Psql's \copy command also works for
anyone.
....
I don't understand why this error because the superuser postgres (who had create for
launch the server) have the privilege
for access and read this file.
The second method :
$res = $db->exec('COPY temp FROM stdin');
open (FIC,'<result.res');
while (<FIC>) {
$db->putline($_);
}
$db->putline("\\.\n");
close(FIC);
print $res->resultStatus;
This method works with success, but i have some questions.
- print $res->resultStatus; print PGRES_COPY_IN
But i don't kown if the action of copy achieve its goal or no. I post this question
because i has tested this case
after insert an row error on file and the response had the same.
Documentation indicates that one would have to be inserted a command $res =
$db->endcopy for synchronize the copy
instruction. But when i use this instruction at end of my code., the prog perl blocks
itself and if i kill, the operation of copy
fails on postgres.
If i omit this instruction the process works fine. Must I use or not this instruction ?
Victor
>
>
> Bonjour,
>
> On Mon, 7 Oct 2002, [utf-8] victor3.lopes@voila.fr wrote:
>
> > Hello no one has a solution for my problem :-) ?
> >
> >
> > ---------- Entête Initiale -----------
> >
> > De : "victor3.lopes@voila.fr" <victor3.lopes@voila.fr>
> > A : "pgsql-interfaces" <pgsql-interfaces@postgresql.org>
> > Copie :
> > Date : Sat, 5 Oct 2002 23:31:40 +0200
> > Objet : interface PERL and return results
> >
> > Hello,
> > I use Perl Pg interface to join my postgresql database.
> >
> > I wan't send the below query in perl :
> >
> > $res = $db->exec(' create table temp (....); \copy temp from result.res');
>
> The \copy is a psql command. You need to use just the word 'copy' without the
> leading '\'.
>
> Using:
>
> COPY temp FROM /path/to/result.res
>
> will try and read result.res from a file on the server system. To load a file
> on your client system you should use COPY temp FROM STDIN and read the Pg
> manpage.
>
> Documentation:
>
> http://developer.postgresql.org/docs/postgres/sql-copy.html
>
> and
>
> man Pg
>
> search for 'copy' in the second of these to get a good starting point on the
> subject.
>
>
> --
> Nigel J. Andrews
> Director
>
> ---
> Logictree Systems Limited
> Computer Consultants
>
>
>
------------------------------------------
Faites un voeu et puis Voila ! www.voila.fr
------------------------------------------
Faites un voeu et puis Voila ! www.voila.fr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Re: TR: interface PERL and return results
@ 2002-10-11 05:56 rmunn@pobox.com
parent: victor3.lopes@voila.fr <victor3.lopes@voila.fr>
0 siblings, 1 reply; 6+ messages in thread
From: rmunn@pobox.com @ 2002-10-11 05:56 UTC (permalink / raw)
To: victor3.lopes@voila.fr <victor3.lopes@voila.fr>; +Cc: pgsql-interfaces
On Fri, Oct 11, 2002 at 12:29:40AM +0200, victor3.lopes@voila.fr wrote:
> Hello, anyone have a response of my problem ?
Maybe. See below...
>
> I have try two methods for charge my result.res in my table.
>
> the first method :
>
> $res = $db->exec(" COPY temp FROM "full path\result.res ");
>
> Unfortunately I did not succeed in using this method, I had always an
> error ( While placing postgres in debug mode ).
>
> DEBUG: StartTransactionCommand
> DEBUG: query: COPY temp FROM '-- my path -- /result.res'
> DEBUG: parse tree: { QUERY :command 5 :utility ? :resultRelation 0 :into <>
> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
> <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
> :resultRelations ()}
> DEBUG: ProcessUtility: COPY temp FROM '-- my path -- /result.res'
> ERROR: You must have Postgres superuser privilege to do a COPY directly to or from a
> file. Anyone can COPY to stdout or from stdin. Psql's \copy command also works for
> anyone.
> ....
>
> I don't understand why this error because the superuser postgres (who had create for
> launch the server) have the privilege
> for access and read this file.
In your Perl script, what user and password did you use to connect to
the database? You need to *connect* as the Postgres superuser (which is
almost always the user named 'postgres').
If you are connected as user postgres then your COPY command should
work.
--
Robin Munn
rmunn@pobox.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Re: TR: interface PERL and return results
@ 2002-10-11 08:34 victor3.lopes@voila.fr <victor3.lopes@voila.fr>
0 siblings, 1 reply; 6+ messages in thread
From: victor3.lopes@voila.fr @ 2002-10-11 08:34 UTC (permalink / raw)
To: rmunn <rmunn@pobox.com>; +Cc: pgsql-interfaces
Hello
I have found a solution for my second problem.
Indeed is necessary to use a endcopy command when we use
a copy instruction.
The code should be this :
$res = $db->exec('COPY temp FROM stdin');
open (FIC,'<result.res');
while (<FIC>) {
$db->putline($_);
}
$db->putline("\\.\n");
$db->endcopy;
close(FIC);
The most important is the $db->putline("\\.\n"); with \n before use endcopy. My error had to omit this :-)
Today my copy code works fine :-)
> On Fri, Oct 11, 2002 at 12:29:40AM +0200, victor3.lopes@voila.fr wrote:
> > Hello, anyone have a response of my problem ?
>
> Maybe. See below...
> >
> > I have try two methods for charge my result.res in my table.
> >
> > the first method :
> >
> > $res = $db->exec(" COPY temp FROM "full path\result.res ");
> >
> > Unfortunately I did not succeed in using this method, I had always an
> > error ( While placing postgres in debug mode ).
> >
> > DEBUG: StartTransactionCommand
> > DEBUG: query: COPY temp FROM '-- my path -- /result.res'
> > DEBUG: parse tree: { QUERY :command 5 :utility ? :resultRelation 0 :into <>
> > :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
> > <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
> > :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
> > :resultRelations ()}
> > DEBUG: ProcessUtility: COPY temp FROM '-- my path -- /result.res'
> > ERROR: You must have Postgres superuser privilege to do a COPY directly to or from a
> > file. Anyone can COPY to stdout or from stdin. Psql's \copy command also works for
> > anyone.
> > ....
> >
> > I don't understand why this error because the superuser postgres (who had create for
> > launch the server) have the privilege
> > for access and read this file.
>
> In your Perl script, what user and password did you use to connect to
> the database?
Indeed my user is http and is not the superuser. In my host superuser postgresql is user postgres.
>You need to *connect* as the Postgres superuser (which is
> almost always the user named 'postgres').
>
> If you are connected as user postgres then your COPY command should
> work.
>
> --
> Robin Munn
> rmunn@pobox.com
Ok, thanks for this information, but i find it's a pity because on all my code i use the user http for to access
tables and I do not see why it would be necessary that I connect differently to use command Copy : - ( for
access directly to my file.
Perhaps a problem of security ?
Thanks
Victor
------------------------------------------
Faites un voeu et puis Voila ! www.voila.fr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Re: TR: interface PERL and return results
@ 2002-10-11 14:22 rmunn@pobox.com
parent: victor3.lopes@voila.fr <victor3.lopes@voila.fr>
0 siblings, 0 replies; 6+ messages in thread
From: rmunn@pobox.com @ 2002-10-11 14:22 UTC (permalink / raw)
To: victor3.lopes@voila.fr <victor3.lopes@voila.fr>; +Cc: pgsql-interfaces
On Fri, Oct 11, 2002 at 10:34:43AM +0200, victor3.lopes@voila.fr wrote:
> > > DEBUG: ProcessUtility: COPY temp FROM '-- my path -- /result.res'
> > > ERROR: You must have Postgres superuser privilege to do a COPY directly to or from a
> > > file. Anyone can COPY to stdout or from stdin. Psql's \copy command also works for
> > > anyone.
> > > ....
> > >
> > > I don't understand why this error because the superuser postgres (who had create for
> > > launch the server) have the privilege
> > > for access and read this file.
> >
> > In your Perl script, what user and password did you use to connect to
> > the database?
> Indeed my user is http and is not the superuser. In my host superuser postgresql is user postgres.
>
> >You need to *connect* as the Postgres superuser (which is
> > almost always the user named 'postgres').
> >
> > If you are connected as user postgres then your COPY command should
> > work.
> >
> > --
> > Robin Munn
> > rmunn@pobox.com
>
> Ok, thanks for this information, but i find it's a pity because on all my code i use the user http for to access
> tables and I do not see why it would be necessary that I connect differently to use command Copy : - ( for
> access directly to my file.
> Perhaps a problem of security ?
Yes, the reason for this is security reasons. If anyone could read from
any file, then it would be easy to read, say, '/etc/passwd' into a
database table. And if anyone could write to any file, you might be able
to clobber important security files like /etc/passwd, or just do a
denial-of-service attack by writing gigabytes and gigabytes until the
disk fills up.
Anytime you give direct access to the hard disk, in any way at all, to a
user connecting from the Web, you have created a LOT of security
problems. That's why file access is limited only to the postgres
superuser. It's better not to allow file access from the web AT ALL, but
if you really must, then you can use the postgres superuser -- but be
certain that you know what you're doing.
--
Robin Munn
rmunn@pobox.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Re: TR: interface PERL and return results
@ 2002-10-11 14:31 Jean-Luc Lachance <jllachan@nsd.ca>
parent: rmunn@pobox.com
0 siblings, 0 replies; 6+ messages in thread
From: Jean-Luc Lachance @ 2002-10-11 14:31 UTC (permalink / raw)
To: rmunn@pobox.com; +Cc: victor3.lopes@voila.fr <victor3.lopes@voila.fr>; pgsql-interfaces
Two things:
The file MUST be on the server where PostgreSQL is running.
The file MUST be accessible by the user postgres.
rmunn@pobox.com wrote:
>
> On Fri, Oct 11, 2002 at 12:29:40AM +0200, victor3.lopes@voila.fr wrote:
> > Hello, anyone have a response of my problem ?
>
> Maybe. See below...
> >
> > I have try two methods for charge my result.res in my table.
> >
> > the first method :
> >
> > $res = $db->exec(" COPY temp FROM "full path\result.res ");
> >
> > Unfortunately I did not succeed in using this method, I had always an
> > error ( While placing postgres in debug mode ).
> >
> > DEBUG: StartTransactionCommand
> > DEBUG: query: COPY temp FROM '-- my path -- /result.res'
> > DEBUG: parse tree: { QUERY :command 5 :utility ? :resultRelation 0 :into <>
> > :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
> > <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
> > :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
> > :resultRelations ()}
> > DEBUG: ProcessUtility: COPY temp FROM '-- my path -- /result.res'
> > ERROR: You must have Postgres superuser privilege to do a COPY directly to or from a
> > file. Anyone can COPY to stdout or from stdin. Psql's \copy command also works for
> > anyone.
> > ....
> >
> > I don't understand why this error because the superuser postgres (who had create for
> > launch the server) have the privilege
> > for access and read this file.
>
> In your Perl script, what user and password did you use to connect to
> the database? You need to *connect* as the Postgres superuser (which is
> almost always the user named 'postgres').
>
> If you are connected as user postgres then your COPY command should
> work.
>
> --
> Robin Munn
> rmunn@pobox.com
>
> ------------------------------------------------------------------------
> Part 1.2Type: application/pgp-signature
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Re: TR: interface PERL and return results
@ 2002-10-11 22:36 victor3.lopes@voila.fr <victor3.lopes@voila.fr>
0 siblings, 0 replies; 6+ messages in thread
From: victor3.lopes@voila.fr @ 2002-10-11 22:36 UTC (permalink / raw)
To: rmunn <rmunn@pobox.com>; +Cc: pgsql-interfaces
Ok I understands better maintaining this constraint.
This is why I would use the first method across <STDIN> functionnality :-)
Thank you with all the people during this wire to have helped me in this investigation :-)
Victor
* From: rmunn@pobox.com
* To: "victor3.lopes@voila.fr" <victor3.lopes@voila.fr>
* Subject: Re: TR: Re: TR: interface PERL and return results
* Date: Fri, 11 Oct 2002 09:22:46 -0500
> Yes, the reason for this is security reasons. If anyone could read from
> any file, then it would be easy to read, say, '/etc/passwd' into a
> database table. And if anyone could write to any file, you might be able
> to clobber important security files like /etc/passwd, or just do a
> denial-of-service attack by writing gigabytes and gigabytes until the
> disk fills up.
>
> Anytime you give direct access to the hard disk, in any way at all, to a
> user connecting from the Web, you have created a LOT of security
> problems. That's why file access is limited only to the postgres
> superuser. It's better not to allow file access from the web AT ALL, but
> if you really must, then you can use the postgres superuser -- but be
> certain that you know what you're doing.
>
> --
> Robin Munn
>rmunn@pobox.com
> Attachment: pgp00003.pgp
> Description: PGP signature
------------------------------------------
Faites un voeu et puis Voila ! www.voila.fr
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-10-11 22:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-10 22:29 TR: Re: TR: interface PERL and return results victor3.lopes@voila.fr <victor3.lopes@voila.fr>
2002-10-11 05:56 ` Re: TR: Re: TR: interface PERL and return results rmunn@pobox.com
2002-10-11 14:31 ` Re: TR: Re: TR: interface PERL and return results Jean-Luc Lachance <jllachan@nsd.ca>
2002-10-11 08:34 Re: TR: Re: TR: interface PERL and return results victor3.lopes@voila.fr <victor3.lopes@voila.fr>
2002-10-11 14:22 ` Re: TR: Re: TR: interface PERL and return results rmunn@pobox.com
2002-10-11 22:36 Re: TR: Re: TR: interface PERL and return results victor3.lopes@voila.fr <victor3.lopes@voila.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox