Skip site navigation (1) Skip section navigation (2)

Re: Problem about connecting PostgreSQL through TCP/IP

From: Hui Chen <gray(dot)chenhui(at)gmail(dot)com>
To: David Rogers <David(dot)Rogers(at)mci(dot)com>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: Problem about connecting PostgreSQL through TCP/IP
Date: 2005-08-17 17:48:38
Message-ID: 923c3c1405081710486f4a1bfa@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-php
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 <David(dot)Rogers(at)mci(dot)com> 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
> 
> /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,
> > <?php
> >    $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 "<html><body>";
> >    print $fetch[0];
> >    print "<body></html>";
> >    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 <percy(dot)deleon(at)gmail(dot)com> wrote:
> > > did you configure your PHP with pgsql support?
> > >
> > > On 8/17/05, Hui Chen <gray(dot)chenhui(at)gmail(dot)com> 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 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(at)yahoo(dot)com <operationsengineer1(at)yahoo(dot)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.html
> > > > >
> > > > > good luck.
> > > > >
> > > > > --- Hui Chen <gray(dot)chenhui(at)gmail(dot)com> 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,
> > > > > > <?php
> > > > > >   $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 "<html><body>";
> > > > > >    print $fetch[0];
> > > > > >    print "<body></html>";
> > > > > >
> > > > > >    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.,
> > > > > > <html><body>foo<body></html>
> > > > > >
> > > > > > 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() [<a
> > > > > > href='function.pg-connect'>function.pg-connect</a>]:
> > > > > > Unable to connect
> > > > > > to PostgreSQL server: could not connect to server:
> > > > > > Permission
> > > > > > denied\n\tIs the server running on host
> > > > > > "localhost" and
> > > > > > accepting\n\tTCP/IP connections on port 5432? 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
> 
>

In response to

pgsql-php by date

Next:From: Gnanavel SDate: 2005-08-18 04:37:24
Subject: Re: Problem about connecting PostgreSQL through TCP/IP
Previous:From: Hui ChenDate: 2005-08-17 17:46:16
Subject: Re: Problem about connecting PostgreSQL through TCP/IP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group