Postgres 14.3 created user authentication failed

Question

I am new to this SQL stuff and I recently installed Postgres 14.3 on my windows machine as part of an online learning requirement. I created a database and a user to connect to the database in the following lines from the shell:

postgres=# create database staff;
postgres=# create user Naruto with encrypted password 'secret';
postgres=# grant all privileges on database staff to Naruto;
postgres=# \c staff Naruto;

password for user Naruto:

After inputting the password I get an error message like this

connection to server at "local host" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "Naruto" Previous connection kept

Whereas the video description from which I am taking tutorials didn't ask for a password prompt but it connected to the database straight up with the designated user.

I have tried numerous suggestions on stack overflow but still, no breakthrough in any way. I'd appreciate any hint because I haven't recorded any progress with my learning recently. Thanks!

jjanes · Accepted Answer · 2022-06-08 16:36:44Z

1

The user you created is named "naruto", not "Naruto", because identifiers are case-folded when not inside double quotes. In the \c, however, it is not case folded because at that point is not an identifier, it is more like a command line argument.

Depending on the contents of pg_hba.conf, PostgreSQL might not tell you when you try to login as a nonexistent user. Instead it goes through the motions of authentication, even though authentication is doomed to fail. This is so that an attacker cannot determine which users exist by trying a bunch and looking at the error messages. The real reason for failure is published to the db server's log file, so if you had looked there you should have seen role "Naruto" does not exist.

If you want the user to have a capital letter, put double quotes around the name when you do the CREATE. Alternatively given that you already created the user without the cap, connect to it using the lower-case spelling. And either way, look in the servers log file when you run into problems.

answered Jun 8, 2022 at 16:36

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

4 Comments

Nas Over a year ago

The issue has been fixed. Thank you so much @jjanes for the heads up !!! Please, how can I locate the db server's log file?

Belayer Over a year ago

If you can and do not mind post yout solution. This helps future questioners facing the same issue.

jjanes Over a year ago

Logging is very configurable, but the default for me when using the EDB installer for Windows is in the directory "c:/Program Files/PostgreSQL/14/data/log/"

Nas Over a year ago

Thank you so much once again! I appreciate you taking the time to get this problem solved.

Nas · Accepted Answer · 2022-06-08 18:06:23Z

0

I hope this might help someone in the future. All I had to do was fix the caps for the user I initially created as 'Naruto' and it got executed smoothly.

postgres=# create database staff; postgres=# create user naruto with encrypted password 'secret'; postgres=# grant all privileges on database staff to naruto; postgres=# \c staff naruto;

answered Jun 8, 2022 at 18:06

Nas

12 bronze badges

Collectives™ on Stack Overflow

Postgres 14.3 created user authentication failed

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related