Thread: Postgres Pg_connect PHP

Good day,

I'm having one heck of a time here. I'm looking for a clean solution
to issue a COPY statement from a remote system. (because I really
don't want to play the ssh, sudo spiel).

It's evident that pg_connect doesn't like the \copy command (it's a no
go), however it is okay with the COPY command, however the COPY
command requires superuser and I really don't want to open up the DB
from a remote system with Superuser access.

Soooo, what am I missing, misunderstanding ? There has to be a way,
seems from reading this has been brought up but I have not found a
solution.

I must be able to do this from a client system, I must be able to lock
down the db user to limited access and I can take care of permissions
on the client end, but postgres seems to be slightly rigid in regards
to the COPY command and the fact that one can't really have a jailed
superuser. I also understand that it's a pg_connect limitation to not
accept the \copy variant which would solve me issues.

Help me Rhonda, help help me..

Thanks
Tory

> I must be able to do this from a client system, I must be able to lock
> down the db user to limited access and I can take care of permissions
> on the client end, but postgres seems to be slightly rigid in regards
> to the COPY command and the fact that one can't really have a jailed
> superuser. I also understand that it's a pg_connect limitation to not
> accept the \copy variant which would solve me issues.
>
> Help me Rhonda, help help me..

http://www.php.net/manual/en/function.pg-copy-from.php

?

Joshua D. Drake



> Thanks
> Tory
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

> however the COPY
> command requires superuser and I really don't want to open up the DB
> from a remote system with Superuser access.

COPY FROM STDIN does not need superuser privileges.

On Tue, Jun 9, 2009 at 11:31 AM, Vyacheslav Kalinin<vka@mgcp.com> wrote:
>> however the COPY
>> command requires superuser and I really don't want to open up the DB
>> from a remote system with Superuser access.
>
> COPY FROM STDIN does not need superuser privileges.
>

Thanks guys, the problem with copy from or to is that it creates an
array and thus puts a load of stuff in memory, it's possible the file
will get huge and I can't take that memory hit.

I'll look again and see if I missed something

Thanks
Tory

It's not hard to do some streaming with copy (untested):

  $conn = pg_pconnect("dbname=foo");
  $fd = fopen('file.dat', 'r');
  while (!feof($fd)) {
        pg_put_line($conn, fgets($fd));
  }
  fclose($fd);
  pg_put_line($conn, "\\.\n");
  pg_end_copy($conn);

http://ru.php.net/manual/en/function.pg-put-line.php

Forgot about COPY command in my previous reply:

  $conn = pg_pconnect("dbname=foo");
  $fd = fopen('file.dat', 'r');
  pg_query($conn, "copy bar from stdin");
  while (!feof($fd)) {
        pg_put_line($conn, fgets($fd));
  }
  fclose($fd);
  pg_put_line($conn, "\\.\n");
  pg_end_copy($conn);

Vyacheslav Kalinin <vka@mgcp.com> writes:

>  $conn = pg_pconnect("dbname=foo");

Please reconsider and use plain pg_connect().
--
dim

> Vyacheslav Kalinin <vka@mgcp.com> writes:
>
>>  $conn = pg_pconnect("dbname=foo");
>
> Please reconsider and use plain pg_connect().

Would you like to elaborate on that? Why connect and not pconnect?

Uh, I just copied/pasted that part from somewhere in PHP manual, personally I tend to use plain pg_connect and pgbouncer for pooling when needed.