Django & psycopg2: FATAL: password authentication failed for user

Question

I'm having trouble connecting to my postgres database from my django site. Before beginning, I have read and attempted the instructions provided at each of these sites:

Any assistance you are able to provide with the problem described below would be most-helpful in completing this site for a non-profit. Thank you in advance.

Traceback

Traceback is too large for this post. See: https://pastebin.com/fX7NY36M.

uname

kris@dask:~$ uname -a
Linux dask 4.9.36-x86_64-linode85 #1 SMP Thu Jul 6 15:31:23 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

pg_hba.conf

kris@dask:~$ sudo cat /etc/postgresql/9.5/main/pg_hba.conf
...

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# 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

wsgi.py

kris@dask:/home/dask/www/code/daskretreats_org$ cat wsgi.py
"""
WSGI config for daskretreats_org project.

It exposes the WSGI callable as a module-level variable named ``application``.

For more information on this file, see
https://docs.djangoproject.com/en/1.10/howto/deployment/wsgi/
"""

import os

from django.core.wsgi import get_wsgi_application
from mezzanine.utils.conf import real_project_name

os.environ.setdefault("DJANGO_SETTINGS_MODULE",
                      "%s.settings" % real_project_name("daskretreats_org"))

application = get_wsgi_application()

settings.py

settings.py is too large for this post. See: https://pastebin.com/Dynq7EvT.

Testing psycopg2 python library

(venv) dask@dask:~/www/secrets$ python
Python 2.7.12 (default, Nov 19 2016, 06:48:10) 
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> f = open("postgres_key")
>>> passwd = f.read().strip()
>>> conn = psycopg2.connect("dbname=daskretreats_org user=dask password=%s" % passwd)
>>> conn.dsn
'password=xxx user=dask dbname=daskretreats_org'

Apache Site Configuration

kris@dask:~$ cat /etc/apache2/sites-enabled/daskretreats_org.conf 
<VirtualHost *:80>
        ServerName daskretreats.org
        Alias /static /home/dask/www/html/static
        <Directory /home/dask/www/html/static>
                Require all granted
        </Directory>

        <Directory /home/dask/www/code/daskretreats_org>
                <Files wsgi.py>
                        Require all granted
                </Files>
        </Directory>

        WSGIDaemonProcess daskretreats_org python-home=/home/dask/www/venv python-path=/home/dask/www/code
        WSGIProcessGroup daskretreats_org
        WSGIScriptAlias / /home/dask/www/code/daskretreats_org/wsgi.py
</VirtualHost>

You need to decide as which PostgreSQL user Django will connect to PostgreSQL and set the username and password in DATABASES. You also need to create that user in PostgreSQL using SQL's CREATE USER or PostgreSQL's createuser command-line program. — Antonis Christofides
Thanks for the reply, Antonis. That's been done already. See settings.py, lines 153-168. See also where I am in a python interactive terminal and am able to connect with just the raw psycopg2 library (so the database user "dask" is created and with the right password). Thanks. — Kristopher Kerwin
1) Please change HOST to 'localhost' instead of the empty string and check if it works; 2) Have you restarted PostgreSQL after changing pg_hba.conf? 3) Could you paste the entire pg_hba.conf to pastebin? — Antonis Christofides
@AntonisChristofides: pg_hba.conf: pastebin.com/eFyQaGQp. Changed HOST to localhost in settings.py. Executed sudo service postgresql reload and sudo service apache2 reload. Webpage test returned same error in browser. Traceback was also the same. — Kristopher Kerwin
I don't like the "unable to resolve host dask". It's probably not the reason for the problem, but still I don't like it. Add 127.0.1.1 dask to your /etc/hosts. Next, please clarify: the message in the traceback is "peer authentication failed". When you change "HOST" to 'localhost' is it still the same? Or is it "password authentication failed"? — Antonis Christofides

BugZap BugZap · Accepted Answer · 2017-09-05T22:46:14

Have you set the roles for the user and granted the user privileges to the database?

postgres=#
ALTER ROLE mydbuser SET client_encoding TO 'utf8';
ALTER ROLE mydbuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE mydbuser SET timezone TO 'UTC';

GRANT ALL PRIVILEGES ON DATABASE mydb TO mydbuser;

Django & psycopg2: FATAL: password authentication failed for user

2 Answers