HELP! I'm trying to connect from the PSQL command line to my PostgreSQL database on Windows, and I keep getting this error message: psql: FATAL: password authentication failed for user "..." even when I seem to have all the right flags including the -W one that's supposed to force it to ask me for a password!
This one was annoying for me, but not fundamentally problematic since I could always connect via pgAdmin III tool. However, I got stuck debugging a problem that appeared to be emanating from pgAdmin itself (more on that later) and so I had to resort to my non-working command line tool. Arse.
Fortunately this time when I did a web search for it, Robert Kinberg had found the answer, making him my personal hero for this week at least, and the cause is amusing in a geeky sort of way:
If you have a dev directory in the root of the drive from which you run PSQL, it spots this and looks for /dev/tty in order to make sure (in a strictly Unix sort of way) that it's definitely rading the password from a tty. D'oh! You end up with a file called /dev/tty containing the prompt, and on subsequent runs it tries to use that as the password! That sort of expectation confounding bug can be a pain to debug, so well done to Robert, and thanks to Magnus Hagander for writing it up. Hopefully this Paperstack write up will help raise the visibility of that problem for sufferers (oh and it sounds like they've fixed it in versions 8.1.4 and 8.0.8 of PostgreSQL.
Here's Magnus's write up: http://people.planetpostgresql.org/mha/index.php?/archives/84-At-last-the-password-bug-is-found.html
And here's my before and after attempt to connect to a database:
C:\>psql blog blog psql: FATAL: password authentication failed for user "blog" C:\>psql blog blog -W psql: FATAL: password authentication failed for user "blog" (remove c:\dev) C:\>psql blog blog -W Password: Welcome to psql 8.0.4, the PostgreSQL interactive terminal. etc.
Last updated: 23rd May 2006, by dave@paperstack.com while trying to debug something completely different.