Discussion:
db dump from php
(too old to reply)
jody
2013-01-06 13:50:50 UTC
Permalink
Hi
I have a problem when creating a db dump from a php script in a html page.
This used to work in previous PostgreSQL versions, but now (v, 9.1.5)
it fails, and i have no idea why.

It is not a matter of write-access, since the directory has mode 777,
and in the same php-file i successfully create a file there.

Here's a minimal version which shows the behavior:

<html>
<head></head>
<body>
<?php
putenv('PG_USER=magnus');
putenv('PG_PASSWORD=mypass');
putenv('PG_DB=magnus2');
putenv('PG_HOST=localhost');
putenv('PGPASSFILE=/home/jody/.pgpass');

$sPath="/home/jody/progs/magnus2/dumps/dumpidump.sql";
$sDBName="magnus2";
$sQuery1="/usr/bin/pg_dump -w -U magnus -c -f $sPath $sDBName";

exec($sQuery1, $output, $iRet);

print("Q:" . $sQuery1 . "<br/>");
print("ret:" . $iRet . "<br/>");
foreach($output as $ss) {
print("out:" . $ss . "<br/>");
}

print("<hr/>\n");

$sQuery2="echo gnagna > " . $sPath . ".test";
exec($sQuery2, $output, $iRet);
print("Q:" . $sQuery2 . "<br/>");
print("ret:" . $iRet . "<br/>");
foreach($output as $ss) {
print("out:" . $ss . "<br/>");
}
?>
The call to pg_dump fails with a return code of 1, and $output is empty.
The second call succeeds with return code 0, and a file is indeed written.

When i replace $sQuery1 by "/usr/bin/pg_dump --version" the cal
succeeds with return code 0,
and $output contains the string "pg_dump (PostgreSQL) 9.1.5"

When i copy-paste the command contained in $sQuery to the command line,
it succeeds.

In my home directory /home/jody i have .pgpass file, with the correct
username and password.

Is there a way to specify a password on the command line?
Can somebody give me a hint how i can get this to work?

Thank You
Jody
--
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Tom Lane
2013-01-06 21:34:03 UTC
Permalink
Post by jody
I have a problem when creating a db dump from a php script in a html page.
This used to work in previous PostgreSQL versions, but now (v, 9.1.5)
it fails, and i have no idea why.
It might help to look in the postgres server's log to see if there are
any related messages. Based on the described symptoms I'm guessing a
user authentication failure, possibly related to the fact that the
dump is running as whatever-user-owns-the-webserver-process rather than
yourself. If it worked before, that probably means you had the older
server configured differently for authentication --- see pg_hba.conf
and related files.

Note in particular that "peer" and "ident" auth modes don't care about
password files, and instead directly check which OS user the client
program is running as ...

regards, tom lane
--
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Christian Hammers
2013-01-07 09:37:03 UTC
Permalink
Hello

Instead of exec(), try

$output = `$sQuery1 2>&1`;

You don't get the return code then but the stdout/stderr output which might
help you more.

bye,

-christian-


On Sun, 6 Jan 2013 14:50:50 +0100
Post by jody
Hi
I have a problem when creating a db dump from a php script in a html page.
This used to work in previous PostgreSQL versions, but now (v, 9.1.5)
it fails, and i have no idea why.
It is not a matter of write-access, since the directory has mode 777,
and in the same php-file i successfully create a file there.
<html>
<head></head>
<body>
<?php
putenv('PG_USER=magnus');
putenv('PG_PASSWORD=mypass');
putenv('PG_DB=magnus2');
putenv('PG_HOST=localhost');
putenv('PGPASSFILE=/home/jody/.pgpass');
$sPath="/home/jody/progs/magnus2/dumps/dumpidump.sql";
$sDBName="magnus2";
$sQuery1="/usr/bin/pg_dump -w -U magnus -c -f $sPath $sDBName";
exec($sQuery1, $output, $iRet);
print("Q:" . $sQuery1 . "<br/>");
print("ret:" . $iRet . "<br/>");
foreach($output as $ss) {
print("out:" . $ss . "<br/>");
}
print("<hr/>\n");
$sQuery2="echo gnagna > " . $sPath . ".test";
exec($sQuery2, $output, $iRet);
print("Q:" . $sQuery2 . "<br/>");
print("ret:" . $iRet . "<br/>");
foreach($output as $ss) {
print("out:" . $ss . "<br/>");
}
?>
The call to pg_dump fails with a return code of 1, and $output is empty.
The second call succeeds with return code 0, and a file is indeed written.
When i replace $sQuery1 by "/usr/bin/pg_dump --version" the cal
succeeds with return code 0,
and $output contains the string "pg_dump (PostgreSQL) 9.1.5"
When i copy-paste the command contained in $sQuery to the command line,
it succeeds.
In my home directory /home/jody i have .pgpass file, with the correct
username and password.
Is there a way to specify a password on the command line?
Can somebody give me a hint how i can get this to work?
Thank You
Jody
--
Network Engineering & Design; Content Delivery Platform & IP

NETCOLOGNE Gesellschaft für Telekommunikation mbH
Am Coloneum 9 | 50829 Köln
Tel: 0221 2222-8711 | Fax: 0221 2222-78711
www.netcologne.de

Geschäftsführer:
Dr. Hans Konle (Sprecher)
Dipl.-Ing. Karl-Heinz Zankel
HRB 25580, AG Köln



Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese
Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch
Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu
löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer
Weise verwendet werden.
--
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
jody
2013-01-07 19:02:24 UTC
Permalink
Hi
I have now changed all entries in pg_hba.conf to 'trust'

