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   |

Can you add the result of: `sudo -u postgres psql -x -c "select * from pg_user where usename='postgres'"` (to see password validity information) — Daniel Vérité, Jan 28 '13 at 16:00

score 79 · Accepted Answer · edited May 23 '17 at 11:46

79

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/

edited May 23 '17 at 11:46

Community

1
1

answered Jan 28 '13 at 16:13

Daniel Vérité

58,074
15
129
156

1

This "expired at unix time 0" thing happened to me today on PostgreSQL 9.2.3. It was with a new user I had created using PgAdmin. In the past, I had always created users within `psql` or by using the `createuser` script. Perhaps this has something to do with PgAdmin. – Jonathan Fuerth May 10 '13 at 19:17
4

@Jonathan: yes, it turns out to be a pgadmin bug. See http://dba.stackexchange.com/questions/36137 – Daniel Vérité May 11 '13 at 17:10
3

Thanks. Same here - I did not touch anything ... lost 3 hours of my life. – Jörg Sep 02 '14 at 15:21

score 11 · Answer 2 · answered Dec 29 '16 at 08:59

11

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.

answered Dec 29 '16 at 08:59

Marcovannoord

323
3
10

1

To prevent possible confusion: what i meant with the part "during authentication it uses both upper- and lowercase", i meant that during creation of an user, the username gets converted to lowercase, but it doesn't during authentication, so authentication is case-sensitive. – Marcovannoord Apr 05 '17 at 18:42
I've been banging my head against this for about an hour! Thanks! – iLoveTux Apr 16 '19 at 17:38

Pablo Santa Cruz · Answer 3 · 2013-01-28T15:10:18.877

10

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.

edited Jan 28 '13 at 15:10

answered Jan 28 '13 at 15:00

Pablo Santa Cruz

176,835
32
241
292

1

I have added the pg_hba.conf file. Which is pretty much default, with the addition of my ip address. – milosa Jan 28 '13 at 15:05
Oops. I put `192.168.16.0/0` when it should have been `192.168.16.0/24`. Just edited the answer to correct that. – Pablo Santa Cruz Jan 28 '13 at 15:13
1

I should have added that my server is not in a local network. Also, the problem occurs also when I use psql on the same machine as where Postgresql is running. – milosa Jan 28 '13 at 15:50

score 1 · Answer 4 · answered Apr 24 '19 at 08:15

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';

score 0 · Answer 5 · answered Feb 25 '22 at 17:17

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.

Postgres password authentication fails

5 Answers5

Linked