Postgres 14.3 created user authentication failed-CodePudding

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!

CodePudding user response：

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.

CodePudding user response：

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;