Problem about connecting PostgreSQL through TCP/IP

Hello, everyone,

Can anyone help me? I am quite frustrating now. I have been trying to
connect PostgreSQL server through TCP/IP connection using PHP. I was
not successful. Could please read the following and give me some
hints. Thanks a million.

OS: Fedora Core 4 ("uname -s -r" yields "Linux 2.6.12-1.1398_FC4smp")
PostgreSQL: version 8.0.3
PHP: version 5.0.4

In postgresql.conf, these three lines were inserted,=20
listen_addresses =3D '*'
port =3D 5432
max_connections =3D 100

The following lines were inserted to pg_hba.conf:
local all all password
host all all 127.0.0.1/32 password
host all all 192.168.0.0/1 password

restart posgresql by=20
/sbin/service postgresql restart
The server was restarted OK.

The PHP script used to connect database is as follows,
$conn =3D pg_connect("host=3Dlocalhost port=3D5432 dbname=3Dfoodb user=3D=
foo
password=3Dfoo") or die ("Can not connect to postgres");
$result=3Dpg_exec("SELECT * FROM footbl");
$fetch =3D pg_fetch_row($result);=20
print "";
print $fetch[0];
print "";

pg_close($conn); // Close this connection
?>=20

Assume this PHP script is named as "connectdb.php", then on
commandline, when I run the following,
php connectedb.php
I see correct results, e.g.,=20
foo=20

On commandline again, I used the following to connect database,
psql -d foodb -h localhost -p 5432 -U foo
Password: ************
Welcome to psql 8.0.3, 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

foodb=3D>=20

It is OK. Then I disabled "iptables" temporarily for testing purpose by
/sbin/service iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]

On localhost, I tried this command,
telnet localhost 5432
after hit "enters", telnet exited. I checked postgresql_%S.log and see
"LOG: invalid length of startup packet"
which means postgresql server indeed saw the telnet connection.=20

I tried this on any of my other machines in the LAN, I observed the same th=
ing.=20

In summary, it seemed the postgresql allowed TCP/IP connection, and
worked well. However, when I ran the PHP script through web browser, I
did not get connection. Instead, in Apache web server's error_log, I
see
[client 192.168.1.10] PHP Warning: pg_connect() [ href=3D'function.pg-connect'>function.pg-connect]: 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? in /foo/connectdb.php on
line 2

Interestingly, when I checked the log file for postgresql again, it
did not have anything for this connection failure. It seemed the
connection did not send to the database server at all. However,
1) I disabled firewall
2) I can telnet the ports
3) I can run the script from commandline

What could go wrong?

Thanks a lot for your reading this lengthy email. Waiting for your help!

Gray

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Problem about connecting PostgreSQL through TCP/IP

>
> restart posgresql by
> /sbin/service postgresql restart
> The server was restarted OK.
>

I usually use pg_ctl to start.

With it and other methods of starting postgres you have to include the
-i flag to get it to listen over TCP/IP

perhaps your method of restarting needs this flag as well?



> The PHP script used to connect database is as follows,
> > $conn = pg_connect("host=localhost port=5432 dbname=foodb user=foo
> password=foo") or die ("Can not connect to postgres");
> $result=pg_exec("SELECT * FROM footbl");
> $fetch = pg_fetch_row($result);
> print "";
> print $fetch[0];
> print "";
>
> pg_close($conn); // Close this connection
> ?>
>
> Assume this PHP script is named as "connectdb.php", then on
> commandline, when I run the following,
> php connectedb.php
> I see correct results, e.g.,
> foo
>
> On commandline again, I used the following to connect database,
> psql -d foodb -h localhost -p 5432 -U foo
> Password: ************
> Welcome to psql 8.0.3, 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
>
> foodb=>
>
> It is OK. Then I disabled "iptables" temporarily for testing purpose by
> /sbin/service iptables stop
> Flushing firewall rules: [ OK ]
> Setting chains to policy ACCEPT: filter [ OK ]
> Unloading iptables modules: [ OK ]
>
> On localhost, I tried this command,
> telnet localhost 5432
> after hit "enters", telnet exited. I checked postgresql_%S.log and see
> "LOG: invalid length of startup packet"
> which means postgresql server indeed saw the telnet connection.
>
> I tried this on any of my other machines in the LAN, I observed the
> same thing.
>
> In summary, it seemed the postgresql allowed TCP/IP connection, and
> worked well. However, when I ran the PHP script through web browser, I
> did not get connection. Instead, in Apache web server's error_log, I
> see
> [client 192.168.1.10] PHP Warning: pg_connect() [ > href='function.pg-connect'>function.pg-connect]: 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? in /foo/connectdb.php on
> line 2
>
> Interestingly, when I checked the log file for postgresql again, it
> did not have anything for this connection failure. It seemed the
> connection did not send to the database server at all. However,
> 1) I disabled firewall
> 2) I can telnet the ports
> 3) I can run the script from commandline
>
> What could go wrong?
>
> Thanks a lot for your reading this lengthy email. Waiting for your
> help!
>
> Gray
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Problem about connecting PostgreSQL through TCP/IP

did you enable the "i" flag when you started the
postmaster? if you didn't do that, tcp/ip will not
connect!

http://www.postgresql.org/docs/7.4/interactive/app-postmaste r.html

good luck.

--- Hui Chen wrote:

> Hello, everyone,
>
> Can anyone help me? I am quite frustrating now. I
> have been trying to
> connect PostgreSQL server through TCP/IP connection
> using PHP. I was
> not successful. Could please read the following and
> give me some
> hints. Thanks a million.
>
> OS: Fedora Core 4 ("uname -s -r" yields "Linux
> 2.6.12-1.1398_FC4smp")
> PostgreSQL: version 8.0.3
> PHP: version 5.0.4
>
> In postgresql.conf, these three lines were inserted,
>
> listen_addresses = '*'
> port = 5432
> max_connections = 100
>
> The following lines were inserted to pg_hba.conf:
> local all all
> password
> host all all 127.0.0.1/32
> password
> host all all 192.168.0.0/1
> password
>
> restart posgresql by
> /sbin/service postgresql restart
> The server was restarted OK.
>
> The PHP script used to connect database is as
> follows,
> > $conn = pg_connect("host=localhost port=5432
> dbname=foodb user=foo
> password=foo") or die ("Can not connect to
> postgres");
> $result=pg_exec("SELECT * FROM footbl");
> $fetch = pg_fetch_row($result);
> print "";
> print $fetch[0];
> print "";
>
> pg_close($conn); // Close this connection
> ?>
>
> Assume this PHP script is named as "connectdb.php",
> then on
> commandline, when I run the following,
> php connectedb.php
> I see correct results, e.g.,
> foo
>
> On commandline again, I used the following to
> connect database,
> psql -d foodb -h localhost -p 5432 -U foo
> Password: ************
> Welcome to psql 8.0.3, 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
>
> foodb=>
>
> It is OK. Then I disabled "iptables" temporarily for
> testing purpose by
> /sbin/service iptables stop
> Flushing firewall rules:
> [ OK ]
> Setting chains to policy ACCEPT: filter
> [ OK ]
> Unloading iptables modules:
> [ OK ]
>
> On localhost, I tried this command,
> telnet localhost 5432
> after hit "enters", telnet exited. I checked
> postgresql_%S.log and see
> "LOG: invalid length of startup packet"
> which means postgresql server indeed saw the telnet
> connection.
>
> I tried this on any of my other machines in the LAN,
> I observed the same thing.
>
> In summary, it seemed the postgresql allowed TCP/IP
> connection, and
> worked well. However, when I ran the PHP script
> through web browser, I
> did not get connection. Instead, in Apache web
> server's error_log, I
> see
> [client 192.168.1.10] PHP Warning: pg_connect() [ > href='function.pg-connect'>function.pg-connect]:
> 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? in
> /foo/connectdb.php on
> line 2
>
> Interestingly, when I checked the log file for
> postgresql again, it
> did not have anything for this connection failure.
> It seemed the
> connection did not send to the database server at
> all. However,
> 1) I disabled firewall
> 2) I can telnet the ports
> 3) I can run the script from commandline
>
> What could go wrong?
>
> Thanks a lot for your reading this lengthy email.
> Waiting for your help!
>
> Gray
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Problem about connecting PostgreSQL through TCP/IP

