"password authentication failed for user" for any newly created user

Question

I can successfully connect with every user except one. I always get "password authentication failed for user" even after changing the password. This worked before the last time the server restarted. (maybe something corrupted?)

After some time of trying to fix this issue I decided to just create a new user. However, I get the same problem with any new user that I create.

I just executed this:

postgres@server:~$ psql
psql (9.6.13)
Type "help" for help.

postgres=# CREATE USER test WITH PASSWORD 'test';
CREATE ROLE
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# GRANT ALL ON DATABASE test TO test;
GRANT
postgres=# \q
postgres@server:~$ psql -U test -d test -h localhost
Password for user test:
psql: FATAL:  password authentication failed for user "test"
FATAL:  password authentication failed for user "test"

I am pretty sure I did not mistype the password here.

My pg_hba.conf contains the following line:

host    all             all             127.0.0.1/32            md5

I am quite a bit lost as to what I should do to fix this issue.

Answer 1

I just solved the problem. Sometimes you just have to write it down and think a couple of seconds about it ;)

The problem was that SOMETHING (no idea what so far) on my system seems to start an old version of postgres on boot. When I was using psql to create the users it seems I connected through the unix domain socket to my postgres 9.6.

But when I connected using "-h localhost" I connected to the 9.4 instance.

And obviously that old instance has not been in use for a long time and as such only had a few old users.

So the actual error was not an incorrect password, the user was just missing.

Now I have to track down whatever starts the old postgres instance ;)