Обсуждение: DB Error: connect failed

Hi!

On a Red Hat Linux 7.3 system with PostgreSQL 7.3.3, I am having a
problem connecting to the database, using PEAR/PHP code in a web page.
The code I have worked out so far is:

      $dsn = array('phptype'  => 'pgsql',
                   'username' => 'simpatic_www',
                   'password' => 'gottago',
                   'hostspec' => 'localhost',
                   'database' => 'simpatic_logbook'
      );
      $options = array('debug'       => 2,
                       'portability' => DB_PORTABILITY_ALL
      );
      $db =& DB::connect($dsn, $options);
      if (DB::isError($db)) { die($db->getMessage()); }

Only the password has been changed to protect the guilty. The user
'simpatic_www' is created by CPANEL webhosting software's control panel.
As an attempt to check correctness of the password, I logged into the
simpatic_logbook database as the postgres user (superuser?) and typed:

simpatic_logbook=# alter user simpatic_www with password 'gottago';
ALTER USER

So in theory it succeeded. Connect still fails, though. What gets me is
that *all* I get is that one "DB Error: connect failed" error. Is there
a way to get more detail, so I at least know exactly *why* it is
failing? Can I get psql to show me simpatic_www's unencrypted password
to see what it has?

Thanks for any pointers,

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>

On Wed, 2005-02-02 at 23:33 -0600, Rodolfo J. Paiz wrote:
> What gets me is that *all* I get is
> that one "DB Error: connect failed" error.

Well, sorry to reply to my own post but I've got a little more detail
now. I modified my web page with this (straight from the PEAR manual):

    if (DB::isError($db)) {
    /*
     * This is not what you would really want to do in
     * your program.  It merely demonstrates what kinds
     * of data you can get back from error objects.
     */
    echo 'Standard Message: ' . $db->getMessage() . "<br>";
    echo 'Standard Code: ' . $db->getCode() . "<br>";
    echo 'DBMS/User Message: ' . $db->getUserInfo() . "<br>";
    echo 'DBMS/Debug Message: ' . $db->getDebugInfo() . "<br>";
 #   exit;
}

Now I get this as an error message:

Standard Message: DB Error: connect failed
Standard Code: -24
DBMS/User Message: [nativecode=Unable to connect to PostgreSQL server:
could not connect to server: Connection refused Is the server running on
host localhost and accepting TCP/IP connections on port 5432?] ** Array
DBMS/Debug Message: [nativecode=Unable to connect to PostgreSQL server:
could not connect to server: Connection refused Is the server running on
host localhost and accepting TCP/IP connections on port 5432?] ** Array

I do know the database is running, as I can use it with both psql and
phpPgAdmin. So, just the web page fails. Now to figure out how.

It is worth mentioning that the server is set up to IDENT users, so I
cannot log in as user "simpatic" and use psql as simpatic_www. Fails.
For perhaps the same or other reasons, phpPgAdmin also fails the
simpatic_www user... not sure why. So I cannot be 100% certain that the
user/pass of simpatic_www is correct. But it should be.

Thanks for any hints,

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>

On Wed, 2005-02-02 at 23:49 -0600, Rodolfo J. Paiz wrote:
> I cannot be 100% certain that the
> user/pass of simpatic_www is correct. But it should be.

Since I'm posting in sequence here, just one more before I crash for the
night. Thanks to the good offices of Jason Dixon, I've been able to
determine that the simpatic_www user had no privileges at all on the
simpatic_logbook database.

Fixed the permissions by granting only SELECT to all tables at this
time. Was also able to verify that simpatic_www *is* able to log into
the database via psql so the password is correct. Am also able to log
into phpPgAdmin now as simpatic_www, although it shows no databases.

Lost and confused... somewhat better educated than I was earlier, but
still lost and confused. Hopefully tomorrow will shed some more light.

Cheers,

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>

"Rodolfo J. Paiz" <rpaiz@simpaticus.com> writes:
> Now I get this as an error message:

> Standard Message: DB Error: connect failed
> Standard Code: -24
> DBMS/User Message: [nativecode=Unable to connect to PostgreSQL server:
> could not connect to server: Connection refused Is the server running on
> host localhost and accepting TCP/IP connections on port 5432?] ** Array

This generally means either that the server isn't listening for TCP
connections (did you turn on tcpip_socket or use -i switch?) or that
the kernel is filtering TCP connection attempts (check firewall rules
for localhost connections).

> I do know the database is running, as I can use it with both psql and
> phpPgAdmin. So, just the web page fails. Now to figure out how.

Dunno about phpPgAdmin, but psql defaults to connecting through a Unix
socket, which doesn't have either of the above risks.

            regards, tom lane

On Thu, 2005-02-03 at 01:42 -0500, Tom Lane wrote:
> Dunno about phpPgAdmin, but psql defaults to connecting through a Unix
> socket, which doesn't have either of the above risks.
>

I *thought* I was connecting to the Unix socket, so of course such a
thing should not have been a problem. I went back to the DSN array I had
written after your message and (after a little trial and error) figured
out to add "'protocol' => 'unix'" as one of the values in the array. The
final (working) DSN array and connect process was:

          $dsn = array('phptype'  => 'pgsql',
                       'username' => 'simpatic_www',
                       'password' => 'gottago',
                       'protocol' => 'unix',
                       'hostspec' => 'localhost',
                       'database' => 'simpatic_logbook'
          );
          $options = array('debug'       => 2,
                           'portability' => DB_PORTABILITY_ALL
          );
          $db =& DB::connect($dsn, $options);
          if (DB::isError($db)) { die($db->getMessage()); }

The connect *appears* to have worked fine, since I then get a page with
no database output (I've made no queries) but also no errors. So I'm
guessing the connect is done... thanks!

Of course, everything immediately broke again when I tried a query.  But
after a little time with the PEAR manual, and googling for bizarre
errors I would never have expected, the thing works. I have a simple
query that returns one value, but it's working! I now have only two
small details that pique my curiosity:

  1. What is "debug=2" and do I need it? Happy to RTFM... URL to FM?

  2. What is the significance of "=&" instead of "="? I can't find an
     explanation anywhere.

My thanks to everyone who has helped me get over this first hump in my
PostgreSQL usage.

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>