Thanks, Matthew! I did notice that "-i" flag was used to enable the
postgresql server to listen over TCP/IP. However, it seemed to me this
option had become obsolete in postgresal 8.0. I did not find "-i"
option from man page. Plus, the document here
http://www.postgresql.org/docs/8.0/interactive/runtime-confi g.html
states that I only need to insert "listen_addresses" option in the
postgresql.conf file. Did I misunderstand anything? What do you think?
thanks again!

Gray

On 8/15/05, Matthew Terenzio wrote:
> >
> >
> > restart posgresql by
> > /sbin/service postgresql restart
> > The server was restarted OK.
> I usually use pg_ctl to start.
>=20
> With it and other methods of starting postgres you have to include the
> -i flag to get it to listen over TCP/IP
>=20
> perhaps your method of restarting needs this flag as well?
>=20
> >
> > The PHP script used to connect database is as follows,
> > > > $conn =3D pg_connect("host=3Dlocalhost port=3D5432 dbname=3Dfoodb use=
r=3Dfoo
> > password=3Dfoo") or die ("Can not connect to postgres");
> > $result=3Dpg_exec("SELECT * FROM footbl");
> > $fetch =3D pg_fetch_row($result);
> > print "";
> > print $fetch[0];
> > print "";
> >
> > pg_close($conn); // Close this connection
> > ?>
> >
> > Assume this PHP script is named as "connectdb.php", then on
> > commandline, when I run the following,
> > php connectedb.php
> > I see correct results, e.g.,
> > foo
> >
> > On commandline again, I used the following to connect database,
> > psql -d foodb -h localhost -p 5432 -U foo
> > Password: ************
> > Welcome to psql 8.0.3, 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
> >
> > foodb=3D>
> >
> > It is OK. Then I disabled "iptables" temporarily for testing purpose by
> > /sbin/service iptables stop
> > Flushing firewall rules: [ OK ]
> > Setting chains to policy ACCEPT: filter [ OK ]
> > Unloading iptables modules: [ OK ]
> >
> > On localhost, I tried this command,
> > telnet localhost 5432
> > after hit "enters", telnet exited. I checked postgresql_%S.log and see
> > "LOG: invalid length of startup packet"
> > which means postgresql server indeed saw the telnet connection.
> >
> > I tried this on any of my other machines in the LAN, I observed the
> > same thing.
> >
> > In summary, it seemed the postgresql allowed TCP/IP connection, and
> > worked well. However, when I ran the PHP script through web browser, I
> > did not get connection. Instead, in Apache web server's error_log, I
> > see
> > [client 192.168.1.10] PHP Warning: pg_connect() [ > > href=3D'function.pg-connect'>function.pg-connect]: 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? in /foo/connectdb.php on
> > line 2
> >
> > Interestingly, when I checked the log file for postgresql again, it
> > did not have anything for this connection failure. It seemed the
> > connection did not send to the database server at all. However,
> > 1) I disabled firewall
> > 2) I can telnet the ports
> > 3) I can run the script from commandline
> >
> > What could go wrong?
> >
> > Thanks a lot for your reading this lengthy email. Waiting for your
> > help!
> >
> > Gray
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>=20
>=20
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Problem about connecting PostgreSQL through TCP/IP

Hello,

Thanks a lot!

I did noticed "-i" option. I am using 8.0 whose manual
(http://www.postgresql.org/docs/8.0/interactive/app-postmast er.html)
states that "-i" option is deprecated. The manual
(http://www.postgresql.org/docs/8.0/interactive/runtime-conf ig.html#RUNTIME=
-CONFIG-CONNECTION)
also says that I can enable "listen_addresses" option in the
postgresql.conf to enable the server to listen over TCP/IP. What
really puzzles me is that I could actually telnet server at 5432 ports
and saw an corresponding entry in the log file; however, when I used
PHP to connect the server, the log file did not have an entry for the
failed connection attempt at all. Do you think what can go wrong?
Thanks!

Gray


On 8/15/05, operationsengineer1@yahoo.com w=
rote:
> did you enable the "i" flag when you started the
> postmaster? if you didn't do that, tcp/ip will not
> connect!
>=20
> http://www.postgresql.org/docs/7.4/interactive/app-postmaste r.html
>=20
> good luck.
>=20
> --- Hui Chen wrote:
>=20
> > Hello, everyone,
> >
> > Can anyone help me? I am quite frustrating now. I
> > have been trying to
> > connect PostgreSQL server through TCP/IP connection
> > using PHP. I was
> > not successful. Could please read the following and
> > give me some
> > hints. Thanks a million.
> >
> > OS: Fedora Core 4 ("uname -s -r" yields "Linux
> > 2.6.12-1.1398_FC4smp")
> > PostgreSQL: version 8.0.3
> > PHP: version 5.0.4
> >
> > In postgresql.conf, these three lines were inserted,
> >
> > listen_addresses =3D '*'
> > port =3D 5432
> > max_connections =3D 100
> >
> > The following lines were inserted to pg_hba.conf:
> > local all all
> > password
> > host all all 127.0.0.1/32
> > password
> > host all all 192.168.0.0/1
> > password
> >
> > restart posgresql by
> > /sbin/service postgresql restart
> > The server was restarted OK.
> >
> > The PHP script used to connect database is as
> > follows,
> > > > $conn =3D pg_connect("host=3Dlocalhost port=3D5432
> > dbname=3Dfoodb user=3Dfoo
> > password=3Dfoo") or die ("Can not connect to
> > postgres");
> > $result=3Dpg_exec("SELECT * FROM footbl");
> > $fetch =3D pg_fetch_row($result);
> > print "";
> > print $fetch[0];
> > print "";
> >
> > pg_close($conn); // Close this connection
> > ?>
> >
> > Assume this PHP script is named as "connectdb.php",
> > then on
> > commandline, when I run the following,
> > php connectedb.php
> > I see correct results, e.g.,
> > foo
> >
> > On commandline again, I used the following to
> > connect database,
> > psql -d foodb -h localhost -p 5432 -U foo
> > Password: ************
> > Welcome to psql 8.0.3, 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
> >
> > foodb=3D>
> >
> > It is OK. Then I disabled "iptables" temporarily for
> > testing purpose by
> > /sbin/service iptables stop
> > Flushing firewall rules:
> > [ OK ]
> > Setting chains to policy ACCEPT: filter
> > [ OK ]
> > Unloading iptables modules:
> > [ OK ]
> >
> > On localhost, I tried this command,
> > telnet localhost 5432
> > after hit "enters", telnet exited. I checked
> > postgresql_%S.log and see
> > "LOG: invalid length of startup packet"
> > which means postgresql server indeed saw the telnet
> > connection.
> >
> > I tried this on any of my other machines in the LAN,
> > I observed the same thing.
> >
> > In summary, it seemed the postgresql allowed TCP/IP
> > connection, and
> > worked well. However, when I ran the PHP script
> > through web browser, I
> > did not get connection. Instead, in Apache web
> > server's error_log, I
> > see
> > [client 192.168.1.10] PHP Warning: pg_connect() [ > > href=3D'function.pg-connect'>function.pg-connect]:
> > 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? in
> > /foo/connectdb.php on
> > line 2
> >
> > Interestingly, when I checked the log file for
> > postgresql again, it
> > did not have anything for this connection failure.
> > It seemed the
> > connection did not send to the database server at
> > all. However,
> > 1) I disabled firewall
> > 2) I can telnet the ports
> > 3) I can run the script from commandline
> >
> > What could go wrong?
> >
> > Thanks a lot for your reading this lengthy email.
> > Waiting for your help!
> >
> > Gray
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>=20
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Problem about connecting PostgreSQL through TCP/IP

This may be of interest to the PHP Dev group. I'll forward it to
someone there.


On Aug 17, 2005, at 1:03 AM, Hui Chen wrote:

