Fix: FATAL: role "app" does not exist

FATAL: role "app" does not exist is PostgreSQL refusing a login because the cluster it received the connection on has no role with that name. The two explanations are simpler than they feel at 2 a.m.: either the role genuinely was never created on this cluster (fresh server, restored to a new instance, provisioning step skipped), or the connection is landing on a different cluster than the one where the role exists. Establish which one you are dealing with before creating anything.

FATAL: role "app" does not exist

What this error means

Authentication in PostgreSQL starts by matching the presented role name against pg_roles in the target cluster. If there is no match, the server rejects the login with this message before any password check happens. Note what it does not mean: it is not a wrong password (that says password authentication failed) and not a missing database (that says database "..." does not exist). It means this specific cluster, on this host and port, has never heard of the role. Since a host can run several clusters on different ports, and connection strings pass through environment variables, poolers, and DNS, “this cluster” is exactly the thing to verify.

Common causes

• A new or rebuilt server where the provisioning step that creates application roles never ran.
• Connecting to the wrong cluster: wrong port (5432 vs 5433), a local instance shadowing the intended remote, or DNS or a pooler pointing somewhere unexpected.
• psql run without -U, so it defaults to the operating system username, which has no matching role.
• A restore that copied the database but not the roles: pg_dump of one database does not include roles, which are cluster wide and need a globals only pg_dumpall dump.
• Typos or case traps: unquoted identifiers fold to lowercase, so role App created with quotes is not the same as app.
• Environment variables (PGUSER, PGHOST, PGPORT) or a stale .pg_service.conf overriding what you think the connection string says.

How to fix it

STABILIZE first. If production is blocked and the role is genuinely missing, creating it with the correct password and grants restores logins in seconds; the investigation into how it went missing can follow.

1. DIAGNOSE: confirm exactly where the failing connection goes and as whom. Print the effective settings rather than trusting the code.

env | grep -E "^PG(USER|HOST|PORT|DATABASE)"
psql "host=db.internal port=5432 user=app dbname=appdb" -c "SELECT 1;"

2. DIAGNOSE: connect as the superuser to the same host and port and list the roles that actually exist there.

psql -h db.internal -p 5432 -U postgres -d postgres

SELECT rolname, rolcanlogin FROM pg_roles ORDER BY rolname;
SELECT inet_server_addr(), inet_server_port();

If the role is present here, your application is connecting elsewhere: recheck host, port, pooler config, and DNS. If it is absent, move to the fix.

3. FIX the missing role: create it with login, set a strong password, and grant what the application needs.

CREATE ROLE app WITH LOGIN PASSWORD 'use-a-generated-secret';
GRANT CONNECT ON DATABASE appdb TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;

4. FIX a restore that lost the roles: dump the globals from the old cluster and replay them on the new one before restoring databases.

pg_dumpall -h old-db.internal -U postgres --globals-only > globals.sql
psql -h new-db.internal -U postgres -f globals.sql

5. Verify the original failing path now works, using the exact same connection string the application uses.

psql "host=db.internal port=5432 user=app dbname=appdb" -c "SELECT current_user;"

How to prevent it

• Create roles in provisioning code (Terraform, Ansible, migration scripts) so a rebuilt cluster always gets them, never by hand.
• Include a globals only pg_dumpall dump in every backup plan alongside database dumps.
• Pass the role explicitly in every connection string and script; never rely on the OS username default.
• Avoid quoted mixed case role names entirely, so case folding cannot bite.
• Keep one source of truth for connection settings and log the resolved host and port at application startup.
• Test restores onto a fresh cluster periodically, which catches missing globals long before an incident does.

When to call a senior engineer

Call for help when logins fail intermittently as if the role flickers in and out of existence (a classic sign of two clusters or a misrouted pooler), when a restore left you unsure which grants and roles are missing, or when nobody can say with confidence which server the application actually reaches. Erzon engineers can trace the real connection path end to end, rebuild the role and privilege layout cleanly, and put the provisioning in code so a missing role never blocks production again.