PostgreSQL – Cannot Login with Created Users

postgresql

I am very new to postgreSQL, but quite familiar with SQL.

I have recently installed postgreSQL on Windows 10. I ran through the startup tutorials and made a few DBs to mess around with. I can login just fine with the postgres account with the command

psql -U postgres -d DatabaseName

However, I have thus far been unable to create a new user to log in with.

I tried the command

CREATE USER myUser WITH SUPERUSER PASSWORD 'password';

When I do this, it creates the user. I can see this with the \du command:

Role name  |   List of Attributes                      | Member of
postgres   | Superuser, Create Role, Create DB, yada...| {}
myuser     | Superuser                                 | {}

I then try \q and logging in with my new user and get the following:

psql -U myUser -d Database
Password for myUser: password
psql: FATAL: password authentication failed for "myUser"

I have also tried createuser from cmd, this user showed up in the list of users as well, but I was again unable to login as these users.

Thank you for any help you can offer.

Best Answer

It's just a case folding problem. You're trying to connect with myUser but due to case folding rules, you created myuser in lowercase, as the \du output shows. Per documentation:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case.

To create myUser with this exact case, enclose it in double quotes, as in:

CREATE USER "myUser" WITH SUPERUSER PASSWORD 'password';

Then the invocation psql -U myUser ... will work.

Or alternatively, don't recreate the user but always refer to it in lowercase.

Related Question