> Hello,
>
> Thanks a lot!
>
> I did noticed "-i" option. I am using 8.0 whose manual
> (http://www.postgresql.org/docs/8.0/interactive/app-postmast er.html)
> states that "-i" option is deprecated. The manual
> (http://www.postgresql.org/docs/8.0/interactive/runtime-
> config.html#RUNTIME-CONFIG-CONNECTION)
> also says that I can enable "listen_addresses" option in the
> postgresql.conf to enable the server to listen over TCP/IP. What
> really puzzles me is that I could actually telnet server at 5432 ports
> and saw an corresponding entry in the log file; however, when I used
> PHP to connect the server, the log file did not have an entry for the
> failed connection attempt at all. Do you think what can go wrong?
> Thanks!
>
> Gray
>
>
> On 8/15/05, operationsengineer1@yahoo.com
> wrote:
>> did you enable the "i" flag when you started the
>> postmaster? if you didn't do that, tcp/ip will not
>> connect!
>>
>> http://www.postgresql.org/docs/7.4/interactive/app-postmaste r.html
>>
>> good luck.
>>
>> --- Hui Chen wrote:
>>
>>> Hello, everyone,
>>>
>>> Can anyone help me? I am quite frustrating now. I
>>> have been trying to
>>> connect PostgreSQL server through TCP/IP connection
>>> using PHP. I was
>>> not successful. Could please read the following and
>>> give me some
>>> hints. Thanks a million.
>>>
>>> OS: Fedora Core 4 ("uname -s -r" yields "Linux
>>> 2.6.12-1.1398_FC4smp")
>>> PostgreSQL: version 8.0.3
>>> PHP: version 5.0.4
>>>
>>> In postgresql.conf, these three lines were inserted,
>>>
>>> listen_addresses = '*'
>>> port = 5432
>>> max_connections = 100
>>>
>>> The following lines were inserted to pg_hba.conf:
>>> local all all
>>> password
>>> host all all 127.0.0.1/32
>>> password
>>> host all all 192.168.0.0/1
>>> password
>>>
>>> restart posgresql by
>>> /sbin/service postgresql restart
>>> The server was restarted OK.
>>>
>>> The PHP script used to connect database is as
>>> follows,
>>> >>> $conn = pg_connect("host=localhost port=5432
>>> dbname=foodb user=foo
>>> password=foo") or die ("Can not connect to
>>> postgres");
>>> $result=pg_exec("SELECT * FROM footbl");
>>> $fetch = pg_fetch_row($result);
>>> print "";
>>> print $fetch[0];
>>> print "";
>>>
>>> pg_close($conn); // Close this connection
>>> ?>
>>>
>>> Assume this PHP script is named as "connectdb.php",
>>> then on
>>> commandline, when I run the following,
>>> php connectedb.php
>>> I see correct results, e.g.,
>>> foo
>>>
>>> On commandline again, I used the following to
>>> connect database,
>>> psql -d foodb -h localhost -p 5432 -U foo
>>> Password: ************
>>> Welcome to psql 8.0.3, 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
>>>
>>> foodb=>
>>>
>>> It is OK. Then I disabled "iptables" temporarily for
>>> testing purpose by
>>> /sbin/service iptables stop
>>> Flushing firewall rules:
>>> [ OK ]
>>> Setting chains to policy ACCEPT: filter
>>> [ OK ]
>>> Unloading iptables modules:
>>> [ OK ]
>>>
>>> On localhost, I tried this command,
>>> telnet localhost 5432
>>> after hit "enters", telnet exited. I checked
>>> postgresql_%S.log and see
>>> "LOG: invalid length of startup packet"
>>> which means postgresql server indeed saw the telnet
>>> connection.
>>>
>>> I tried this on any of my other machines in the LAN,
>>> I observed the same thing.
>>>
>>> In summary, it seemed the postgresql allowed TCP/IP
>>> connection, and
>>> worked well. However, when I ran the PHP script
>>> through web browser, I
>>> did not get connection. Instead, in Apache web
>>> server's error_log, I
>>> see
>>> [client 192.168.1.10] PHP Warning: pg_connect() [ >>> href='function.pg-connect'>function.pg-connect]:
>>> 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? in
>>> /foo/connectdb.php on
>>> line 2
>>>
>>> Interestingly, when I checked the log file for
>>> postgresql again, it
>>> did not have anything for this connection failure.
>>> It seemed the
>>> connection did not send to the database server at
>>> all. However,
>>> 1) I disabled firewall
>>> 2) I can telnet the ports
>>> 3) I can run the script from commandline
>>>
>>> What could go wrong?
>>>
>>> Thanks a lot for your reading this lengthy email.
>>> Waiting for your help!
>>>
>>> Gray
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 4: Have you searched our list archives?
>>>
>>> http://archives.postgresql.org
>>>
>>
>>
>> __________________________________________________
>> Do You Yahoo!?
>> Tired of spam? Yahoo! Mail has the best spam protection around
>> http://mail.yahoo.com
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Problem about connecting PostgreSQL through TCP/IP

Hello,=20

Very good point. But did configure the PHP with pgsql support. In
fact, I can successfully connect the postgresql server over
UNIX-domain socket, e.g., this code below uses the UNIX-domain socket
since I do not have include "host" and "port" in "pg_connect" function
call,
$conn =3D pg_connect("dbname=3Dfoodb user=3Dfoo password=3Dfoo") or die
("Can not connect to postgres");
$result=3Dpg_exec("SELECT * FROM footbl");
$fetch =3D pg_fetch_row($result);
print "";
print $fetch[0];
print "";
pg_close($conn); // Close this connection
?>
This code produced the expected result. However, when "host" and
"port" were given, the connection failed. What I can conclude is,
1) PHP was successfully configured with postgresql support. PHP
connected the database server UNIX-domain socket without any problem;
However, PHP could not establish connection with the database server
over TCP/IP
2) It seemed the database server accepted TCP/IP connection since I
could locate an entry in the database log file when a telnet attempt
at port 5432 was issued.
3) PHP did not pass the connection to the database at all because I
did not see any entry for the failed connection in the database log
file.
Am I getting it correctly? How should I do?=20

Thanks a lot!

Gray

