Getting authentication failed error with postgresql from command line

Question

When i try to login from command line like this

psql -h dbserver -U testuser test

then i get this error

psql: FATAL:  Ident authentication failed for user "testuser"

This is my

nano -w /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          password
# IPv6 local connections:
host    all         all         ::1/128               ident
host    all     all     192.168.0.1/24  password

Answer 1

You can't specify a non-default user with -U in psql when using ident authentication. You won't be running psql as that user, so auth will fail.

You must either use sudo -u testuser psql test or change pg_hba.conf so that authentication (at least for testuser on the db test) uses md5 password authentication, and set a password for testuser with ALTER USER ... PASSWORD if they don't have one already.

Answer 2

I saw the following errors in /var/lib/pgsql/data/pg_log/postgresql-Wed.log:

LOG: provided username (DB_USER) and authenticated username (SHELL_USER) don't match FATAL: Ident authentication failed for user "DB_USER"

The solution in my case was to change all the authentication methods in /var/lib/pgsql/data/pg_hba.conf to md5

Answer 3

With abovementioned, there can be two reasons for this issue - testuser not existing - wrong password or authentication failure for the user.

Here is what I did to resolve this in Windows:

1. Check via pgAdmin: Go to your programs and search for pgAdmin and run. Typically this will be running at http://127.0.0.1:51865/browser/

Check for the Login/Group Roles tab under Server "PostgreSQL". Check if the user is existing. You can also do this by logging to "postgres" user via command "psql -U postgres" and running command "\du"

If user testuser does not exist, create it.

2. Change the authentication methods in pg_hba.conf to md5. Alternatively,
   change the password for the user with the command: ALTER USER ... PASSWORD.