Victor's room

Overview & Root Cause Summary: The error psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "<username>" does not exist occurs when attempting to establish a connection to a PostgreSQL instance without specifying a valid, pre-existing database role. By default, the psql client assumes the database user identity matches the active operating system username. If that specific user role has not been provisioned inside PostgreSQL’s cluster catalog, the server rejects the connection handshake immediately.

Understanding the Root Causes

• Default OS Username Assumption: Invoking psql without the -U <username> flag instructs the client to authenticate using your current terminal user account (e.g., ubuntu, developer, or runner). On fresh installations, only the default postgres superuser role exists.
• Linux Peer Authentication Restrictions: On Ubuntu and Debian, local Unix domain socket connections use peer authentication by default, which strictly enforces that the incoming OS user must match the target PostgreSQL role name verbatim.
• Unprovisioned Application Service Accounts: Backend application configs (Django, Rails, Spring Boot, Prisma) referencing custom database users before executing user creation scripts.
• Typographical Errors in Connection Strings: Misspelled usernames in environment variables (such as PGUSER or DATABASE_URL).

Step 1: Quick Fix (Create the Missing Role via createuser CLI)

Use PostgreSQL’s native wrapper utility under the postgres administrative account to create a matching login role for your current OS user.

# 1. Create a superuser role matching your current OS username:
sudo -u postgres createuser --superuser $(whoami)

# 2. Or create a standard interactive role with custom permissions:
sudo -u postgres createuser --interactive
# Enter name of role to add: your_username
# Shall the new role be a superuser? (y/n) y

# 3. Create a default database matching the username (optional but recommended):
sudo -u postgres createdb $(whoami)

# 4. Now connect directly without passing extra flags:
psql

Step 2: Create Role via SQL with Login & Superuser Privileges

Log in as the default superuser and define the role with secure password authentication using standard SQL commands.

# 1. Connect to PostgreSQL console as the postgres administrative user:
sudo -u postgres psql

-- 2. Create the new role with login rights and an encrypted password:
CREATE ROLE myuser WITH LOGIN PASSWORD 'SecurePassword123!';

-- (Optional: Grant superuser or database creation privileges):
ALTER ROLE myuser WITH SUPERUSER CREATEDB;

-- 3. Grant full privileges on the application database:
GRANT ALL PRIVILEGES ON DATABASE my_app_db TO myuser;

-- 4. Exit the shell:
\q

Step 3: Configure pg_hba.conf and User Mapping (pg_ident.conf)

If you want to allow an OS user to log in as a differently named PostgreSQL role under peer authentication, define an explicit identity mapping.

# 1. Open the user mapping file:
sudo nano /etc/postgresql/16/main/pg_ident.conf

# Add a map entry linking your OS user to the database role:
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
local_map       my_os_user              myuser

# 2. Edit pg_hba.conf to utilize the map:
sudo nano /etc/postgresql/16/main/pg_hba.conf

# Update the local socket rule:
# TYPE  DATABASE        USER            ADDRESS                 METHOD  OPTIONS
local   all             all                                     peer    map=local_map

# 3. Reload PostgreSQL configuration without downtime:
sudo systemctl reload postgresql

Verification & Testing Steps

Confirm that the role exists in the cluster catalog and verify authentication across local and TCP sockets.

# 1. List all active roles in PostgreSQL:
sudo -u postgres psql -c "\du"
# Confirm that your target username appears in the "Role name" column with "Cannot login" NOT present.

# 2. Test direct connection using the newly created role:
psql -U myuser -d postgres

# 3. Verify session identity:
# Inside psql:
SELECT current_user, session_user;

Summary Comparison Table