On 8/17/05, shadowbox wrote:
> did you configure your PHP with pgsql support?
>=20
> On 8/17/05, Hui Chen wrote:
> > Hello,
> >
> > Thanks a lot!
> >
> > I did noticed "-i" option. I am using 8.0 whose manual
> > (http://www.postgresql.org/docs/8.0/interactive/app-postmast er.html)
> > states that "-i" option is deprecated. The manual
> > (http://www.postgresql.org/docs/8.0/interactive/runtime-conf ig.html#RUN=
TIME-CONFIG-CONNECTION)
> > also says that I can enable "listen_addresses" option in the
> > postgresql.conf to enable the server to listen over TCP/IP. What
> > really puzzles me is that I could actually telnet server at 5432 ports
> > and saw an corresponding entry in the log file; however, when I used
> > PHP to connect the server, the log file did not have an entry for the
> > failed connection attempt at all. Do you think what can go wrong?
> > Thanks!
> >
> > Gray
> >
> >
> > On 8/15/05, operationsengineer1@yahoo.com m> wrote:
> > > did you enable the "i" flag when you started the
> > > postmaster? if you didn't do that, tcp/ip will not
> > > connect!
> > >
> > > http://www.postgresql.org/docs/7.4/interactive/app-postmaste r.html
> > >
> > > good luck.
> > >
> > > --- Hui Chen wrote:
> > >
> > > > Hello, everyone,
> > > >
> > > > Can anyone help me? I am quite frustrating now. I
> > > > have been trying to
> > > > connect PostgreSQL server through TCP/IP connection
> > > > using PHP. I was
> > > > not successful. Could please read the following and
> > > > give me some
> > > > hints. Thanks a million.
> > > >
> > > > OS: Fedora Core 4 ("uname -s -r" yields "Linux
> > > > 2.6.12-1.1398_FC4smp")
> > > > PostgreSQL: version 8.0.3
> > > > PHP: version 5.0.4
> > > >
> > > > In postgresql.conf, these three lines were inserted,
> > > >
> > > > listen_addresses =3D '*'
> > > > port =3D 5432
> > > > max_connections =3D 100
> > > >
> > > > The following lines were inserted to pg_hba.conf:
> > > > local all all
> > > > password
> > > > host all all 127.0.0.1/32
> > > > password
> > > > host all all 192.168.0.0/1
> > > > password
> > > >
> > > > restart posgresql by
> > > > /sbin/service postgresql restart
> > > > The server was restarted OK.
> > > >
> > > > The PHP script used to connect database is as
> > > > follows,
> > > > > > > > $conn =3D pg_connect("host=3Dlocalhost port=3D5432
> > > > dbname=3Dfoodb user=3Dfoo
> > > > password=3Dfoo") or die ("Can not connect to
> > > > postgres");
> > > > $result=3Dpg_exec("SELECT * FROM footbl");
> > > > $fetch =3D pg_fetch_row($result);
> > > > print "";
> > > > print $fetch[0];
> > > > print "";
> > > >
> > > > pg_close($conn); // Close this connection
> > > > ?>
> > > >
> > > > Assume this PHP script is named as "connectdb.php",
> > > > then on
> > > > commandline, when I run the following,
> > > > php connectedb.php
> > > > I see correct results, e.g.,
> > > > foo
> > > >
> > > > On commandline again, I used the following to
> > > > connect database,
> > > > psql -d foodb -h localhost -p 5432 -U foo
> > > > Password: ************
> > > > Welcome to psql 8.0.3, 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
> > > >
> > > > foodb=3D>
> > > >
> > > > It is OK. Then I disabled "iptables" temporarily for
> > > > testing purpose by
> > > > /sbin/service iptables stop
> > > > Flushing firewall rules:
> > > > [ OK ]
> > > > Setting chains to policy ACCEPT: filter
> > > > [ OK ]
> > > > Unloading iptables modules:
> > > > [ OK ]
> > > >
> > > > On localhost, I tried this command,
> > > > telnet localhost 5432
> > > > after hit "enters", telnet exited. I checked
> > > > postgresql_%S.log and see
> > > > "LOG: invalid length of startup packet"
> > > > which means postgresql server indeed saw the telnet
> > > > connection.
> > > >
> > > > I tried this on any of my other machines in the LAN,
> > > > I observed the same thing.
> > > >
> > > > In summary, it seemed the postgresql allowed TCP/IP
> > > > connection, and
> > > > worked well. However, when I ran the PHP script
> > > > through web browser, I
> > > > did not get connection. Instead, in Apache web
> > > > server's error_log, I
> > > > see
> > > > [client 192.168.1.10] PHP Warning: pg_connect() [ > > > > href=3D'function.pg-connect'>function.pg-connect]:
> > > > 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? in
> > > > /foo/connectdb.php on
> > > > line 2
> > > >
> > > > Interestingly, when I checked the log file for
> > > > postgresql again, it
> > > > did not have anything for this connection failure.
> > > > It seemed the
> > > > connection did not send to the database server at
> > > > all. However,
> > > > 1) I disabled firewall
> > > > 2) I can telnet the ports
> > > > 3) I can run the script from commandline
> > > >
> > > > What could go wrong?
> > > >
> > > > Thanks a lot for your reading this lengthy email.
> > > > Waiting for your help!
> > > >
> > > > Gray
> > > >
> > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > TIP 4: Have you searched our list archives?
> > > >
> > > > http://archives.postgresql.org
> > > >
> > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Tired of spam? Yahoo! Mail has the best spam protection around
> > > http://mail.yahoo.com
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Problem about connecting PostgreSQL through TCP/IP

Hello, Matthew,

This is indeed great point. Thanks a lot for your help!

Gray

On 8/17/05, Matthew Terenzio wrote:
> This may be of interest to the PHP Dev group. I'll forward it to
> someone there.
>=20
>=20
> On Aug 17, 2005, at 1:03 AM, Hui Chen wrote:
>=20
> > Hello,
> >
> > Thanks a lot!
> >
> > I did noticed "-i" option. I am using 8.0 whose manual
> > (http://www.postgresql.org/docs/8.0/interactive/app-postmast er.html)
> > states that "-i" option is deprecated. The manual
> > (http://www.postgresql.org/docs/8.0/interactive/runtime-
> > config.html#RUNTIME-CONFIG-CONNECTION)
> > also says that I can enable "listen_addresses" option in the
> > postgresql.conf to enable the server to listen over TCP/IP. What
> > really puzzles me is that I could actually telnet server at 5432 ports
> > and saw an corresponding entry in the log file; however, when I used
> > PHP to connect the server, the log file did not have an entry for the
> > failed connection attempt at all. Do you think what can go wrong?
> > Thanks!
> >
> > Gray
> >
> >
> > On 8/15/05, operationsengineer1@yahoo.com
> > wrote:
> >> did you enable the "i" flag when you started the
> >> postmaster? if you didn't do that, tcp/ip will not
> >> connect!
> >>
> >> http://www.postgresql.org/docs/7.4/interactive/app-postmaste r.html
> >>
> >> good luck.
> >>
> >> --- Hui Chen wrote:
> >>
> >>> Hello, everyone,
> >>>
> >>> Can anyone help me? I am quite frustrating now. I
> >>> have been trying to
> >>> connect PostgreSQL server through TCP/IP connection
> >>> using PHP. I was
> >>> not successful. Could please read the following and
> >>> give me some
> >>> hints. Thanks a million.
> >>>
> >>> OS: Fedora Core 4 ("uname -s -r" yields "Linux
> >>> 2.6.12-1.1398_FC4smp")
> >>> PostgreSQL: version 8.0.3
> >>> PHP: version 5.0.4
> >>>
> >>> In postgresql.conf, these three lines were inserted,
> >>>
> >>> listen_addresses =3D '*'
> >>> port =3D 5432
> >>> max_connections =3D 100
> >>>
> >>> The following lines were inserted to pg_hba.conf:
> >>> local all all
> >>> password
> >>> host all all 127.0.0.1/32
> >>> password
> >>> host all all 192.168.0.0/1
> >>> password
> >>>
> >>> restart posgresql by
> >>> /sbin/service postgresql restart
> >>> The server was restarted OK.
> >>>
> >>> The PHP script used to connect database is as
> >>> follows,
> >>> > >>> $conn =3D pg_connect("host=3Dlocalhost port=3D5432
> >>> dbname=3Dfoodb user=3Dfoo
> >>> password=3Dfoo") or die ("Can not connect to
> >>> postgres");
> >>> $result=3Dpg_exec("SELECT * FROM footbl");
> >>> $fetch =3D pg_fetch_row($result);
> >>> print "";
> >>> print $fetch[0];
> >>> print "";
> >>>
> >>> pg_close($conn); // Close this connection
> >>> ?>
> >>>
> >>> Assume this PHP script is named as "connectdb.php",
> >>> then on
> >>> commandline, when I run the following,
> >>> php connectedb.php
> >>> I see correct results, e.g.,
> >>> foo
> >>>
> >>> On commandline again, I used the following to
> >>> connect database,
> >>> psql -d foodb -h localhost -p 5432 -U foo
> >>> Password: ************
> >>> Welcome to psql 8.0.3, 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
> >>>
> >>> foodb=3D>
> >>>
> >>> It is OK. Then I disabled "iptables" temporarily for
> >>> testing purpose by
> >>> /sbin/service iptables stop
> >>> Flushing firewall rules:
> >>> [ OK ]
> >>> Setting chains to policy ACCEPT: filter
> >>> [ OK ]
> >>> Unloading iptables modules:
> >>> [ OK ]
> >>>
> >>> On localhost, I tried this command,
> >>> telnet localhost 5432
> >>> after hit "enters", telnet exited. I checked
> >>> postgresql_%S.log and see
> >>> "LOG: invalid length of startup packet"
> >>> which means postgresql server indeed saw the telnet
> >>> connection.
> >>>
> >>> I tried this on any of my other machines in the LAN,
> >>> I observed the same thing.
> >>>
> >>> In summary, it seemed the postgresql allowed TCP/IP
> >>> connection, and
> >>> worked well. However, when I ran the PHP script
> >>> through web browser, I
> >>> did not get connection. Instead, in Apache web
> >>> server's error_log, I
> >>> see
> >>> [client 192.168.1.10] PHP Warning: pg_connect() [ > >>> href=3D'function.pg-connect'>function.pg-connect]:
> >>> 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? in
> >>> /foo/connectdb.php on
> >>> line 2
> >>>
> >>> Interestingly, when I checked the log file for
> >>> postgresql again, it
> >>> did not have anything for this connection failure.
> >>> It seemed the
> >>> connection did not send to the database server at
> >>> all. However,
> >>> 1) I disabled firewall
> >>> 2) I can telnet the ports
> >>> 3) I can run the script from commandline
> >>>
> >>> What could go wrong?
> >>>
> >>> Thanks a lot for your reading this lengthy email.
> >>> Waiting for your help!
> >>>
> >>> Gray
> >>>
> >>> ---------------------------(end of
> >>> broadcast)---------------------------
> >>> TIP 4: Have you searched our list archives?
> >>>
> >>> http://archives.postgresql.org
> >>>
> >>
> >>
> >> __________________________________________________
> >> Do You Yahoo!?
> >> Tired of spam? Yahoo! Mail has the best spam protection around
> >> http://mail.yahoo.com
> >>
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>=20
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Problem about connecting PostgreSQL through TCP/IP

On 8/14/05, Hui Chen wrote:
> Hello, everyone,
>=20
> Can anyone help me? I am quite frustrating now. I have been trying to
> connect PostgreSQL server through TCP/IP connection using PHP. I was
> not successful. Could please read the following and give me some
> hints. Thanks a million.
>=20
> OS: Fedora Core 4 ("uname -s -r" yields "Linux 2.6.12-1.1398_FC4smp")
> PostgreSQL: version 8.0.3
> PHP: version 5.0.4
> In postgresql.conf, these three lines were inserted,
> listen_addresses =3D '*'
> port =3D 5432
> max_connections =3D 100
>=20
> The following lines were inserted to pg_hba.conf:
> local all all password
> host all all 127.0.0.1/32 password
> host all all 192.168.0.0/1 password
>=20
> restart posgresql by
> /sbin/service postgresql restart
> The server was restarted OK.
>=20
> The PHP script used to connect database is as follows,
> > $conn =3D pg_connect("host=3Dlocalhost port=3D5432 dbname=3Dfoodb user=
=3Dfoo
> password=3Dfoo") or die ("Can not connect to postgres");
> $result=3Dpg_exec("SELECT * FROM footbl");
> $fetch =3D pg_fetch_row($result);
> print "";
> print $fetch[0];
> print "";
>=20
> pg_close($conn); // Close this connection
> ?>
>=20
> Assume this PHP script is named as "connectdb.php", then on
> commandline, when I run the following,
> php connectedb.php
> I see correct results, e.g.,
> foo
>=20
> On commandline again, I used the following to connect database,
> psql -d foodb -h localhost -p 5432 -U foo
> Password: ************
> Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
>=20
> 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
>=20
> foodb=3D>
>=20
> It is OK. Then I disabled "iptables" temporarily for testing purpose by
> /sbin/service iptables stop
> Flushing firewall rules: [ OK ]
> Setting chains to policy ACCEPT: filter [ OK ]
> Unloading iptables modules: [ OK ]
>=20
> On localhost, I tried this command,
> telnet localhost 5432
> after hit "enters", telnet exited. I checked postgresql_%S.log and see
> "LOG: invalid length of startup packet"
> which means postgresql server indeed saw the telnet connection.
>=20
> I tried this on any of my other machines in the LAN, I observed the same =
thing.
>=20
> In summary, it seemed the postgresql allowed TCP/IP connection, and
> worked well. However, when I ran the PHP script through web browser, I
> did not get connection. Instead, in Apache web server's error_log, I
> see
> [client 192.168.1.10] PHP Warning: pg_connect() [ > href=3D'function.pg-connect'>function.pg-connect]: 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? in /foo/connectdb.php on
> line 2
>=20
> Interestingly, when I checked the log file for postgresql again, it
> did not have anything for this connection failure. It seemed the
> connection did not send to the database server at all. However,
> 1) I disabled firewall
> 2) I can telnet the ports
> 3) I can run the script from commandline
>=20
> What could go wrong?
>=20
> Thanks a lot for your reading this lengthy email. Waiting for your help!
>=20
> Gray

