Fix PostgreSQL "role does not exist" and "database does not exist"

Problem statement

Django cannot connect, and migrate or the first request fails with one of:

django.db.utils.OperationalError: connection to server at "127.0.0.1", port 5432 failed:
FATAL:  role "deploy" does not exist

FATAL:  database "myapp" does not exist

PostgreSQL is running and reachable; it is rejecting the names. Either the role (user) or the database in your connection settings was never created on this server, was created with different spelling or case, or Django is not using the settings you think and falls back to defaults such as your Linux username.

1. List roles and databases: sudo -u postgres psql -c "\du" and sudo -u postgres psql -l.
2. Create what is missing: CREATE ROLE myapp LOGIN PASSWORD '...'; and CREATE DATABASE myapp OWNER myapp;.
3. Check Django's effective settings: python manage.py shell -c "from django.db import connection; print(connection.settings_dict)".
4. If the role equals your Linux username, USER is empty or unset, so libpq used the OS user.
5. In Docker, POSTGRES_USER and POSTGRES_DB only apply when the data volume is first created.

Step-by-step solution

1. See what exists on the server

sudo -u postgres psql -c "\du"
sudo -u postgres psql -l

Compare with what Django uses. Names are case-sensitive when created with quotes: CREATE ROLE "MyApp" creates MyApp, while CREATE ROLE MyApp creates myapp. Use lowercase names and avoid quoting.

2. Print the settings Django actually uses

python manage.py shell -c "from django.db import connection; s=connection.settings_dict; print(s['HOST'], s['PORT'], s['NAME'], s['USER'])"

If USER is empty, libpq connects as the current operating system user, which is why the error names deploy, ubuntu, or www-data even though you never configured that. The same happens with NAME: the default database name equals the user name. The fix is to load the right settings or environment, for example DATABASE_URL in the systemd unit's EnvironmentFile.

3. Create the role and database

sudo -u postgres psql <<'SQL'
CREATE ROLE myapp LOGIN PASSWORD 'change-me';
CREATE DATABASE myapp OWNER myapp ENCODING 'UTF8';
SQL

Making the app role the database owner lets migrations create tables in the public schema on PostgreSQL 15+ without extra grants. Store the password in your secrets, not in settings.py.

4. Docker Compose: environment only applies once

The official image creates POSTGRES_USER and POSTGRES_DB only when it initialises an empty data directory. If you change those variables later, or the volume was created by an earlier run with different values, nothing happens:

docker compose exec db psql -U postgres -c "\du"

Either create the role and database manually as above, or, on a development machine only, remove the volume so it reinitialises (docker compose down -v deletes all data).

5. Check you reached the right server

Two PostgreSQL versions on one host often listen on 5432 and 5433. Managed databases have separate hostnames for primary, replica, and pooler. Confirm:

psql "host=$DB_HOST port=$DB_PORT user=postgres dbname=postgres" -c "SELECT version(), inet_server_port();"

If the role exists on one instance but Django connects to another, fix HOST and PORT.

Verification check:

python manage.py dbshell -c "SELECT current_user, current_database();"
python manage.py migrate --check

The first shows the role and database Django uses; the second confirms the schema is up to date.

Explanation

PostgreSQL authenticates a connection in stages: it looks up the role named in the startup packet, applies pg_hba.conf rules, and then opens the requested database. Missing roles and databases fail before any password check, which is why this error appears even with a correct password. Because libpq fills missing connection parameters from the environment and the OS user, a Django process that did not receive its settings still makes a connection attempt with plausible-looking defaults, producing confusing names in the error.

Edge cases / notes

• Managed services often create a default admin user and database; create a separate app role rather than using the admin account.
• PgBouncer checks its own userlist.txt or auth query; a role missing there gives a similar error from the pooler.
• Restores with pg_restore --no-owner avoid failures when the original owner role does not exist on the target.
• Tests create test_<NAME>; the role needs CREATEDB to run the test suite.

Next steps

• Set up the full connection with Connect Django to PostgreSQL in Production.
• If the role exists but login fails, see Fix Postgres Password Authentication Failed.

FAQ

Why does the error mention my Linux username?

Django's USER setting is empty, so libpq used the operating system user name as the role.

Do I need a superuser for Django?

No. A normal login role that owns the app database is enough and much safer.

Why did POSTGRES_DB not create my database?

The Docker image only applies it on first initialisation of an empty data volume.

Are PostgreSQL role names case-sensitive?

Only when created with double quotes. Unquoted names are folded to lowercase.