Postgres password authentication fails
PostgresqlPostgresql Problem Overview
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 |
Postgresql Solutions
Solution 1 - Postgresql
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:
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 - Postgresql
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 - Postgresql
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.
Solution 4 - Postgresql
Assuming, that you have root access on the box you can do:
sudo -u postgres psql
If that fails with a database "postgres" does not exists this block.
sudo -u postgres psql template1
Then sudo nano /etc/postgresql/11/main/pg_hba.conf file
local all postgres ident
For newer versions of PostgreSQL ident actually might be peer.
Inside the psql shell you can give the DB user postgres a password:
ALTER USER postgres PASSWORD 'newPassword';
Solution 5 - Postgresql
I have the same problem on a windows machine
Actually, I'm getting the same error for another user but the problem was I have Postgres database installed on my machine and for this specific project that I'm working on it, I'm trying to use Postgres image on docker so when I want to run the project I get this error.
Finally, I find that the problem is that the Postgres service is up and running so first I have to terminate this on services.msc
and then run the project so it uses the Postgres image.