could this have something to do with SELinux? can you disable it alltogether
and try?

javier

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Problem about connecting PostgreSQL through TCP/IP

The -i command line switch is depreciated in version 8. A new mechanism
is now preferred. In order to connect via TCP/IP you must tell
PostgreSQL to listen on TCP interfaces in the file

/var/lib/pgsql/data/postgresql.conf

Change:

listen_addresses = 'localhost' to listen_addresses = '*'

You must also have appropriate entries in pg_hba.conf for hosts and
authentication type. You could use something like:

local all all password
host all all 127.0.0.1/32 password
host all all 192.168.1.0/24 password

Be sure to enter your own subnet instead of 192.168.1.0.



On Wed, 2005-08-17 at 10:32 -0500, Hui Chen wrote:
> Hello,
>
> Very good point. But did configure the PHP with pgsql support. In
> fact, I can successfully connect the postgresql server over
> UNIX-domain socket, e.g., this code below uses the UNIX-domain socket
> since I do not have include "host" and "port" in "pg_connect" function
> call,
> > $conn = pg_connect("dbname=foodb user=foo password=foo") or die
> ("Can not connect to postgres");
> $result=pg_exec("SELECT * FROM footbl");
> $fetch = pg_fetch_row($result);
> print "";
> print $fetch[0];
> print "";
> pg_close($conn); // Close this connection
> ?>
> This code produced the expected result. However, when "host" and
> "port" were given, the connection failed. What I can conclude is,
> 1) PHP was successfully configured with postgresql support. PHP
> connected the database server UNIX-domain socket without any problem;
> However, PHP could not establish connection with the database server
> over TCP/IP
> 2) It seemed the database server accepted TCP/IP connection since I
> could locate an entry in the database log file when a telnet attempt
> at port 5432 was issued.
> 3) PHP did not pass the connection to the database at all because I
> did not see any entry for the failed connection in the database log
> file.
> Am I getting it correctly? How should I do?
>
> Thanks a lot!
>
> Gray
>
> On 8/17/05, shadowbox wrote:
> > did you configure your PHP with pgsql support?
> >
> > On 8/17/05, Hui Chen wrote:
> > > Hello,
> > >
> > > Thanks a lot!
> > >
> > > I did noticed "-i" option. I am using 8.0 whose manual
> > > (http://www.postgresql.org/docs/8.0/interactive/app-postmast er.html)
> > > states that "-i" option is deprecated. The manual
> > > (http://www.postgresql.org/docs/8.0/interactive/runtime-conf ig.html#RUNTIME-CONFIG-CONNECTION)
> > > also says that I can enable "listen_addresses" option in the
> > > postgresql.conf to enable the server to listen over TCP/IP. What
> > > really puzzles me is that I could actually telnet server at 5432 ports
> > > and saw an corresponding entry in the log file; however, when I used
> > > PHP to connect the server, the log file did not have an entry for the
> > > failed connection attempt at all. Do you think what can go wrong?
> > > Thanks!
> > >
> > > Gray
> > >
> > >
> > > On 8/15/05, operationsengineer1@yahoo.com wrote:
> > > > did you enable the "i" flag when you started the
> > > > postmaster? if you didn't do that, tcp/ip will not
> > > > connect!
> > > >
> > > > http://www.postgresql.org/docs/7.4/interactive/app-postmaste r.html
> > > >
> > > > good luck.
> > > >
> > > > --- Hui Chen wrote:
> > > >
> > > > > Hello, everyone,
> > > > >
> > > > > Can anyone help me? I am quite frustrating now. I
> > > > > have been trying to
> > > > > connect PostgreSQL server through TCP/IP connection
> > > > > using PHP. I was
> > > > > not successful. Could please read the following and
> > > > > give me some
> > > > > hints. Thanks a million.
> > > > >
> > > > > OS: Fedora Core 4 ("uname -s -r" yields "Linux
> > > > > 2.6.12-1.1398_FC4smp")
> > > > > PostgreSQL: version 8.0.3
> > > > > PHP: version 5.0.4
> > > > >
> > > > > In postgresql.conf, these three lines were inserted,
> > > > >
> > > > > listen_addresses = '*'
> > > > > port = 5432
> > > > > max_connections = 100
> > > > >
> > > > > The following lines were inserted to pg_hba.conf:
> > > > > local all all
> > > > > password
> > > > > host all all 127.0.0.1/32
> > > > > password
> > > > > host all all 192.168.0.0/1
> > > > > password
> > > > >
> > > > > restart posgresql by
> > > > > /sbin/service postgresql restart
> > > > > The server was restarted OK.
> > > > >
> > > > > The PHP script used to connect database is as
> > > > > follows,
> > > > > > > > > > $conn = pg_connect("host=localhost port=5432
> > > > > dbname=foodb user=foo
> > > > > password=foo") or die ("Can not connect to
> > > > > postgres");
> > > > > $result=pg_exec("SELECT * FROM footbl");
> > > > > $fetch = pg_fetch_row($result);
> > > > > print "";
> > > > > print $fetch[0];
> > > > > print "";
> > > > >
> > > > > pg_close($conn); // Close this connection
> > > > > ?>
> > > > >
> > > > > Assume this PHP script is named as "connectdb.php",
> > > > > then on
> > > > > commandline, when I run the following,
> > > > > php connectedb.php
> > > > > I see correct results, e.g.,
> > > > > foo
> > > > >
> > > > > On commandline again, I used the following to
> > > > > connect database,
> > > > > psql -d foodb -h localhost -p 5432 -U foo
> > > > > Password: ************
> > > > > Welcome to psql 8.0.3, 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
> > > > >
> > > > > foodb=>
> > > > >
> > > > > It is OK. Then I disabled "iptables" temporarily for
> > > > > testing purpose by
> > > > > /sbin/service iptables stop
> > > > > Flushing firewall rules:
> > > > > [ OK ]
> > > > > Setting chains to policy ACCEPT: filter
> > > > > [ OK ]
> > > > > Unloading iptables modules:
> > > > > [ OK ]
> > > > >
> > > > > On localhost, I tried this command,
> > > > > telnet localhost 5432
> > > > > after hit "enters", telnet exited. I checked
> > > > > postgresql_%S.log and see
> > > > > "LOG: invalid length of startup packet"
> > > > > which means postgresql server indeed saw the telnet
> > > > > connection.
> > > > >
> > > > > I tried this on any of my other machines in the LAN,
> > > > > I observed the same thing.
> > > > >
> > > > > In summary, it seemed the postgresql allowed TCP/IP
> > > > > connection, and
> > > > > worked well. However, when I ran the PHP script
> > > > > through web browser, I
> > > > > did not get connection. Instead, in Apache web
> > > > > server's error_log, I
> > > > > see
> > > > > [client 192.168.1.10] PHP Warning: pg_connect() [ > > > > > href='function.pg-connect'>function.pg-connect]:
> > > > > 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? in
> > > > > /foo/connectdb.php on
> > > > > line 2
> > > > >
> > > > > Interestingly, when I checked the log file for
> > > > > postgresql again, it
> > > > > did not have anything for this connection failure.
> > > > > It seemed the
> > > > > connection did not send to the database server at
> > > > > all. However,
> > > > > 1) I disabled firewall
> > > > > 2) I can telnet the ports
> > > > > 3) I can run the script from commandline
> > > > >
> > > > > What could go wrong?
> > > > >
> > > > > Thanks a lot for your reading this lengthy email.
> > > > > Waiting for your help!
> > > > >
> > > > > Gray
> > > > >
> > > > > ---------------------------(end of
> > > > > broadcast)---------------------------
> > > > > TIP 4: Have you searched our list archives?
> > > > >
> > > > > http://archives.postgresql.org
> > > > >
> > > >
> > > >
> > > > __________________________________________________
> > > > Do You Yahoo!?
> > > > Tired of spam? Yahoo! Mail has the best spam protection around
> > > > http://mail.yahoo.com
> > > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: Don't 'kill -9' the postmaster
> > >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
David Rogers
vnet 777-6522
Network Security Operations Center


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Problem about connecting PostgreSQL through TCP/IP

Hello, Javier,

Thanks. I tried accordingly. I change the line in /boot/grub/grob.conf from=
=20
kernel /vmlinuz-2.6.12-1.1398_FC4smp ro root=3DLABEL=3D/ rhgb quiet
to
kernel /vmlinuz-2.6.12-1.1398_FC4smp ro root=3DLABEL=3D/ rhgb quiet selinux=
=3D0
.. I then reboot the linux. I would assume it will disable SElinux
altogehter. The problem remains. I seems like it is not about SElinux.

Thanks!

Gray


On 8/17/05, javier wilson wrote:
> could this have something to do with SELinux? can you disable it alltoget=
her
> and try?

> javier
> On 8/14/05, Hui Chen wrote:
> > Hello, everyone,
> >
> > Can anyone help me? I am quite frustrating now. I have been trying to
> > connect PostgreSQL server through TCP/IP connection using PHP. I was
> > not successful. Could please read the following and give me some
> > hints. Thanks a million.
> >
> > OS: Fedora Core 4 ("uname -s -r" yields "Linux 2.6.12-1.1398_FC4smp")
> > PostgreSQL: version 8.0.3
> > PHP: version 5.0.4
> > In postgresql.conf, these three lines were inserted,
> > listen_addresses =3D '*'
> > port =3D 5432
> > max_connections =3D 100
> >
> > The following lines were inserted to pg_hba.conf:
> > local all all password
> > host all all 127.0.0.1/32 password
> > host all all 192.168.0.0/1 password
> >
> > restart posgresql by
> > /sbin/service postgresql restart
> > The server was restarted OK.
> >
> > The PHP script used to connect database is as follows,
> > > > $conn =3D pg_connect("host=3Dlocalhost port=3D5432 dbname=3Dfoodb use=
r=3Dfoo
> > password=3Dfoo") or die ("Can not connect to postgres");
> > $result=3Dpg_exec("SELECT * FROM footbl");
> > $fetch =3D pg_fetch_row($result);
> > print "";
> > print $fetch[0];
> > print "";
> >
> > pg_close($conn); // Close this connection
> > ?>
> >
> > Assume this PHP script is named as "connectdb.php", then on
> > commandline, when I run the following,
> > php connectedb.php
> > I see correct results, e.g.,
> > foo
> >
> > On commandline again, I used the following to connect database,
> > psql -d foodb -h localhost -p 5432 -U foo
> > Password: ************
> > Welcome to psql 8.0.3, 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
> >
> > foodb=3D>
> >
> > It is OK. Then I disabled "iptables" temporarily for testing purpose by
> > /sbin/service iptables stop
> > Flushing firewall rules: [ OK ]
> > Setting chains to policy ACCEPT: filter [ OK ]
> > Unloading iptables modules: [ OK ]
> >
> > On localhost, I tried this command,
> > telnet localhost 5432
> > after hit "enters", telnet exited. I checked postgresql_%S.log and see
> > "LOG: invalid length of startup packet"
> > which means postgresql server indeed saw the telnet connection.
> >
> > I tried this on any of my other machines in the LAN, I observed the sam=
e thing.
> >
> > In summary, it seemed the postgresql allowed TCP/IP connection, and
> > worked well. However, when I ran the PHP script through web browser, I
> > did not get connection. Instead, in Apache web server's error_log, I
> > see
> > [client 192.168.1.10] PHP Warning: pg_connect() [ > > href=3D'function.pg-connect'>function.pg-connect]: 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? in /foo/connectdb.php on
> > line 2
> >
> > Interestingly, when I checked the log file for postgresql again, it
> > did not have anything for this connection failure. It seemed the
> > connection did not send to the database server at all. However,
> > 1) I disabled firewall
> > 2) I can telnet the ports
> > 3) I can run the script from commandline
> >
> > What could go wrong?
> >
> > Thanks a lot for your reading this lengthy email. Waiting for your help!
> >
> > Gray
>=20
> could this have something to do with SELinux? can you disable it alltoget=
her
> and try?
>=20
> javier
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Problem about connecting PostgreSQL through TCP/IP

