I don't administer postgresql very often. Normally, I use it as a back-end to something, so I often forget how to do some of the most basic things, like ... er... login.

Consider a recent experience. I tried to login:

psql mydb

Pg responded:

FATAL:  role "paul" does not exist

So, I try to create 'paul' using createuser, but I can't until, I realise that I can create users if I su as user postgres. Again I try to log in:

FATAL:  Ident authentication failed for user "paul"

This is really annoying, but it's supposed to be an aid to security. Postgres is installed in such a way that you can only log in with the same user name as your Linux account. A line in pg_hba.conf does this:

local   all         all                               ident sameuser

One way to fix the problem is to amend that line. The other is not to connect through a Unix socket rather through an IP address. So, whereas this will fail to login to the database mydb:

psql -U dbuser mydb

This will succeed:

psql -U dbuser -h 127.0.0.1 mydb

Save/Share: Stumble It!  Subscribe Share on Facebook Digg it Reddit Delicious