Re: [INTERFACES] 'pgaccess' - Can't Connect to Backend

Kenneth Jacker <khj@cs.appstate.edu> writes:
> I can connect to the backend via 'psql' but not 'pgaccess'.
> Both 'pgaccess' and 'postgreSQL' are running on the same machine.

Probably, access to the backend works OK via a Unix socket, but does
not work via TCP/IP.  Try
    psql -h localhost your-database-name
and I'll bet it fails just like pgaccess does.  (Unless Constantin
has changed something recently, pgaccess always tries to connect
via TCP/IP, which is why it fails even for the default case.)

> Pgaccess gives the message:
>     Error connecting database
>     Connection to database failed
>     connectDB() failed: Is the postmaster running
>     and accepting TCP/IP (with -i) connections
>     at 'localhost' on port '5432'?

My first thought was that your pg_hba.conf file is messed up,
but on second thought I don't like that theory.  The particular
error you quote only comes out if libpq's connect() call fails,
and that would mean failure even to contact the postmaster ---
authorization checks don't happen until later.

> Here's the relevant process info (in particular, note the '-i'
> option):
>     /usr/bin/postmaster -i -S -D/var/lib/pgsql

OK, silly mistake #1 is eliminated.

> Also, the following 'netstat' output (corresponding to the 'psql'
> connection) shows that port '5432' is, in fact, being used:

>   Proto Recv-Q Send-Q Local Address           Foreign Address         State
>   tcp        0      2 cs:3123                 localhost:5432          CLOSE

I think this line has nothing to do with your psql session --- unless
you ran psql with -h switch or PGHOST environment setting, it would've
connected via Unix socket and not via TCP at all.  (You might find its
connection in the "Unix domain" part of netstat's listing.)  My guess
is that this line reflects the failed attempt to connect from pgaccess.
That guess is reinforced by the fact that the socket state is shown
as CLOSE (ie, connection in process of closing down) not ESTABLISHED.

So now we have to figure out how it's getting that way.  Does anything
get added to the postmaster's log file (typically ~postgres/server.log)
when the connect attempt fails?  I'd look for lines mentioning
StreamConnection in particular.

Beyond that I'm fresh out of ideas.  It's clearly an oversight that
libpq doesn't report the kernel error code when the connect() call fails
--- that would be very useful to know right now ...  I'd suggest
modifying src/interfaces/libpq/fe-connect.c so that strerror(errno) is
included in the error report when connect() fails inside the connectDB()
routine.  (Make it look more like the handling of failures from the
other kernel calls nearby...)  Armed with that, we can try to make a
better guess about the problem.

            regards, tom lane