which is what I did. I encounter the connection problem with the
configuration the same as what you have shown except that the ip
addresses and its masks were different. Thanks!

Gray

On 8/17/05, David Rogers wrote:
> The -i command line switch is depreciated in version 8. A new mechanism
> is now preferred. In order to connect via TCP/IP you must tell
> PostgreSQL to listen on TCP interfaces in the file
>=20
> /var/lib/pgsql/data/postgresql.conf
>=20
> Change:
>=20
> listen_addresses =3D 'localhost' to listen_addresses =3D '*'
>=20
> You must also have appropriate entries in pg_hba.conf for hosts and
> authentication type. You could use something like:
>=20
> local all all password
> host all all 127.0.0.1/32 password
> host all all 192.168.1.0/24 password
>=20
> Be sure to enter your own subnet instead of 192.168.1.0.
>=20
>=20
>=20
> On Wed, 2005-08-17 at 10:32 -0500, Hui Chen wrote:
> > Hello,
> >
> > Very good point. But did configure the PHP with pgsql support. In
> > fact, I can successfully connect the postgresql server over
> > UNIX-domain socket, e.g., this code below uses the UNIX-domain socket
> > since I do not have include "host" and "port" in "pg_connect" function
> > call,
> > > > $conn =3D pg_connect("dbname=3Dfoodb user=3Dfoo password=3Dfoo") or=
die
> > ("Can not connect to postgres");
> > $result=3Dpg_exec("SELECT * FROM footbl");
> > $fetch =3D pg_fetch_row($result);
> > print "";
> > print $fetch[0];
> > print "";
> > pg_close($conn); // Close this connection
> > ?>
> > This code produced the expected result. However, when "host" and
> > "port" were given, the connection failed. What I can conclude is,
> > 1) PHP was successfully configured with postgresql support. PHP
> > connected the database server UNIX-domain socket without any problem;
> > However, PHP could not establish connection with the database server
> > over TCP/IP
> > 2) It seemed the database server accepted TCP/IP connection since I
> > could locate an entry in the database log file when a telnet attempt
> > at port 5432 was issued.
> > 3) PHP did not pass the connection to the database at all because I
> > did not see any entry for the failed connection in the database log
> > file.
> > Am I getting it correctly? How should I do?
> >
> > Thanks a lot!
> >
> > Gray
> >
> > On 8/17/05, shadowbox wrote:
> > > did you configure your PHP with pgsql support?
> > >
> > > On 8/17/05, Hui Chen wrote:
> > > > Hello,
> > > >
> > > > Thanks a lot!
> > > >
> > > > I did noticed "-i" option. I am using 8.0 whose manual
> > > > (http://www.postgresql.org/docs/8.0/interactive/app-postmast er.html)
> > > > states that "-i" option is deprecated. The manual
> > > > (http://www.postgresql.org/docs/8.0/interactive/runtime-conf ig.html=
#RUNTIME-CONFIG-CONNECTION)
> > > > also says that I can enable "listen_addresses" option in the
> > > > postgresql.conf to enable the server to listen over TCP/IP. What
> > > > really puzzles me is that I could actually telnet server at 5432 po=
rts
> > > > and saw an corresponding entry in the log file; however, when I used
> > > > PHP to connect the server, the log file did not have an entry for t=
he
> > > > failed connection attempt at all. Do you think what can go wrong?
> > > > Thanks!
> > > >
> > > > Gray
> > > >
> > > >
> > > > On 8/15/05, operationsengineer1@yahoo.com o.com> wrote:
> > > > > did you enable the "i" flag when you started the
> > > > > postmaster? if you didn't do that, tcp/ip will not
> > > > > connect!
> > > > >
> > > > > http://www.postgresql.org/docs/7.4/interactive/app-postmaste r.html
> > > > >
> > > > > good luck.
> > > > >
> > > > > --- Hui Chen wrote:
> > > > >
> > > > > > Hello, everyone,
> > > > > >
> > > > > > Can anyone help me? I am quite frustrating now. I
> > > > > > have been trying to
> > > > > > connect PostgreSQL server through TCP/IP connection
> > > > > > using PHP. I was
> > > > > > not successful. Could please read the following and
> > > > > > give me some
> > > > > > hints. Thanks a million.
> > > > > >
> > > > > > OS: Fedora Core 4 ("uname -s -r" yields "Linux
> > > > > > 2.6.12-1.1398_FC4smp")
> > > > > > PostgreSQL: version 8.0.3
> > > > > > PHP: version 5.0.4
> > > > > >
> > > > > > In postgresql.conf, these three lines were inserted,
> > > > > >
> > > > > > listen_addresses =3D '*'
> > > > > > port =3D 5432
> > > > > > max_connections =3D 100
> > > > > >
> > > > > > The following lines were inserted to pg_hba.conf:
> > > > > > local all all
> > > > > > password
> > > > > > host all all 127.0.0.1/32
> > > > > > password
> > > > > > host all all 192.168.0.0/1
> > > > > > password
> > > > > >
> > > > > > restart posgresql by
> > > > > > /sbin/service postgresql restart
> > > > > > The server was restarted OK.
> > > > > >
> > > > > > The PHP script used to connect database is as
> > > > > > follows,
> > > > > > > > > > > > $conn =3D pg_connect("host=3Dlocalhost port=3D5432
> > > > > > dbname=3Dfoodb user=3Dfoo
> > > > > > password=3Dfoo") or die ("Can not connect to
> > > > > > postgres");
> > > > > > $result=3Dpg_exec("SELECT * FROM footbl");
> > > > > > $fetch =3D pg_fetch_row($result);
> > > > > > print "";
> > > > > > print $fetch[0];
> > > > > > print "";
> > > > > >
> > > > > > pg_close($conn); // Close this connection
> > > > > > ?>
> > > > > >
> > > > > > Assume this PHP script is named as "connectdb.php",
> > > > > > then on
> > > > > > commandline, when I run the following,
> > > > > > php connectedb.php
> > > > > > I see correct results, e.g.,
> > > > > > foo
> > > > > >
> > > > > > On commandline again, I used the following to
> > > > > > connect database,
> > > > > > psql -d foodb -h localhost -p 5432 -U foo
> > > > > > Password: ************
> > > > > > Welcome to psql 8.0.3, 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
> > > > > >
> > > > > > foodb=3D>
> > > > > >
> > > > > > It is OK. Then I disabled "iptables" temporarily for
> > > > > > testing purpose by
> > > > > > /sbin/service iptables stop
> > > > > > Flushing firewall rules:
> > > > > > [ OK ]
> > > > > > Setting chains to policy ACCEPT: filter
> > > > > > [ OK ]
> > > > > > Unloading iptables modules:
> > > > > > [ OK ]
> > > > > >
> > > > > > On localhost, I tried this command,
> > > > > > telnet localhost 5432
> > > > > > after hit "enters", telnet exited. I checked
> > > > > > postgresql_%S.log and see
> > > > > > "LOG: invalid length of startup packet"
> > > > > > which means postgresql server indeed saw the telnet
> > > > > > connection.
> > > > > >
> > > > > > I tried this on any of my other machines in the LAN,
> > > > > > I observed the same thing.
> > > > > >
> > > > > > In summary, it seemed the postgresql allowed TCP/IP
> > > > > > connection, and
> > > > > > worked well. However, when I ran the PHP script
> > > > > > through web browser, I
> > > > > > did not get connection. Instead, in Apache web
> > > > > > server's error_log, I
> > > > > > see
> > > > > > [client 192.168.1.10] PHP Warning: pg_connect() [ > > > > > > href=3D'function.pg-connect'>function.pg-connect]:
> > > > > > 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? in
> > > > > > /foo/connectdb.php on
> > > > > > line 2
> > > > > >
> > > > > > Interestingly, when I checked the log file for
> > > > > > postgresql again, it
> > > > > > did not have anything for this connection failure.
> > > > > > It seemed the
> > > > > > connection did not send to the database server at
> > > > > > all. However,
> > > > > > 1) I disabled firewall
> > > > > > 2) I can telnet the ports
> > > > > > 3) I can run the script from commandline
> > > > > >
> > > > > > What could go wrong?
> > > > > >
> > > > > > Thanks a lot for your reading this lengthy email.
> > > > > > Waiting for your help!
> > > > > >
> > > > > > Gray
> > > > > >
> > > > > > ---------------------------(end of
> > > > > > broadcast)---------------------------
> > > > > > TIP 4: Have you searched our list archives?
> > > > > >
> > > > > > http://archives.postgresql.org
> > > > > >
> > > > >
> > > > >
> > > > > __________________________________________________
> > > > > Do You Yahoo!?
> > > > > Tired of spam? Yahoo! Mail has the best spam protection around
> > > > > http://mail.yahoo.com
> > > > >
> > > >
> > > > ---------------------------(end of broadcast)----------------------=
-----
> > > > TIP 2: Don't 'kill -9' the postmaster
> > > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> --
> David Rogers
> vnet 777-6522
> Network Security Operations Center
>=20
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Problem about connecting PostgreSQL through TCP/IP

------=_Part_8920_26248182.1124339844595
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 8/14/05, Hui Chen wrote:
>=20
> Hello, everyone,
>=20
> Can anyone help me? I am quite frustrating now. I have been trying to
> connect PostgreSQL server through TCP/IP connection using PHP. I was
> not successful. Could please read the following and give me some
> hints. Thanks a million.
>=20
> OS: Fedora Core 4 ("uname -s -r" yields "Linux 2.6.12-1.1398_FC4smp")
> PostgreSQL: version 8.0.3
> PHP: version 5.0.4
>=20
> In postgresql.conf, these three lines were inserted,
> listen_addresses =3D '*'
> port =3D 5432
> max_connections =3D 100
>=20
> The following lines were inserted to pg_hba.conf:
> local all all password
> host all all 127.0.0.1/32 password
> host all all 192.168.0.0/1 password
>=20
> restart posgresql by
> /sbin/service postgresql restart
> The server was restarted OK.
>=20
> The PHP script used to connect database is as follows,
> > $conn =3D pg_connect("host=3Dlocalhost port=3D5432 dbname=3Dfoodb user=3D=
foo
> password=3Dfoo") or die ("Can not connect to postgres");
> $result=3Dpg_exec("SELECT * FROM footbl");
> $fetch =3D pg_fetch_row($result);
> print "";
> print $fetch[0];
> print "";
>=20
> pg_close($conn); // Close this connection
> ?>
>=20
> Assume this PHP script is named as "connectdb.php", then on
> commandline, when I run the following,
> php connectedb.php
> I see correct results, e.g.,
> foo
>=20
> On commandline again, I used the following to connect database,
> psql -d foodb -h localhost -p 5432 -U foo
> Password: ************

Whether the password you typed here is "foo"?

Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
>=20
> 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
>=20
> foodb=3D>
>=20
> It is OK. Then I disabled "iptables" temporarily for testing purpose by
> /sbin/service iptables stop
> Flushing firewall rules: [ OK ]
> Setting chains to policy ACCEPT: filter [ OK ]
> Unloading iptables modules: [ OK ]
>=20
> On localhost, I tried this command,
> telnet localhost 5432
> after hit "enters", telnet exited. I checked postgresql_%S.log and see
> "LOG: invalid length of startup packet"
> which means postgresql server indeed saw the telnet connection.
>=20
> I tried this on any of my other machines in the LAN, I observed the same=
=20
> thing.
>=20
> In summary, it seemed the postgresql allowed TCP/IP connection, and
> worked well. However, when I ran the PHP script through web browser, I
> did not get connection. Instead, in Apache web server's error_log, I
> see
> [client 192.168.1.10 ] PHP Warning: pg_connect() [ > href=3D'function.pg-connect'>function.pg-connect]: 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? in /foo/connectdb.php on
> line 2
>=20
> Interestingly, when I checked the log file for postgresql again, it
> did not have anything for this connection failure. It seemed the
> connection did not send to the database server at all. However,
> 1) I disabled firewall
> 2) I can telnet the ports
> 3) I can run the script from commandline
>=20
> What could go wrong?
>=20
> Thanks a lot for your reading this lengthy email. Waiting for your help!
>=20
> Gray
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>=20
> http://archives.postgresql.org
>=20