I have modified the call as suggested by Christian.

When i call the php script from the command line, the dump i made.

When i call it from apache, i get the output

pg_dump: [archiver (db)] connection to database "magnus2" failed:
could not connect to server: Permission denied Is the server running
locally and accepting connections on Unix domain socket
"/run/postgresql/.s.PGSQL.5432"?

But the server is running, because my phpPgAdmin can display the db
data, and because the dump worked when executed from the commandline.

From the file generated by the "echo" command, i know that the user
executing the php script is apache.
This seems not to be a problem with the password, because then it
would already have contacted the server.

So for some reason the user apache can't connect to the server.
(and "apache" seems to be a "virtual" user: even as root you cannot do
'su appache': This account is currently not available.)
Any ideas?

Thank You
jody
Post by Christian Hammers
Hello
Instead of exec(), try
$output = `$sQuery1 2>&1`;
You don't get the return code then but the stdout/stderr output which might
help you more.
bye,
-christian-
On Sun, 6 Jan 2013 14:50:50 +0100
Post by jody
Hi
I have a problem when creating a db dump from a php script in a html page.
This used to work in previous PostgreSQL versions, but now (v, 9.1.5)
it fails, and i have no idea why.
It is not a matter of write-access, since the directory has mode 777,
and in the same php-file i successfully create a file there.
<html>
<head></head>
<body>
<?php
putenv('PG_USER=magnus');
putenv('PG_PASSWORD=mypass');
putenv('PG_DB=magnus2');
putenv('PG_HOST=localhost');
putenv('PGPASSFILE=/home/jody/.pgpass');
$sPath="/home/jody/progs/magnus2/dumps/dumpidump.sql";
$sDBName="magnus2";
$sQuery1="/usr/bin/pg_dump -w -U magnus -c -f $sPath $sDBName";
exec($sQuery1, $output, $iRet);
print("Q:" . $sQuery1 . "<br/>");
print("ret:" . $iRet . "<br/>");
foreach($output as $ss) {
print("out:" . $ss . "<br/>");
}
print("<hr/>\n");
$sQuery2="echo gnagna > " . $sPath . ".test";
exec($sQuery2, $output, $iRet);
print("Q:" . $sQuery2 . "<br/>");
print("ret:" . $iRet . "<br/>");
foreach($output as $ss) {
print("out:" . $ss . "<br/>");
}
?>
The call to pg_dump fails with a return code of 1, and $output is empty.
The second call succeeds with return code 0, and a file is indeed written.
When i replace $sQuery1 by "/usr/bin/pg_dump --version" the cal
succeeds with return code 0,
and $output contains the string "pg_dump (PostgreSQL) 9.1.5"
When i copy-paste the command contained in $sQuery to the command line,
it succeeds.
In my home directory /home/jody i have .pgpass file, with the correct
username and password.
Is there a way to specify a password on the command line?
Can somebody give me a hint how i can get this to work?
Thank You
Jody
--
Network Engineering & Design; Content Delivery Platform & IP
NETCOLOGNE Gesellschaft für Telekommunikation mbH
Am Coloneum 9 | 50829 Köln
Tel: 0221 2222-8711 | Fax: 0221 2222-78711
www.netcologne.de
Dr. Hans Konle (Sprecher)
Dipl.-Ing. Karl-Heinz Zankel
HRB 25580, AG Köln
Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese
Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch
Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu
löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer
Weise verwendet werden.
--
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Andreas Kretschmer
2013-01-07 19:09:24 UTC
Permalink
Post by jody
Hi
I have now changed all entries in pg_hba.conf to 'trust'
I have modified the call as suggested by Christian.
When i call the php script from the command line, the dump i made.
When i call it from apache, i get the output
could not connect to server: Permission denied Is the server running
locally and accepting connections on Unix domain socket
"/run/postgresql/.s.PGSQL.5432"?
check if the file (socket) exists. Try to connect via tcp/ip using
parameter -h <hostname or ip>


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Tom Lane
2013-01-07 19:19:21 UTC
Permalink
Post by jody
When i call the php script from the command line, the dump i made.
When i call it from apache, i get the output
could not connect to server: Permission denied Is the server running
locally and accepting connections on Unix domain socket
"/run/postgresql/.s.PGSQL.5432"?
Does the apache user have permissions to access that socket file? What
about the directories containing it?

regards, tom lane
--
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
jody
2013-01-07 21:48:05 UTC
Permalink
Hi
srwxrwxrwx 1 postgres postgres 0 Jan 7 20:35 /run/postgresql/.s.PGSQL.5432
drwxrwx--- 2 postgres postgres 80 Jan 7 20:35 /run/postgresql
pg_dump: [archiver (db)] connection to database "magnus2" failed: fe_sendauth: no password supplied
I checked again - i have for some reason used a wrong environment
variable for the password:
it should be PGPASSWORD and not PG_PASSWORD

I changed this and now the dump is correctly done and saved.

Thanks again
Jody
Post by jody
When i call the php script from the command line, the dump i made.
When i call it from apache, i get the output
could not connect to server: Permission denied Is the server running
locally and accepting connections on Unix domain socket
"/run/postgresql/.s.PGSQL.5432"?
Does the apache user have permissions to access that socket file? What
about the directories containing it?
regards, tom lane
--
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Loading...