Possible PostgreSQL 8.3beta4 bug with MD5 authentication in psql?

Hi everyone,

I think that I may have found a minor bug with PostgreSQL 8.3beta4 with
respect to md5 authentication. I actually discovered this on Win32, but
it appears that the behaviour is the same under Linux too.

As part of the PostGIS install under Win32, I have a few scripts that
check for the existence of a particular database by doing the following:

psql -d adatabase -h localhost -c "SELECT version();"

By checking the psql exit code, it is fairly easy to see whether this
failed, and if so display the contents of stdout for the user. The
problem I have is that under PostgreSQL 8.3beta4, if the database
doesn't exist then I get an extra password prompt which breaks the
install scripts as they run in the background :(

To recreate this is fairly easy:

1. Temporarily rename any .pgpass files so they aren't found by libpq
2. Stop the PostgreSQL 8.3 server
3. Change pg_hba.conf so that local connections are disabled, but
connections to 127.0.0.1 are allowed with md5 authentication
4. Restart the PostgreSQL server
5. Open up a shell and do the following:

pg83@mca-desktop:~$ export PGPASSWORD=mypass
pg83@mca-desktop:~$ psql -h localhost -d postgres -c "SELECT version();"
version
 
---------------------------------------------------------------------------------------------------PostgreSQL 8.3beta2
oni686-pc-linux-gnu, compiled by GCC gcc (GCC)
 
4.0.3 (Ubuntu 4.0.3-1ubuntu5)
(1 row)


So far so good. But now try with a database that doesn't exist:

pg83@mca-desktop:~$ psql -h localhost -d doesntexist -c "SELECT
version();"
Password:
psql: FATAL:  database "doesntexist" does not exist


Hmmmm. So even though PGPASSWORD is set (and the command works if the
database exists within the cluster), if I specify a non-existent
database then I still get prompted for a password.

I've run the same test against PostgreSQL 8.2.5 and the test works in
that I don't get prompted for a password the second time. So the
behaviour has changed between versions, but I wanted to check that it
wasn't a deliberate change before looking deeper.


Many thanks,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk