As per a recent discussion in pgsql-admin,
http://archives.postgresql.org/pgsql-admin/2006-09/msg00297.php
libpq doesn't cope well with the situation where the server is
configured to allow only SSL connections (or only non-SSL connections)
and there is some unrelated-to-SSL connection problem such as wrong
password. The reason is that libpq is set up to retry with the other
kind of connection (either dropping or adding SSL) for just about any
sort of error returned by the server. This may lead to reporting "no
pg_hba.conf entry", or some such, rather than the more useful "password
authentication failed".
I am tempted to propose that libpq should only retry in the other mode
when the server specifically returns "no pg_hba.conf entry", and not for
other server errors (beyond the initial do-you-do-SSL-at-all handshake
of course). This would save a useless fork() cycle on the server as
well as make it more likely that we return a useful error message.
There are some corner cases where this might fail to connect when
a blind retry would have succeeded, but they all involve the server
offering different auth methods depending on SSL or not --- an example
is "hostssl + ident" and "hostnossl + password", and you fail the ident
test but could have produced the correct password. ISTM that is a
scenario where the user should use the "sslmode" parameter to control
which method is tried first.
One problem with implementing this proposal is that we currently use the
generic INVALID_AUTHORIZATION_SPECIFICATION sqlstate for a bunch of
distinct conditions including "no pg_hba.conf entry". Looking directly
at the error string is of course not localization-proof, so we'd have to
break down that errcode into some more-specific categories. Which is
probably not a bad idea anyway, but it would mean that the nicer
behavior would only happen when talking to an 8.2 or later server.
Thoughts? Is this something to tackle during beta, or must we put it
off till 8.3?
regards, tom lane