Postgres password authentication fails

Question

I tried to login with the postgres user from my windows machine to my server with Pgadmin.

But it keeps giving me this error:

psql: FATAL:  password authentication failed for user "postgres" 

So then I tried to login from the command line with psql, which gave me the same error. I then resetted the password to 'test' using psql, after putting the local entry in pg_hba.conf to trust. And then I placed the entry back to md5, and tried to login with the password 'test'.

In psql I have used these commands:

ALTER ROLE postgres WITH PASSWORD 'test'; ALTER ROLE postgres PASSWORD 'test'; ALTER USER postgres WITH PASSWORD 'test'; ALTER USER postgres PASSWORD 'test'; 

And this special psql command

\password 

Every time, I returned the pg_hba.conf local entry to md5, and tried to login with psql:

psql -U postgres 

And then I am asked for a password. After entering 'test', psql gives me the same error as I mentioned earlier.

And of course, I restarted postgresql after each and every change to the pg_hba file. And I'm using psql with 'su postgres'.

So, even though I am able to change the password the usual way, it isn't accepted as the password.

I hope somebody is able to help me with this.

Some info:

Postgresql 9.1 Ubuntu 12.04

Pg_hba file (as requested)

local   all             postgres                                md5  local   all             all                                     md5 # IPv4 local connections: host    all             all             127.0.0.1/32            md5 # IPv6 local connections: host    all             all             ::1/128                 md5  host    all             all             <my-ip-address>/32        md5 

When I wanted to modify the password, I changed the top md5 to trust. I want to mention that this configuration has worked without problems before.

The results of

sudo -u postgres psql -x -c "select * from pg_user where usename='postgres'" 

Are:

usename     | postgres usesysid    | 10 usecreatedb | t usesuper    | t usecatupd   | t userepl     | t passwd      | ******** valuntil    | 1970-01-01 00:00:00+01 useconfig   | 

Answer 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/

Answer 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.