Postgres Password Authentication Failed

Question

I am running a Postgres 12 database on Ubuntu 18.04 on WSL (Windows Subsystem for Linux).

I can connect to the database with sudo -u postgres psql without any issues, and can view and create new databases and users.

I created a new user with CREATE USER myusername WITH PASSWORD 'mypassword'; and psql returns CREATE ROLE.

However, when I try to log in as this user with the password that I set with psql -h localhost -U myusername, I get psql: error: could not connect to server: FATAL: password authentication failed for user "myusername".

I have tried changing the password on this user multiple times with ALTER USER myusername WITH PASSWORD 'newpassword'; and psql returns ALTER ROLE.

A common solution I have found, such as this post, is to modify the pg_hba.conf file.

My pg_hba.conf file is:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5

I have tried modifying both the first and second lines to md5 and ident, and restarted the postgres server each time, to no avail. I have also tried completely uninstalling and reinstalling Postgres, which does not work either.

Is there anything I am missing? Anything else that I can do to try and fix this? Thanks!

This should work: are your sure that you have only one PG instance running on Windows ? Could you check that this PG instance port number is 5432 with show port ? — pifor
– pifor, Commented Jun 2, 2020 at 15:12
@pifor well this was the issue. I had an instance of Postgres running on Windows which I had to stop. Thanks! — Jared Forth
– Jared Forth, Commented Jun 2, 2020 at 15:43

Jared Forth · Accepted Answer · 2020-06-19 20:46:44Z

2

For future reference to those who encounter this problem on Windows Subsystem for Linux, I used the Command Prompt to search the running services and see if a Postgres service was running with sc queryex type=service state=all | find /i "postgres".

This returned:

SERVICE_NAME: postgresql-x64-9.5
DISPLAY_NAME: postgresql-x64-9.5 - PostgreSQL Server 9.5

I then was able to use sc stop with the service name to stop the service:

sc stop postgresql-x64-9.5

This solved the password authentication issue.

edited Jun 19, 2020 at 20:46

answered Jun 2, 2020 at 15:55

Jared Forth

1,6476 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jared Forth Over a year ago

@LeviMurray I'm glad it was helpful!

pifor · Accepted Answer · 2020-06-02 15:53:10Z

1

Doublecheck if there is another PostgreSQL instance running on the default port number.

edited Jun 2, 2020 at 15:53

answered Jun 2, 2020 at 15:45

pifor

7,9722 gold badges12 silver badges18 bronze badges

Collectives™ on Stack Overflow

Postgres Password Authentication Failed

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related