🚨 PostgreSQL Error Guide: 'role "name" does not exist' — Fix

Overview

PostgreSQL raises this when a connection, GRANT, SET ROLE, or object-ownership operation names a role that doesn’t exist in the cluster:

FATAL:  role "app_user" does not exist

At connection time it’s FATAL and the client can’t log in. In SQL it appears as a plain ERROR when you reference a missing role:

ERROR:  role "reporting" does not exist

Roles are cluster-wide (shared across all databases), which is central to diagnosing why one exists in your head but not in the cluster.

Symptoms

• A client or service fails to connect with FATAL: role "..." does not exist.
• psql -U someuser fails immediately at authentication.
• A GRANT/REVOKE, ALTER ... OWNER TO, or SET ROLE fails naming a missing role.
• Restoring a dump fails because it references roles that don’t exist on the target cluster.
• After a failover or clone, apps can’t log in because roles weren’t replicated.

Common Root Causes

• The role was never created on this cluster — a typo, or an assumption it existed.
• Roles are cluster-wide but weren’t provisioned on a new/cloned/failover instance. pg_dump of a single database does NOT include roles; only pg_dumpall (or pg_dumpall --roles-only) does.
• The role was dropped — sometimes as a side effect of a cleanup script, leaving apps that used it broken.
• OS-user / peer authentication mismatch — with peer auth, psql defaults the role to the OS username; if no matching role exists you get this error even though you never typed a role name.
• Case sensitivity / quoting — a role created as "Reporting" (quoted, case-preserved) won’t match reporting.
• Wrong cluster/port — connecting to a different instance that doesn’t have the role.
• A role that owns objects was dropped elsewhere, and operations referencing it now fail.

Diagnostic Workflow

First confirm whether the role actually exists in the cluster you’re connected to:

SELECT rolname, rolcanlogin, rolsuper
FROM pg_roles
ORDER BY rolname;

Or check one role directly:

SELECT 1 FROM pg_roles WHERE rolname = 'app_user';

If this is a connection failure with peer auth, check which OS user maps to which role and what pg_hba.conf requires:

whoami                              # the OS user peer auth will use as the role name
psql -c "SHOW hba_file;"            # locate pg_hba.conf

Look for case/quoting mismatches — a case-preserved role name:

SELECT rolname FROM pg_roles WHERE rolname ILIKE 'reporting';

Confirm you’re on the intended cluster:

SELECT inet_server_addr(), inet_server_port(), current_database();

If a restore is failing, check whether roles were dumped separately:

# roles live at the cluster level — dump them explicitly
pg_dumpall --roles-only > roles.sql

Example Root Cause Analysis

After migrating a database to a new server with pg_dump/pg_restore, every application connection failed with FATAL: role "app_service" does not exist, even though the database and all its tables restored fine.

Checking roles on the new cluster:

SELECT rolname FROM pg_roles WHERE rolname = 'app_service';
-- (0 rows)

The role genuinely wasn’t there. The cause: pg_dump dumps a single database’s objects but not cluster-level roles, which are shared across the whole instance. The migration copied the database but never the roles. The old server still had them:

# on the OLD cluster
pg_dumpall --roles-only > roles.sql
# on the NEW cluster
psql -f roles.sql

Loading roles.sql recreated app_service (and the other roles) with their passwords and memberships, and connections succeeded. The durable fix was to make the migration runbook always run pg_dumpall --roles-only alongside the per-database dump.

Prevention Best Practices

• Migrate/clone clusters with roles included: pair pg_dump with pg_dumpall --roles-only, or use pg_dumpall for a full-cluster dump.
• Provision roles as part of infrastructure-as-code (a bootstrap SQL/migration) so every environment and failover target has them.
• Avoid DROP ROLE on roles that own objects; reassign first with REASSIGN OWNED BY old_role TO new_role; then DROP OWNED BY old_role;.
• For peer/local auth, ensure a database role exists matching each OS user that connects, or use explicit -U with a real role.
• Quote-create roles only when you truly need case sensitivity; otherwise use lowercase names to avoid quoting mismatches.
• Alert on failed logins so a dropped or missing role is caught before it becomes an outage.

Quick Command Reference

-- Does the role exist on THIS cluster?
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'app_user';

-- List all roles
SELECT rolname FROM pg_roles ORDER BY rolname;

-- Case-insensitive search for quoting mismatches
SELECT rolname FROM pg_roles WHERE rolname ILIKE 'reporting';

-- Which cluster/db am I on?
SELECT inet_server_addr(), inet_server_port(), current_database();

-- Safely drop a role that owns objects
REASSIGN OWNED BY old_role TO new_owner;
DROP OWNED BY old_role;
DROP ROLE old_role;

# Dump/restore roles (they are NOT in a single-database pg_dump)
pg_dumpall --roles-only > roles.sql
psql -f roles.sql

Conclusion

Because roles are cluster-wide and pg_dump only captures a single database, the most common cause of role does not exist is a migration or failover that copied the data but not the roles — fixed with pg_dumpall --roles-only. The other frequent cause is peer authentication silently using the OS username as the role. Diagnose by confirming the role in pg_roles on the exact cluster you’re connected to, watch for case/quoting mismatches, and never DROP ROLE without reassigning owned objects first. Bake role provisioning into your infrastructure so every environment starts with the roles it needs.