Postgres Pg_connect PHP

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

On Tue, Jun 9, 2009 at 10:35 PM, Tory M Blue <tmblue@gmail.com> wrote:

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?

On Thu, Jun 11, 2009 at 4:36 PM, Dimitri Fontaine <dfontaine@hi-media.com>wrote:

Vyacheslav Kalinin <vka@mgcp.com> writes:

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

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

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.For
those curious why pconnect is not the best option for connection pooling -
discussion on this topic rises in this list from time to time, check this
out, for instance:
http://archives.postgresql.org/pgsql-general/2007-08/msg00660.php