X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 458C452BFD for ; Wed, 17 Aug 2005 14:48:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69471-05 for ; Wed, 17 Aug 2005 17:48:39 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.204]) by svr1.postgresql.org (Postfix) with ESMTP id CF39A52BE5 for ; Wed, 17 Aug 2005 14:48:38 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i22so192295wra for ; Wed, 17 Aug 2005 10:48:38 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=B6zg0QJjjD963O/bJoIrBrqfgqts+Z/MiH0uLuCk81QrMexHIFhPNbbjZ+tMlzZxqL7qHmePxoCLThucOI9cHggAJgt0My+OLg6iZxnO6WOwaZi1fvkC1HXsOq8QocW/xawsqPT9w2d/SFZM+yHbxAqpbVsR4NgNgkh+eAPXKYo= Received: by 10.54.142.2 with SMTP id p2mr588178wrd; Wed, 17 Aug 2005 10:48:38 -0700 (PDT) Received: by 10.54.98.9 with HTTP; Wed, 17 Aug 2005 10:48:38 -0700 (PDT) Message-ID: <923c3c1405081710486f4a1bfa@mail.gmail.com> Date: Wed, 17 Aug 2005 12:48:38 -0500 From: Hui Chen To: David Rogers Subject: Re: Problem about connecting PostgreSQL through TCP/IP Cc: pgsql-php@postgresql.org In-Reply-To: <1124296287.3061.13.camel@nsoc6522a.mcilink.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <923c3c1405081410326a1c2880@mail.gmail.com> <20050816021902.73901.qmail@web33304.mail.mud.yahoo.com> <923c3c14050816220347aaa1f9@mail.gmail.com> <923c3c14050817083231e4870@mail.gmail.com> <1124296287.3061.13.camel@nsoc6522a.mcilink.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.067 required=5 tests=[AWL=0.043, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200508/19 X-Sequence-Number: 3231 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-postmaster.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 po= rts > > > > and saw an corresponding entry in the log file; however, when I use= d > > > > 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 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-postmaster.htm= l > > > > > > > > > > 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 >