Unable to connect to PostgreSQL server via PHP

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 &quot;localhost&quot; 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 &quot;localhost&quot; 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 &quot;localhost&quot; 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&amp;ss=yp.bars~yp.pizza~yp.movie%20theater&amp;cp=42.358996~-71.056691&amp;style=r&amp;lvl=13&amp;tilt=-90&amp;dir=0&amp;alt=-1000&amp;scene=950607&amp;encType=1&amp;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/