Postgres password authentication fails

Solution 1:

As shown in the latest edit, the password is valid until 1970, which means it's currently invalid. This explains the error message which is the same as if the password was incorrect.

Reset the validity with:

ALTER USER postgres VALID UNTIL 'infinity';

In a recent question, another user had the same problem with user accounts and PG-9.2:

PostgreSQL - Password authentication fail after adding group roles

So apparently there is a way to unintentionally set a bogus password validity to the Unix epoch (1st Jan, 1970, the minimum possible value for the abstime type). Possibly, there's a bug in PG itself or in some client tool that would create this situation.

EDIT: it turns out to be a pgadmin bug. See https://dba.stackexchange.com/questions/36137/

Solution 2:

I came across this question, and the answers here didn't work for me; i couldn't figure out why i can't login and got the above error.

It turns out that postgresql saves usernames lowercase, but during authentication it uses both upper- and lowercase.

CREATE USER myNewUser WITH PASSWORD 'passWord';

will create a user with the username 'mynewuser' and password 'passWord'.

This means you have to authenticate with 'mynewuser', and not with 'myNewUser'. For a newbie in pgsql like me, this was confusing. I hope it helps others who run into this problem.

Solution 3:

pg_hba.conf entry define login methods by IP addresses. You need to show the relevant portion of pg_hba.conf in order to get proper help.

Change this line:

host    all             all             <my-ip-address>/32        md5

To reflect your local network settings. So, if your IP is 192.168.16.78 (class C) with a mask of 255.255.255.0, then put this:

host    all             all             192.168.16.0/24        md5

Make sure your WINDOWS MACHINE is in that network 192.168.16.0 and try again.