This is a easy-to-encounter but easy-to-fix gotcha with PostgreSQL. I've created a new database user, but the login fails. The username, password, and database assigned to the user, are all called "lola" but the login with the psql command line tool fails:

dcminter@treacle:/var/lib/postgresql/8.2$ psql lola lola -W
Password for user lola:
psql: FATAL:  Ident authentication failed for user "lola"

The problem is trivial - by default psql tries to use Unix domain sockets with "ident" authentication, which basically means it assumes you're trying to login as one of the users on the operating system that you're connecting from. In order to verify your right to do so, it uses the unix login credentials of the user in question.

Lola is a database user, not an operating system user. I need to tell psql to supply the authentication information directly to the database, so all I need to do is specify the host that I'm connecting to and it will assume I want a TCP socket, rather than a Unix domain socket. Here's how to do that:

dcminter@treacle:~$ psql -h localhost lola lola -W
Password for user lola:
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cypher: DHE-RSA-AES256-SHA, bits: 256)

lola=> \q

Note that I'm still connecting to the database from the database server, I'm just using the -h flag to specify the loopback hostname to force psql to use TCP.

Simple fix for a silly problem, but I know it's not the first time I've got stuck on this so I'm writing it up for perpetuity!

PS No, that's not the real username, password, and database name, and the database isn't publically visible anyway, so don't try... :-)