psql: FATAL: password authentication failed for user windows 8

Question

I installed postgresql on windows and while the installation it asks to make a user for the account.This made a new windows user in my computer called postgres, I have created a password for it as well.

Now I want to run psql on windows command line, it asks for a password (without mentioning the user) and always gives me back the error: psql: FATAL: password authentication failed for user "Ash". Even though I have put my accounts password many times.

using pgadmin I changed the user "postgres" to "Ash" but I have yet to remake the password. I followed the steps here: I forgot the password I entered during postgres installation (I rather types host all 127.0.0.1/32 trust because I am on windows), but when running psql again so that I can change the password I get the error: psql FATAL:could not load pg_hba.conf. All together.

Why can it not load? All I did was add an extra authentication option.

Also, is the windows user separated from a postresql user or are they the same(depend on each other)?

Edit:

As you can see, it did not give me the option to choose if Aisha should be a superuser or not. or the other options for that matter.

I also used pgadmin||| to create a new user but the same error pops up:

The user does not exist so why does it do this?

Dit

The USER field is missing in the mentioned entry in pg_hba.conf. It's host all all 127.0.0.1/32 trust for "all databases, all users" – Toxicology

Turns out there is a PostgreSQL bug. #14565144 – Persse

Answer 1

createuser is a command-line utility. Use CREATE USER username WITH PASSWORD 'fred'; or similar at the SQL level in psql. Note the semicolon.

What you're doing in your screenshot is starting to write an SQL command beginning with createuser, but never sending it to the server to run because there's no semicolon terminator. So you never get the error that would tell you it doesn't make sense to do that.

Chalcopyrite

Answer 2

The user on your machine has nothing to do with the user on PostgreSQL. The installer just creates an account and a PostgreSQL role with the same name and password (which in my mind is a bad idea), but they're not related in any way. The Windows user is used to run the server, the PostgreSQL role is used inside the database.

So you should first access the server with the user postgres and then create a user for yourself. Don't change the username inside the server, or the server's running user! Just create a new username and grant it the permissions you need.

You can use psql -U postgres to connect to the server and it'll ask for the password.

Check the permissions for pg_hba.conf, the postgres user must have permissions for it. If you only edited it as an admin, it should be ok, but if you took permissions or anything else, it may mess it up.

Consciencestricken

Hello @SamiKuhmonen ,I used psql -U postgres to login in, however it did not ask me for a password although all the methods are md5 and postgres user actually does have a password. I edited my question for you to see what happened. – Dit

Answer 3

I was having the same error. I get to solve it by locating the "pg_hba.conf" file. You can use this navigation:

C:\Program Files\PostgreSQL\14\data\pg_hba.conf

Then inside the file, changed all the methods "scram-sha-256" to trust and problem was solved.

Metopic

Answer 4

I had exactly the same problem and this line solved it;

createuser --createdb -U postgres --login -P 'your_new_user'

@sami-kuhmonen was right, but his approach of solving the problem did not work for me.

Evoy

Answer 5

This can also happen if the encryption for stored passwords does not match the encryption for connections as defined in postgresql.conf ang pg_hba.conf

For example, if postgresql.conf has:

password_encryption = md5

but pg_hba.conf has:

host   all             all             10.0.0.110/24           scram-sha-256

Then all connections on the 10.0.0.110 interface will have the 'FATAL: password authentication failed error'.

Remember that if you change either of these files you will need to restart postgresql and if you change the password encryption in postgresql.conf you will need to change the passwords for all of the users.

Hope this helps some others with this problems.

Furry