[postgresql] Postgres password authentication fails

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   |

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.

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

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.