Thread: db dump from php
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
jody <jody.xha@gmail.com> writes:
> 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
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
jody <jody.xha@gmail.com> wrote:
> 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
>
>
--
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.
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
On Mon, Jan 7, 2013 at 10:37 AM, Christian Hammers <ch@lathspell.de> wrote:
> 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
> jody <jody.xha@gmail.com> wrote:
>
>> 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
>>
>>
>
>
> --
> 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.
jody <jody.xha@gmail.com> wrote: > 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"? 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°
jody <jody.xha@gmail.com> writes:
> 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"?
Does the apache user have permissions to access that socket file? What
about the directories containing it?
regards, tom lane
Hi thanks to your suggestions i was able to solve the problem: the socket existed, and it has global permissions: > jody@raven ~/progs/magnus2 $ ls -l /run/postgresql/.s.PGSQL.5432 > srwxrwxrwx 1 postgres postgres 0 Jan 7 20:35 /run/postgresql/.s.PGSQL.5432 but the directory upstairs only allows postgres: > jody@raven ~/progs/magnus2 $ ls -ld /run/postgresql > drwxrwx--- 2 postgres postgres 80 Jan 7 20:35 /run/postgresql i now added the user 'apache' to the group 'postgres' and got one step further: the connection can be made, but now there is a password problem: > 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 On Mon, Jan 7, 2013 at 8:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > jody <jody.xha@gmail.com> writes: >> 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"? > > Does the apache user have permissions to access that socket file? What > about the directories containing it? > > regards, tom lane