--=20
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

------=_Part_8920_26248182.1124339844595
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Re: Problem about connecting PostgreSQL through TCP/IP

Thanks a lot! If I typed the password wrong, I should be able to see a
corresponding entry in the log file. The entry should look like
"FATAL: password authentication failed for user foo". However, I did
not see any entry corresponding to the failed connection at all.
Therefore, I ruled out the possibility that the wrong password was the
reason.

Gray

On 8/17/05, Gnanavel S wrote:
>=20
>=20
>=20
> On 8/14/05, Hui Chen wrote:
> > Hello, everyone,
> >=20
> > Can anyone help me? I am quite frustrating now. I have been trying to
> > connect PostgreSQL server through TCP/IP connection using PHP. I was
> > not successful. Could please read the following and give me some=20
> > hints. Thanks a million.
> >=20
> > OS: Fedora Core 4 ("uname -s -r" yields "Linux 2.6.12-1.1398_FC4smp")
> > PostgreSQL: version 8.0.3
> > PHP: version 5.0.4
> >=20
> > In postgresql.conf, these three lines were inserted,=20
> > listen_addresses =3D '*'
> > port =3D 5432
> > max_connections =3D 100
> >=20
> > The following lines were inserted to pg_hba.conf:
> > local all all =

> password
> > host all all 127.0.0.1/32 password
> > host all all 192.168.0.0/1 password
> >=20
> > restart posgresql by
> > /sbin/service postgresql restart
> > The server was restarted OK.
> >=20
> > The PHP script used to connect database is as follows,=20
> > > > $conn =3D pg_connect("host=3Dlocalhost port=3D5432 dbname=3Dfoodb use=
r=3Dfoo
> > password=3Dfoo") or die ("Can not connect to postgres");
> > $result=3Dpg_exec("SELECT * FROM footbl");
> > $fetch =3D pg_fetch_row($result);
> > print "";
> > print $fetch[0];
> > print "";
> >=20
> > pg_close($conn); // Close this connection
> > ?>
> >=20
> > Assume this PHP script is named as "connectdb.php", then on
> > commandline, when I run the following,
> > php connectedb.php
> > I see correct results, e.g.,
> > foo
> >=20
> > On commandline again, I used the following to connect database,
> > psql -d foodb -h localhost -p 5432 -U foo
> > Password: ************
> Whether the password you typed here is "foo"?
>
> >=20
> > Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
> >=20
> > Type: \copyright for distribution terms=20
> > \h for help with SQL commands
> > \? for help with psql commands
> > \g or terminate with semicolon to execute query
> > \q to quit
> >=20
> > foodb=3D>
> >=20
> > It is OK. Then I disabled "iptables" temporarily for testing purpose by=
=20
> > /sbin/service iptables stop
> > Flushing firewall rules: =20=

> [ OK ]
> > Setting chains to policy ACCEPT: filter [
> OK ]
> > Unloading iptables modules: =

> [ OK ]
> >=20
> > On localhost, I tried this command,
> > telnet localhost 5432
> > after hit "enters", telnet exited. I checked postgresql_%S.log and see
> > "LOG: invalid length of startup packet"=20
> > which means postgresql server indeed saw the telnet connection.
> >=20
> > I tried this on any of my other machines in the LAN, I observed the same
> thing.
> >=20
> > In summary, it seemed the postgresql allowed TCP/IP connection, and=20
> > worked well. However, when I ran the PHP script through web browser, I
> > did not get connection. Instead, in Apache web server's error_log, I
> > see
> > [client 192.168.1.10] PHP Warning: pg_connect() [ > > href=3D'function.pg-connect'>function.pg-connect]: Unable to connect
> > to PostgreSQL server: could not connect to server: Permission
> > denied\n\tIs the server running on host "localhost" and=20
> > accepting\n\tTCP/IP connections on port 5432? in /foo/connectdb.php on
> > line 2
> >=20
> > Interestingly, when I checked the log file for postgresql again, it
> > did not have anything for this connection failure. It seemed the=20
> > connection did not send to the database server at all. However,
> > 1) I disabled firewall
> > 2) I can telnet the ports
> > 3) I can run the script from commandline
> >=20
> > What could go wrong?
> >=20
> > Thanks a lot for your reading this lengthy email. Waiting for your help=
!=20
> >=20
> > Gray
> >=20
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >=20
> > http://archives.postgresql.org=20
> >=20
>=20
>=20
>=20
> --=20
> with regards,
> S.Gnanavel
> Satyam Computer Services Ltd.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq