django.db.utils.OperationalError: FATAL: role "django" does not exist

Question

I followed this tutorial on Digital Ocean to install PostgreSQL 9.5 on an Ubuntu 16.04 server to use with Django 1.10.

Everything went smoothly, but I can't get my Django app to connect to the database (or so it seems). App and database are on the same server.

Here are some settings, configs and reports:

The error I get:

File "/home/mathieu/web/agencies/lib/python3.5/site-packages/psycopg2/__init__.py", line 164, in connect
conn = _connect(dsn, connection_factory=connection_factory, async=async)
django.db.utils.OperationalError: FATAL:  role "django" does not exist

My Django project's database settings:

DATABASES = {
'sqlite3': {
    'ENGINE': 'django.db.backends.sqlite3',
    'NAME': os.path.join(BASE_DIR, 'db.sqlite3')
},
'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'NAME': 'agencies',
    'USER': 'django',
    'PASSWORD': '<password>',
    'HOST': 'localhost',
    'PORT': '5432',
}}

The hba_file

postgres=# SHOW hba_file;
hba_file
--------------------------------------
/etc/postgresql/9.5/main/pg_hba.conf

Its contents (well, the relevant part anyway):

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

Users and database in psql

postgres=# \du
                               List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
django    |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


postgres=# \l
                              List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
agencies  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
                                                            |  postgres=CTc/postgres+
                                                            |  django=CTc/postgres
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
          |          |          |             |             |  postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
          |          |          |             |             | postgres=CTc/postgres

I followed the exact same steps on a VM (running Linux Mint, I should say) and all went fine and dandy...

I can't for the life of me figure out what's or where things are going wrong.

Answer 1

If you are using Docker, and it's listening to the 5432 port, you should kill other processes that are listening too.

To do this, type this command to see which processes are using port 5432:

$ lsof -i:5432

This will look like this:

COMMAND     PID           USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
com.docke 15178 andre.carvalho   86u  IPv6 0xb128ac87cd34cc69      0t0  TCP *:postgresql (LISTEN)
postgres  16670 andre.carvalho    7u  IPv6 0xb128ac87dc50c569      0t0  TCP localhost:postgresql (LISTEN)
postgres  16670 andre.carvalho    8u  IPv4 0xb128ac87d2378541      0t0  TCP localhost:postgresql (LISTEN)

After this, it's easy: Just kill the other process, with the command:

kill -9 16670

(Note that the process is identified by the PID.)

Answer 2

You may see this error if you have postgres installed both locally (and running) and a docker container both trying to occupy the same port.

If the local instance starts first and is occupying a port that the docker image is also trying to use, docker won't necessarily tell you this.

When you try to run django or other ./manage.py commands that need the database you'll see the same error because the app will not see the database you're looking for.

In this case, you can change the port on your locally installed postgres by stopping the server, clicking Server Settings and changing the port. You'll have to update settings.py on any apps you have depending on that old port.

In my experience, after doing this if you restart the docker db container you won't see this error anymore.

Answer 3

Refer the link click here. It is explained.

Your settings has

'USER': 'django',

But as error says that user doesn't exist , that means you have not created the user.

Just go into interactive session of psql and enter these commands.

CREATE DATABASE agencies;
CREATE USER django WITH PASSWORD 'password';
ALTER ROLE django SET client_encoding TO 'utf8'; 
ALTER ROLE django SET default_transaction_isolation TO 'read committed'; 
ALTER ROLE django SET timezone TO 'Asia/Kolkata';

GRANT ALL PRIVILEGES ON DATABASE agencies TO django;
\q

then in settings.py

'PASSWORD': 'password',

password should not be enclosed inside < >.

Answer 4

I think you forgot to add priveleges to user 'django':

GRANT ALL PRIVILEGES ON DATABASE agencies TO django;