Thread: Unable to connect to PostgreSQL server via PHP

Unable to connect to PostgreSQL server via PHP

From
"John Coulthard"
Date:
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/


Re: Unable to connect to PostgreSQL server via PHP

From
Julio Cesar Sánchez González
Date:
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.


Re: Unable to connect to PostgreSQL server via PHP

From
"John Coulthard"
Date:
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/


Re: Unable to connect to PostgreSQL server via PHP

From
"Lim Berger"
Date:
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

Re: Unable to connect to PostgreSQL server via PHP

From
"John Coulthard"
Date:
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


Re: Unable to connect to PostgreSQL server via PHP

From
"John Coulthard"
Date:


>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


Re: Unable to connect to PostgreSQL server via PHP

From
Tom Lane
Date:
"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

Re: Unable to connect to PostgreSQL server via PHP

From
"John Coulthard"
Date:


>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/


Re: Unable to connect to PostgreSQL server via PHP

From
"John Coulthard"
Date:
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/