Обсуждение: Unable to connect to PostgreSQL server via PHP
Hi I'm trying to set up a new webserver running php and pgsql. PHP was connecting to postgres but I needed to install the php-gd module and now I get the error... "PHP Warning: pg_connect() [<a href='function.pg-connect'>function.pg-connect</a>]: Unable to connect to PostgreSQL server: could not connect to server: Permission denied\n\tIs the server running on host "localhost" and accepting\n\tTCP/IP connections on port 5432?" .... and I'm at a loss can anyone tell me why it's not connecting? Thanks This bit's I know are... http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql [#@xyala]# telnet localhost 5432 Trying 127.0.0.1... Connected to localhost.localdomain (127.0.0.1). Escape character is '^]'. Connection closed by foreign host. [#@xyala]# [#@xyala]# less /var/lib/pgsql/data/pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only #local all all ident sameuser local all all trust # IPv4 local connections: #host all all 127.0.0.1/32 ident sameuser host all all 127.0.0.1/32 trust # IPv6 local connections: #host all all ::1/128 ident sameuser host all all ::1/128 trust [#@xyala]# grep 'listen' /var/lib/pgsql/data/postgresql.conf # "pg_ctl reload". Some settings, such as listen_address, require #listen_addresses = 'localhost' # what IP interface(s) to listen on; listen_addresses = '*' [#@xyala]# [#@xyala]# less /etc/php.d/pgsql.ini ; Enable pgsql extension module extension=pgsql.so the server I'm going to replace is running the same versions of PHP and postgres http://zeldia.cap.ed.ac.uk/php_info.php The /etc/php.ini files on the two machines are the same and the /var/lib/pgsql/data/postgresql.conf files are only different because I've set listen_addresses = '*' on the new server (xyala) to see if I can make it work. _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
El jue, 09-08-2007 a las 14:51 +0000, John Coulthard escribió: > Hi > > I'm trying to set up a new webserver running php and pgsql. PHP was > connecting to postgres but I needed to install the php-gd module and now I > get the error... > > "PHP Warning: pg_connect() [<a > href='function.pg-connect'>function.pg-connect</a>]: Unable to connect to > PostgreSQL server: could not connect to server: Permission denied\n\tIs the > server running on host "localhost" and accepting\n\tTCP/IP > connections on port 5432?" > > .... and I'm at a loss can anyone tell me why it's not connecting? > > Thanks > > This bit's I know are... > http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql > > [#@xyala]# telnet localhost 5432 > Trying 127.0.0.1... > Connected to localhost.localdomain (127.0.0.1). > Escape character is '^]'. > Connection closed by foreign host. > [#@xyala]# > > > [#@xyala]# less /var/lib/pgsql/data/pg_hba.conf > # TYPE DATABASE USER CIDR-ADDRESS METHOD > > # "local" is for Unix domain socket connections only > #local all all ident sameuser > local all all trust > # IPv4 local connections: > #host all all 127.0.0.1/32 ident sameuser > host all all 127.0.0.1/32 trust > # IPv6 local connections: > #host all all ::1/128 ident sameuser > host all all ::1/128 trust > > [#@xyala]# grep 'listen' /var/lib/pgsql/data/postgresql.conf > # "pg_ctl reload". Some settings, such as listen_address, require > #listen_addresses = 'localhost' # what IP interface(s) to listen on; > listen_addresses = '*' > [#@xyala]# > > [#@xyala]# less /etc/php.d/pgsql.ini > ; Enable pgsql extension module > extension=pgsql.so > > the server I'm going to replace is running the same versions of PHP and > postgres http://zeldia.cap.ed.ac.uk/php_info.php > The /etc/php.ini files on the two machines are the same and the > /var/lib/pgsql/data/postgresql.conf files are only different because I've > set listen_addresses = '*' on the new server (xyala) to see if I can make it > work. > > _________________________________________________________________ > Express yourself instantly with MSN Messenger! Download today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings show your php source code for help you. -- Regards, Julio Cesar Sánchez González www.sistemasyconectividad.com.mx blog: http://darkavngr.blogspot.com --- Ahora me he convertido en la muerte, destructora de mundos. Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.
The part of the php code for the connection is
$dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
user=postgres password=$PG_PASS" );
if ( ! $dbconn ) {
echo "Error connecting to the database !<br> " ;
printf("%s", pg_errormessage( $dbconn ) );
exit(); }
This code works on zeldia
http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
So it's something to do with the way xyala is set up. I'm sure I'm missing
something obvious but what..?
It's not a firewall issue because it persists when the iptabes are off.
What does "could not connect to server: Permission denied" mean? Have I
done some something as root or me that I should have done as user postgres?
Thanks
>From: Julio Cesar S�nchez Gonz�lez <knowhow@sistemasyconectividad.com.mx>
>To: John Coulthard <bahhab@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
>Date: Fri, 10 Aug 2007 22:50:47 -0500
>
>El jue, 09-08-2007 a las 14:51 +0000, John Coulthard escribió:
> > Hi
> >
> > I'm trying to set up a new webserver running php and pgsql. PHP was
> > connecting to postgres but I needed to install the php-gd module and now
>I
> > get the error...
> >
> > "PHP Warning: pg_connect() [<a
> > href='function.pg-connect'>function.pg-connect</a>]: Unable to connect
>to
> > PostgreSQL server: could not connect to server: Permission denied\n\tIs
>the
> > server running on host "localhost" and accepting\n\tTCP/IP
> > connections on port 5432?"
> >
> > .... and I'm at a loss can anyone tell me why it's not connecting?
> >
> > Thanks
> >
> > This bit's I know are...
> > http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql
> >
> > [#@xyala]# telnet localhost 5432
> > Trying 127.0.0.1...
> > Connected to localhost.localdomain (127.0.0.1).
> > Escape character is '^]'.
> > Connection closed by foreign host.
> > [#@xyala]#
> >
> >
> > [#@xyala]# less /var/lib/pgsql/data/pg_hba.conf
> > # TYPE DATABASE USER CIDR-ADDRESS METHOD
> >
> > # "local" is for Unix domain socket connections only
> > #local all all ident sameuser
> > local all all trust
> > # IPv4 local connections:
> > #host all all 127.0.0.1/32 ident sameuser
> > host all all 127.0.0.1/32 trust
> > # IPv6 local connections:
> > #host all all ::1/128 ident sameuser
> > host all all ::1/128 trust
> >
> > [#@xyala]# grep 'listen' /var/lib/pgsql/data/postgresql.conf
> > # "pg_ctl reload". Some settings, such as listen_address, require
> > #listen_addresses = 'localhost' # what IP interface(s) to listen on;
> > listen_addresses = '*'
> > [#@xyala]#
> >
> > [#@xyala]# less /etc/php.d/pgsql.ini
> > ; Enable pgsql extension module
> > extension=pgsql.so
> >
> > the server I'm going to replace is running the same versions of PHP and
> > postgres http://zeldia.cap.ed.ac.uk/php_info.php
> > The /etc/php.ini files on the two machines are the same and the
> > /var/lib/pgsql/data/postgresql.conf files are only different because
>I've
> > set listen_addresses = '*' on the new server (xyala) to see if I can
>make it
> > work.
> >
> > _________________________________________________________________
> > Express yourself instantly with MSN Messenger! Download today it's FREE!
> > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
>
>
>show your php source code for help you.
>
>
>--
>Regards,
>
>Julio Cesar Sánchez González
>www.sistemasyconectividad.com.mx
>blog: http://darkavngr.blogspot.com
>
>---
>Ahora me he convertido en la muerte, destructora de mundos.
>Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.
>
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
On 8/13/07, John Coulthard <bahhab@hotmail.com> wrote:
> The part of the php code for the connection is
>
>
> $dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
> user=postgres password=$PG_PASS" );
> if ( ! $dbconn ) {
> echo "Error connecting to the database !<br> " ;
> printf("%s", pg_errormessage( $dbconn ) );
> exit(); }
>
> This code works on zeldia
> http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
> but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
>
> So it's something to do with the way xyala is set up. I'm sure I'm missing
> something obvious but what..?
>
> It's not a firewall issue because it persists when the iptabes are off.
>
> What does "could not connect to server: Permission denied" mean? Have I
> done some something as root or me that I should have done as
> user postgres?
Hi John,
Had the same issue. Try the following steps:
1. PGHBA.CONF
This is an important file. Mine (on Linux CentOS 4) is located at
"/var/lib/pgsql/data/pghba.conf". Make sure it looks like the
following.
local all all md5
host all all 127.0.0.1 255.255.255.255 md5
2. POSTGRESQL.CONF
listen_addresses = 'localhost,*'
#port = 5432
....other settings....
3. PHP CODE
link = pg_connect("host=localhost dbname=MYDB user=MYUSER password=MYPASS");
--
That is all you need. Don't specify anything else in the connection
string. Let me know how it goes.
LB
The part of the php code for the connection is
$dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
user=postgres password=$PG_PASS" );
if ( ! $dbconn ) {
echo "Error connecting to the database !<br> " ;
printf("%s", pg_errormessage( $dbconn ) );
exit(); }
This code works on zeldia
http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
So it's something to do with the way xyala is set up. I'm sure I'm missing
something obvious but what..?
It's not a firewall issue because it persists when the iptabes are off.
What does "could not connect to server: Permission denied" mean? Have I
done some something as root or me that I should have done as user postgres?
Thanks
>From: Julio Cesar S�nchez Gonz�lez <knowhow@sistemasyconectividad.com.mx>
>To: John Coulthard <bahhab@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
>Date: Fri, 10 Aug 2007 22:50:47 -0500
>
>El jue, 09-08-2007 a las 14:51 +0000, John Coulthard escribió:
> > Hi
> >
> > I'm trying to set up a new webserver running php and pgsql. PHP was
> > connecting to postgres but I needed to install the php-gd module and now
>I
> > get the error...
> >
> > "PHP Warning: pg_connect() [<a
> > href='function.pg-connect'>function.pg-connect</a>]: Unable to connect
>to
> > PostgreSQL server: could not connect to server: Permission denied\n\tIs
>the
> > server running on host "localhost" and accepting\n\tTCP/IP
> > connections on port 5432?"
> >
> > .... and I'm at a loss can anyone tell me why it's not connecting?
> >
> > Thanks
> >
> > This bit's I know are...
> > http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql
> >
> > [#@xyala]# telnet localhost 5432
> > Trying 127.0.0.1...
> > Connected to localhost.localdomain (127.0.0.1).
> > Escape character is '^]'.
> > Connection closed by foreign host.
> > [#@xyala]#
> >
> >
> > [#@xyala]# less /var/lib/pgsql/data/pg_hba.conf
> > # TYPE DATABASE USER CIDR-ADDRESS METHOD
> >
> > # "local" is for Unix domain socket connections only
> > #local all all ident sameuser
> > local all all trust
> > # IPv4 local connections:
> > #host all all 127.0.0.1/32 ident sameuser
> > host all all 127.0.0.1/32 trust
> > # IPv6 local connections:
> > #host all all ::1/128 ident sameuser
> > host all all ::1/128 trust
> >
> > [#@xyala]# grep 'listen' /var/lib/pgsql/data/postgresql.conf
> > # "pg_ctl reload". Some settings, such as listen_address, require
> > #listen_addresses = 'localhost' # what IP interface(s) to listen on;
> > listen_addresses = '*'
> > [#@xyala]#
> >
> > [#@xyala]# less /etc/php.d/pgsql.ini
> > ; Enable pgsql extension module
> > extension=pgsql.so
> >
> > the server I'm going to replace is running the same versions of PHP and
> > postgres http://zeldia.cap.ed.ac.uk/php_info.php
> > The /etc/php.ini files on the two machines are the same and the
> > /var/lib/pgsql/data/postgresql.conf files are only different because
>I've
> > set listen_addresses = '*' on the new server (xyala) to see if I can
>make it
> > work.
> >
> > _________________________________________________________________
> > Express yourself instantly with MSN Messenger! Download today it's FREE!
> > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
>
>
>show your php source code for help you.
>
>
>--
>Regards,
>
>Julio Cesar Sánchez González
>www.sistemasyconectividad.com.mx
>blog: http://darkavngr.blogspot.com
>
>---
>Ahora me he convertido en la muerte, destructora de mundos.
>Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.
>
_________________________________________________________________
Messenger Caf� � open for fun 24/7. Hot games, cool activities served daily.
Visit now. http://cafemessenger.com?ocid=TXT_TAGHM_AugHMtagline
>From: "Lim Berger" <straightfwd007@gmail.com>
>To: "John Coulthard" <bahhab@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
>Date: Mon, 13 Aug 2007 18:51:37 +0800
>
>On 8/13/07, John Coulthard <bahhab@hotmail.com> wrote:
> > The part of the php code for the connection is
> >
> >
> > $dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
> > user=postgres password=$PG_PASS" );
> > if ( ! $dbconn ) {
> > echo "Error connecting to the database !<br> " ;
> > printf("%s", pg_errormessage( $dbconn ) );
> > exit(); }
> >
> > This code works on zeldia
> > http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
> > but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
> >
> > So it's something to do with the way xyala is set up. I'm sure I'm
>missing
> > something obvious but what..?
> >
> > It's not a firewall issue because it persists when the iptabes are off.
> >
> > What does "could not connect to server: Permission denied" mean? Have I
> > done some something as root or me that I should have done as
> > user postgres?
>
>
>
>Hi John,
>
>Had the same issue. Try the following steps:
>
>1. PGHBA.CONF
>
>This is an important file. Mine (on Linux CentOS 4) is located at
>"/var/lib/pgsql/data/pghba.conf". Make sure it looks like the
>following.
>
>local all all md5
>host all all 127.0.0.1 255.255.255.255 md5
>
>
>2. POSTGRESQL.CONF
>
>
>listen_addresses = 'localhost,*'
>#port = 5432
>....other settings....
>
>
>3. PHP CODE
>
>
>link = pg_connect("host=localhost dbname=MYDB user=MYUSER
>password=MYPASS");
>
>--
>That is all you need. Don't specify anything else in the connection
>string. Let me know how it goes.
>
>LB
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
Thanks for the sugestions but they don''t solve the problem. I do notice
that if I set listen_addresses='localhost,*' then I get the following when I
start postgres...
bash-3.00$ ps -ef | grep postgres
root 9669 8757 0 13:34 pts/5 00:00:00 su postgres
postgres 9670 9669 0 13:34 pts/5 00:00:00 bash
postgres 9673 9670 0 13:34 pts/5 00:00:00 ps -ef
postgres 9674 9670 0 13:34 pts/5 00:00:00 grep postgres
bash-3.00$ /usr/bin/pg_ctl -D /var/lib/pgsql/data start
postmaster starting
bash-3.00$ LOG: could not bind IPv4 socket: Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
bash-3.00$
it does start though. But if I just have localhost or * as the
listen_addresses it starts without error.
That's not my problem though this is "could not connect to server:
Permission denied" If it's denying permission I must have the permissions
set wrong but where to I start looking for them? All sub dirs in
/var/lib/pgsql are owned by postgres and seem to have the same permissions
on both systems.
Cheers
_________________________________________________________________
Find a local pizza place, movie theater, and more�.then map the best route!
http://maps.live.com/default.aspx?v=2&ss=yp.bars~yp.pizza~yp.movie%20theater&cp=42.358996~-71.056691&style=r&lvl=13&tilt=-90&dir=0&alt=-1000&scene=950607&encType=1&FORM=MGAC01
"John Coulthard" <bahhab@hotmail.com> writes:
> That's not my problem though this is "could not connect to server:
> Permission denied" If it's denying permission I must have the permissions
> set wrong but where to I start looking for them?
"Permission denied" is a pretty strange error for a TCP connect failure,
as that is not a filesystem operation.
Are you able to connect with psql, or some other non-php client?
Use "psql -h localhost" to make sure it tries a TCP connection not
a Unix-socket connection.
regards, tom lane
>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: "John Coulthard" <bahhab@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP Date:
>Mon, 13 Aug 2007 10:09:15 -0400
>
>"John Coulthard" <bahhab@hotmail.com> writes:
> > That's not my problem though this is "could not connect to server:
> > Permission denied" If it's denying permission I must have the
>permissions
> > set wrong but where to I start looking for them?
>
>"Permission denied" is a pretty strange error for a TCP connect failure,
>as that is not a filesystem operation.
>
>Are you able to connect with psql, or some other non-php client?
>Use "psql -h localhost" to make sure it tries a TCP connection not
>a Unix-socket connection.
>
Thanks. You mean like this? This connects without an error.
[root@xyala john]# su webuser
[webuser@xyala john]$ psql -h localhost lumbribase
Welcome to psql 8.0.8, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
lumbribase=>
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
It was SELinux denying apache permission to make TCP connections!
I thought I had SELinux turned off but it wasn't. To be sure it is do
/usr/sbin/sestatus | grep SELinux
and if it comes back with anything other than SELinux status: disabled it's
still running.
While I was talking to the php board I discovered you can configure php to
run from the command line (mine was by default). So I wrote this bit of php
(obviously change the database name and you may need a user and passwd)...
<?php
$dbconn=pg_connect("dbname=lumbribase");
if ( ! $dbconn ) {
echo "Error connecting to the database !<br> " ;
printf("%s", pg_errormessage( $dbconn ) );
exit(); }
else {echo "connected", "\n";}
?>
....saved it as test.php and at a shell prompt ran...
[john@ tmp]$ php test.php
connected
If you get 'connected' you know php and postgres are talking via tcp and
some secondary process (firewall/SELinux) is preventing the apache
connection.
Thanks for the help.
>From: "John Coulthard" <bahhab@hotmail.com>
>To: tgl@sss.pgh.pa.us
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
>Date: Mon, 13 Aug 2007 15:34:19 +0000
>
>
>
>
>>From: Tom Lane <tgl@sss.pgh.pa.us>
>>To: "John Coulthard" <bahhab@hotmail.com>
>>CC: pgsql-general@postgresql.org
>>Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
>>Date: Mon, 13 Aug 2007 10:09:15 -0400
>>
>>"John Coulthard" <bahhab@hotmail.com> writes:
>> > That's not my problem though this is "could not connect to server:
>> > Permission denied" If it's denying permission I must have the
>>permissions
>> > set wrong but where to I start looking for them?
>>
>>"Permission denied" is a pretty strange error for a TCP connect failure,
>>as that is not a filesystem operation.
>>
>>Are you able to connect with psql, or some other non-php client?
>>Use "psql -h localhost" to make sure it tries a TCP connection not
>>a Unix-socket connection.
>>
>
>Thanks. You mean like this? This connects without an error.
>
>[root@xyala john]# su webuser
>[webuser@xyala john]$ psql -h localhost lumbribase
>Welcome to psql 8.0.8, the PostgreSQL interactive terminal.
>
>Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help with psql commands
> \g or terminate with semicolon to execute query
> \q to quit
>
>lumbribase=>
>
>_________________________________________________________________
>FREE pop-up blocking with the new MSN Toolbar - get it now!
>http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings
_________________________________________________________________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/