2

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>
Kristopher Kerwin
  • 33
  • 1
  • 5
  • 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 Aug 30 '17 at 10:59
  • 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 Aug 30 '17 at 12:52
  • 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 Aug 30 '17 at 15:19
  • @AntonisChristofides: `pg_hba.conf`: https://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 Aug 30 '17 at 23:25
  • 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 Aug 31 '17 at 10:01
  • @AntonisChristofides: Ah, good catch on the error. This error message is now "password authentication failed", not "peer". /etc/hosts updated per your instructions. It fixed the "unable to resolve host dask" message, but not the error. The error is still "password", and not "peer" after this change. Thanks again. – Kristopher Kerwin Sep 01 '17 at 01:18
  • Are you *absolutely certain* you are running only one instance of PostgreSQL in that machine? Is there any docker or virtualbox or other stuff that might be running another one? Could you show the output of `sudo netstat -pln|grep postgres`? – Antonis Christofides Sep 01 '17 at 08:50
  • @AntonisChristofides; Here is the output, although I'm not sure what to make of it: https://pastebin.com/gwB0fmAx. To answer your question: This is a virtual box hosted on Linode. But, there are no children virtual boxes within the virtual box that I'm running. – Kristopher Kerwin Sep 04 '17 at 16:42
  • Have you verified that `POSTGRES_KEY` contains the correct password? After the definition of `DATABASES` in `settings.py` add `from pprint import pprint; with open('/tmp/django.out', 'w') as f: pprint(DATABASES, f)`. Copy the password from the output and paste it to your other means of connecting to the database and verify you can connect. – Antonis Christofides Sep 05 '17 at 09:53
  • Also check the messages PostgreSQL gives you in `/var/log/postgres/postgresql-9.5-main.log` when Django tries to connect (and show us). – Antonis Christofides Sep 05 '17 at 09:55
  • OK. I attempted to make the changes recommended by @bugzap below. While those were ineffective, the symptoms have now changed. The `postgresql-9.5-main.log` file provided this output: https://pastebin.com/jtGvTpXG. I then tried making the changes that you recommended to `settings.py`, and got this output: https://pastebin.com/tmp57T8U. Attempting to run the example that I provided in my original post under **Testing psycopg2 python library**, I get "password authentication failed for user 'dask'". – Kristopher Kerwin Sep 05 '17 at 23:48
  • OK. Then, I reset the password in the database back to the original, and now everything is working. – Kristopher Kerwin Sep 05 '17 at 23:54

2 Answers2

1

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;
BugZap
  • 86
  • 4
  • Sorry @Kristopher Kerwin ... I should have taken a closer look at your settings.py. I didn't see the "HOST" or "PORT" set. `"HOST": "localhost"` and `"PORT": "5432",` should be in there too, at least for defaults. – BugZap Sep 06 '17 at 07:00
0

Are you even able to reach postgres=#? If not then do this:

If you are in ubuntu do that:

  1. Open your terminal and write this command sudo su - postgres and hit enter
  2. Then type psql and hit enter And then you will see this postgres=# Now you can able to do your operations if you want
Nikhil Bhardwaj
  • 562
  • 10
  • 18