How to Fix PostgreSQL Error: Role Does Not Exist

The "Role does not exist" error occurs when attempting to connect to PostgreSQL or execute operations using a username (role) that hasn't been created in the database system. In PostgreSQL, users and groups are both represented as roles, and this error indicates the specified role name is not found in the database cluster.

Impact

This error prevents authentication and database access for the specified user. It can cause application startup failures, deployment issues, and service disruptions, particularly when deploying new applications or after database migrations.

Common Causes

1. Role has not been created yet
2. Typo in the username/role name
3. Case sensitivity issues
4. Role was dropped or deleted
5. Connecting to wrong database cluster
6. Role created in different database cluster than expected
7. Peer authentication attempting to use OS username

Troubleshooting and Resolution Steps

1. List all existing roles:

   -- Connect as superuser
   sudo -u postgres psql
   
   -- List all roles
   \du
   
   -- Or use SQL query
   SELECT rolname FROM pg_roles ORDER BY rolname;
   

2. Create the missing role:

   -- Create basic user
   CREATE ROLE myuser WITH LOGIN PASSWORD 'secure_password';
   
   -- Create user with additional privileges
   CREATE ROLE myuser WITH LOGIN PASSWORD 'secure_password'
       CREATEDB
       CREATEROLE
       VALID UNTIL '2025-12-31';
   
   -- Create superuser (use with caution)
   CREATE ROLE admin WITH LOGIN PASSWORD 'secure_password' SUPERUSER;
   

3. Verify role name matches exactly (case-sensitive):

   -- PostgreSQL role names are case-sensitive when quoted
   CREATE ROLE "MyUser" WITH LOGIN PASSWORD 'password';  -- Creates MyUser
   CREATE ROLE myuser WITH LOGIN PASSWORD 'password';    -- Creates myuser
   
   -- Check exact role name
   SELECT rolname FROM pg_roles WHERE rolname LIKE '%user%';
   

4. Grant necessary permissions to the role:

   -- Grant database access
   GRANT CONNECT ON DATABASE mydb TO myuser;
   
   -- Grant schema usage
   GRANT USAGE ON SCHEMA public TO myuser;
   
   -- Grant table permissions
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
   
   -- Grant future table permissions
   ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
   

5. For peer authentication issues, check pg_hba.conf:

   sudo nano /etc/postgresql/15/main/pg_hba.conf
   

   Change from peer to md5 or scram-sha-256:

   # Before (peer authentication uses OS username)
   local   all   all   peer
   
   # After (password authentication)
   local   all   all   scram-sha-256
   

6. Create a role matching your OS username for peer authentication:

   # If using peer authentication, create role matching OS user
   sudo -u postgres createuser -s $USER
   

7. Verify connection string uses correct role name:

   # Check connection parameters
   psql -h localhost -U myuser -d mydb
   
   # Connection string format
   postgresql://myuser:password@localhost:5432/mydb
   

8. Check if role exists but in different database cluster:

   # List all PostgreSQL clusters
   pg_lsclusters
   
   # Connect to specific cluster
   sudo -u postgres psql -p 5432
   

9. Restore role from backup if accidentally deleted:

   -- If you have a SQL dump with role definitions
   psql -f roles_backup.sql
   

Additional Information

• In PostgreSQL, "user" and "role" are synonymous; CREATE USER is an alias for CREATE ROLE WITH LOGIN
• Role names are case-insensitive unless quoted during creation
• The postgres user is created by default during PostgreSQL installation
• Use groups (roles without LOGIN) to manage permissions efficiently
• Regularly audit roles and remove unused ones
• Document role creation procedures for disaster recovery

Frequently Asked Questions

Q: What's the difference between a user and a role in PostgreSQL?
A: In modern PostgreSQL, they are the same. A "user" is simply a "role" with the LOGIN privilege. CREATE USER is equivalent to CREATE ROLE ... WITH LOGIN.

Q: How do I create a role with the same name as my operating system user?
A: Run sudo -u postgres createuser $USER or in SQL: CREATE ROLE your_os_username WITH LOGIN;

Q: Can I rename a role?
A: Yes, use ALTER ROLE old_name RENAME TO new_name; However, this doesn't update ownership of objects or references in applications.

Q: Why does psql work with sudo -u postgres but fail with my username?
A: By default, peer authentication is used for local connections, which requires a PostgreSQL role matching your OS username. Either create a matching role or use password authentication.

Q: How do I check what permissions a role has?
A: Use \du rolename in psql, or query pg_roles and related system catalogs for detailed information.

Q: Can I create multiple roles at once?
A: Not with a single command, but you can script it:

DO $$
BEGIN
  CREATE ROLE user1 WITH LOGIN PASSWORD 'pass1';
  CREATE ROLE user2 WITH LOGIN PASSWORD 'pass2';
  CREATE ROLE user3 WITH LOGIN PASSWORD 'pass3';
END $$;