Understanding the Error
You just tried to connect to your database, but PostgreSQL kicked back a frustrating message: FATAL: role "myuser" does not exist. This isn't a bug in the software. It is a straightforward lookup failure. When you attempt to log in, Postgres checks its internal pg_roles table. If it can't find a record matching the exact string you provided, it shuts the door.
Why is this happening?
- The role is missing: You haven't actually created the user in this specific database cluster yet.
- Case-sensitivity traps: You created
"MyUser"(with quotes), but Postgres defaults to looking formyuser(lowercase) when quotes are missing. - Connection String Errors: Your
.envfile or connection string might have a typo, or it's pointing to the wrong port (e.g., 5433 instead of 5432) where the user doesn't exist. - Default User Assumptions: On many Linux systems, Postgres expects a database role that matches your OS username. If your Linux user is "ubuntu" but you haven't created an "ubuntu" role in Postgres, it will fail.
How to Fix It
1. List Current Roles
First, see who actually has access. You need to log in as the postgres superuser to peek at the user list.
# Access the Postgres prompt
sudo -u postgres psql
# Run the 'display users' command
\du
Look closely at the output. If your desired username isn't in the list, you need to create it.
2. Create the Missing Role
You can fix this quickly using the SQL interface. While still inside the psql prompt, run the following command to create a basic user with login rights:
CREATE ROLE myuser WITH LOGIN PASSWORD 'your_secure_password';
If this user needs to create their own databases—common for development roles—add the CREATEDB attribute:
ALTER ROLE myuser WITH CREATEDB;
3. Handle Case Sensitivity
Postgres is picky about casing. If you must use a mixed-case username like AppUser, you have to wrap it in double quotes during creation and connection. However, life is much easier if you stick to lowercase for all database objects. If you accidentally created a quoted role, you can rename it to lowercase to save yourself future headaches:
ALTER ROLE "MyUser" RENAME TO myuser;
4. Grant Database Access
A role can exist but still be barred from entering a specific database. To grant full ownership of a database (like project_db) to your new user, run:
ALTER DATABASE project_db OWNER TO myuser;
Testing the Connection
Exit the superuser prompt and try connecting as the new user. Use the -h localhost flag to force a TCP connection, which usually prompts for the password you just set.
psql -U myuser -d project_db -h localhost -W
If you see the project_db=> prompt, you’re in.
Pro-Tips for Smooth Setup
Avoid the 'Peer' Authentication Trap
On Linux, you might fix the role only to hit a Peer authentication failed error. This happens because Postgres is trying to match your Linux login to your DB login. To bypass this for local development, find your pg_hba.conf file (often in /etc/postgresql/15/main/) and change the method from peer to scram-sha-256 or md5.
Use Strong Credentials
Don't use "password" or "123456" for your database roles, even in local dev. It's a bad habit that leads to production leaks. I usually generate 24-character strings to keep things secure. If you need a quick way to do this, the Password Generator from ToolCraft is a solid choice. It generates entropy locally in your browser, so your keys never touch their servers.