SSL OperationalError / ProgrammingError configuring Postgres on Heroku

Using Django
1

I’m trying to setup PostreSQL for Django deployed on Heroku. I am following along with the official Heroku doc titled Concurrency and Database Connections in Django | Heroku Dev Center

I am trying to configure the pgbouncer buildpack.

When I pushed my changes to Heroku, I received: “OperationalError at /, server does not support SSL, but SSL was required”. So I Google: ‘heroku ssl django operationalerror pgbouncer’ which turns up a known issue described in an Issue on the official Heroku GitHub repo titled: “django.db.utils.OperationalError: server does not support SSL, but SSL was required #118”

That is my issue precisely. A number of other users chimed in with a solution that seems to resolve the issue for all of them. The solution is to add:

del DATABASES['default']['OPTIONS']['sslmode']

after:

django_heroku.settings(locals())

When I made that change, that eliminated the SSL traceback successfully (Hooray!) but introduces a new problem that no one else on GitHub seems to report. Here is my new traceback:

ProgrammingError at /
column accounts_authtoggle.enable_protection does not exist
LINE 1: SELECT “accounts_authtoggle”.“id”, “accounts_authtoggle”."en…
Request Method:
GET
Request URL:
https://tarot-juicer-next-iter.herokuapp.com/
Django Version:
2.2.13
Exception Type:
ProgrammingError
Exception Value:
column accounts_authtoggle.enable_protection does not exist
LINE 1: SELECT “accounts_authtoggle”.“id”, “accounts_authtoggle”."en…
^
Exception Location:
/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py in _execute, line 84
Python Executable:
/app/.heroku/python/bin/python
Python Version:
3.6.12
Python Path:
[’/app/.heroku/python/bin’,
‘/app’,
‘/app/.heroku/python/lib/python36.zip’,
‘/app/.heroku/python/lib/python3.6’,
‘/app/.heroku/python/lib/python3.6/lib-dynload’,
‘/app/.heroku/python/lib/python3.6/site-packages’]
Server time:
Fri, 5 Feb 2021 17:58:56 +0000

Logged into my Heroku shell, when I attempt to run $ python manage.py makemigrations, it points to a key error at the line I just added inside my settings.py as you can see in the traceback here:

$ python manage.py makemigrations
Traceback (most recent call last):
File “manage.py”, line 21, in
main()
File “manage.py”, line 17, in main
execute_from_command_line(sys.argv)
File “/app/.heroku/python/lib/python3.6/site-packages/django/core/management/init.py”, line 381, in execute_from_command_line
utility.execute()
File “/app/.heroku/python/lib/python3.6/site-packages/django/core/management/init.py”, line 325, in execute
settings.INSTALLED_APPS
File “/app/.heroku/python/lib/python3.6/site-packages/django/conf/init.py”, line 79, in getattr
self._setup(name)
File “/app/.heroku/python/lib/python3.6/site-packages/django/conf/init.py”, line 66, in _setup
self._wrapped = Settings(settings_module)
File “/app/.heroku/python/lib/python3.6/site-packages/django/conf/init.py”, line 157, in init
mod = importlib.import_module(self.SETTINGS_MODULE)
File “/app/.heroku/python/lib/python3.6/importlib/init.py”, line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File “”, line 994, in _gcd_import
File “”, line 971, in _find_and_load
File “”, line 955, in _find_and_load_unlocked
File “”, line 665, in _load_unlocked
File “”, line 678, in exec_module
File “”, line 219, in _call_with_frames_removed
File “/app/tarot_juicer/settings.py”, line 149, in
del DATABASES[‘default’][‘OPTIONS’][‘sslmode’]
KeyError: ‘OPTIONS’

Here are the relevant lines in my settings.py:

DATABASES = {
   'default': {
       'ENGINE': 'django.db.backends.sqlite3',
       'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
   },
}
 
# HEROKU - replace / update default database with heroku postgresql
# db_from_env = dj_database_url.config(conn_max_age=600, ssl_require=True)
DATABASES['default'] = dj_database_url.config(conn_max_age=600, ssl_require=True)

My full settings.py can be found here.

The contents of my Procfile is a single line which reads: web: bin/start-pgbouncer-stunnel gunicorn tarot_juicer.wsgi

I’m running Python 3.6.12 and Django 2.2.

edit: Moved tracebacks into their own pastebins
edit: Moved tracebacks back into the forum thread and removed pastebin links
edit: Changed traceback wrapper formatting from syntax highlighting to general quotes, trying to make it a little bit more readable

2

In the future, I would suggest that you post any tracebacks and error messages with your post here. It’s the only way that someone doing a search for that specific message is going to find it, and the solution, on this forum. (Also, and speaking only for myself, I find it difficult to bounce between different browser tabs and keep straight what tab goes with what section of text in the question. I can’t follow the narrative of what you’re trying to explain as the issue.)

3

Thanks, @KenWhitesell for the suggestion. I have since moved all the tracebacks back into this thread and removed the pastebin links.

4

Since I don’t know how familiar you may or may not be with Python and Django, I need to ask this question - do you know and understand what this statement is trying to do?

What do you think this error message is telling you relative to the statement above?

(hint) What data type is the DATABASES variable?
Then, what data type is DATABASES[‘default’]?
What is the general cause of a KeyError?

5

A typical (general) Postgres configuration in a Django project’s settings appears this way:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'polls_official',
        'USER': 'paul',
        'PASSWORD': 'asdf1234',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

(This snippet is from a different Django project which I am only referring to in order to better explain how the DATABASES dictionary setting works in general.)

As you can see in that snipped, ‘default’ is the first key which refers to six further sub-keys, each with a different value that specifies either the Postgres user name, password, or other data points.

The purpose of del DATABASES['default']['OPTIONS']['sslmode'] is to remove the sslmode ‘option’ (key-value pair) endpoint.

del is a reserved keyword which removes an object. With del DATABASES['default']['OPTIONS']['sslmode'] this particular Python line is supposed to delete the sslmode key which is three levels deep embedded within my DATABASES dictionary setting.

In the project that I am currently troubleshooting in this thread, based on the guide I found elsewhere online, the Postgres configuration should be abstracted or delegated out to the config method from the dj_database_url module which I installed via pip and imported at the top of my settings.py.

I’m encountering a KeyError. A KeyError indicates that the key doesn’t exist. If this is true - - if the ‘sslmode’ key doesn’t exist in my configuration (which I didn’t manually declare but I expect to be initiated by dj_database_url.config() line - - then why am I receiving this KeyError when other users (on the GitHub issue thread I sourced this solution from) are not receiving a KeyError. It works for them. I am not sure why it doesn’t work for me. I’m not sure where my configuration is different from theirs.

dict()

dict() . Although this key being sliced is a string, ‘default’

See above.

Thanks for your patience helping me work through this KeyError, KenWhitesell.

6

You are spot-on with your analysis.

If you look at the source for the .config() method, you can follow the logic to see that the sslmode option is set if the ssl_require parameter is set to True, which you have in your config statement.
(BTW, this appears to be the only purpose of that parameter - if you don’t want that option set, you could probably take that parameter out of your config call and not worry about the del statement.)

However, that setting also isn’t going to do anything if the environment isn’t set correctly. My guess is that your environment variable isn’t set for dj_database_url to work at all, and so you’re trying to delete a key from a dict that doesn’t exist.

If you want to verify this, add the print statement print(DATABASES) after your call to .config() to verify that your dict is being created correctly. If you have a valid databases dict, then I’m wrong and the problem is somewhere other than what it appears to be to me. If I’m right, and the print statement just shows {}, then you still don’t have your environment variable defined correctly.

7

Thank you, KenWhitesell.

Using print statements as you describe to check the contents of DATABASES is a great idea. I added print(DATABASES) before and after calling .config(). This is the output:

{'default': {'ENGINE': 'django.db.backends.sqlite3', 'NAME': '/home/<user>/dev/projects/python/2018-and-2020/tarot_juicer/db.sqlite3'}}
{'default': {}}

You were precisely correct. The initialization of db.sqlite3 works. But then when .config() attempts to create Postgres, the dictionary is empty. The Heroku devcenter guide I was using (here it is again) doesn’t say anything about other environmental variables.

What might be my next steps in terms of troubleshooting the environmental variables in my settings.py?

Edit: Here is my settings.py tagged on my GItHub repo: https://github.com/enoren5/tarot_juicer/blob/84e98d77741367200d9a7a02878019ee896e732b/tarot_juicer/settings.py

8

When you’re working in your local dev environment, Heroku and all the tutorials and documentation associated with it have no relevance to your local dev environment at all.

As mentioned in your other thread:
See the referenced docs for dj_database_url for the environment requirement (DATABASE_URL) for using the .config method. Verify that that settings has been made and is correct for your local dev environment.

9

Ah OK. Thank you for clarifying. So what you are saying is that it’s almost expected that dj_database_url’s .config() method won’t work locally. Is it possible that it could setup correctly as is, but it will only work remotely on Heroku?

10

No, I’m not saying that at all. The dj_database_url package is not part of Heroku and is not tied to it in any way. It works 100% fantastic in any environment in which you choose to use it. In fact, its purpose is to more easily isolate platform-related configuration information from your settings file, making it easier to deploy your app on different platforms without having to change it.

11

I’m back.

I have a solution! I hired a freelancer who resolved to changing lines 91-105 in my settings.py which now read:

DATABASES = {}

DB_URL = str(os.getenv('DATABASE_URL'))

if DB_URL != 'None':
    DATABASES = {'default': dj_database_url.config(env="DATABASE_URL", default=DB_URL, conn_max_age=600)}
else:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        },
    }
print(DB_URL)
print(DATABASES)

Also, line 149 now reads:

django_heroku.settings(locals(), databases=False)

That works. My project is deployed, including Postgres. I am able to enter data in the AdminDashboard and Django is serving web pages to visitors. But I don’t completely understand why.

I will explain what I do understand with the hope that someone will reply to verify my understanding and fill in my gaps.

At line 91, DATABASES={} initiates an empty dictionary and is assigned to a variable called, DATABASES

At line 93, a new variable called DB_URL is assigned to the string created by the .getenv method sourced from the operating system module which extracts the 'DATABASE_URL' environment variable. If the Django project is deployed in the cloud, the contents of the DATABASE_URL is going to be in this format: postgres://USER:PASSWORD@HOST:PORT/NAME. In this case, DB_URL will not be ‘None’ (it contains the Heroku (aws) Postgres db credentials) and the consequent is that the DATABASES hash table will be established and expanded using the helpful automated dj_database_url configuration tool.

However if the project is running locally, then DB_URL will be an empty string containing: ‘None’. And so Django will then know to fall back to the db.sqlite3 configuration.

The print statements help provide clarity and certainty in my Django shell confirming which database is being triggered.

The main gap in my understanding remains how dj_database_url invokes or refers to DATABASE_URL. When I Google ‘DATABASE_URL django’ I encounter a tutorial titled, “Using A DATABASE_URL from the environment” which says:

There is a module, dj_database_url which automatically extracts the DATABASE_URL environment variable to a Python dictionary appropriate for injecting the database settings in Django.

A sample .env file for a Django project deployed to Heroku includes variables such as:

DEBUG=on
SECRET_KEY=your-secret-key
DATABASE_URL=psql://urser:un-githubbedpassword@127.0.0.1:8458/database
SQLITE_URL=sqlite:///my-local-sqlite.db
CACHE_URL=memcache://127.0.0.1:11211,127.0.0.1:11212,127.0.0.1:11213
REDIS_URL=rediscache://127.0.0.1:6379/1?client_class=django_redis.client.DefaultClient&password=ungithubbed-secret

As you can see, DATABASE_URL has a place there. What I understand about environment variables like these is that they are unique to every Python project and get cumbersome to handle them individually, especially when they are dynamic and are constantly changing. By handling environment variables with tools (methods and functions), it makes Django platform agnostic, meaning that it can run seamlessly across different locations (for example, a local dev server configuration is different to remotely in the cloud in production on Heroku). I get that.

When I navigate to the official Django documentation and search for ‘DATABASE_URL’ the results that appear are false positives, meaning that there a links to recent update release notes, but when you click on the pages, there is no instance of ‘DATABASE_URL’ anywhere. https://docs.djangoproject.com/en/3.1/search/?q=DATABASE_URL&release=3.1&page=2

I guess what confuses me at this point in my research is why the first and second parameters for the dj_database_url.config invocation (env and default) are different when they refer to the same thing. Here it is again:

dj_database_url.config(env="DATABASE_URL", default=DB_URL, conn_max_age=600)}

Why is env="DATABASE_URL" and default=DB_URL when they refer to the same thing (which is: postgres://USER:PASSWORD@HOST:PORT/NAME)?

Another gap in my understanding is with line 149. When I Google ‘django_heroku.settings’, the first link refers to the Heroku doc: Configuring Django Apps for Heroku | Heroku Dev Center

which doesn’t provide a description of what exactly django_heroku.settings does or why it works. All it says is that it is required. The second Google search result for ‘django_heroku.settings’ is a read-only archived GitHub repo. The PyPi.org entry for the project is even older - - it was last updated March 2018: django-heroku · PyPI

If django_heroku.settings is no longer maintained then should I even be using it?

Care to jump in, @KenWhitesell ?

12

This is redundant and makes no sense to me. I can see no value in doing it this way. If you want an explanation, I’d go back to the person who gave it to you and ask them. (That’s my weaseling way of admitting that there may be a reason for doing this - I just can’t see it.)

Yep, DATABASE_URL is not a Django configuration or settings variable. Its usage has nothing to do with core Django. It’s only named that because the third party package chose that name. You could actually use any environment variable you want by overriding the default.

Actually, it’s not that hard to understand. From the pypi page for it, you can click on the link for its homepage on github. You could then click-through to the source code and read the source for the .settings method. It’s not that hard to follow.

I don’t use heroku - I don’t know if any of its functionality helps or not. However, I would work under the general opinion that if everything I’m trying to do works without it, I would probably not bother with it at that point.

13

Thank your for clarifying.

You are right that the settings() function is well documented and easy to follow. Thank you for sharing the link.

I will reach out to my freelancer for further support on this point of clarification.

14

Yes, this is redundant and makes no sense to me also, But the conditions are provided to swap three Databases at a time and he was also in confusion to create environmental variables.

So what I had done, Is simply create a variable to fetch from environmental variable and then check if the variable isn’t empty. Also when calling;

dj_database_url.config(env="DATABASE_URL", default=DB_URL, conn_max_age=600)}

I have checked here two things, I have added a env parameter to check for same environmental variable, which I had stored above ( which is nonsense but a double check for any typo ) and also added the default=DB_URL. So by doing this, I think it is easy for him to have both methods. The variables can be assigned with a url directly, DB_URL = "database__url"

Basically I had made to double check, there are initially three databases swapping based on these environmental variables. All I had is based on the conditions provided to me. I am not a master of Django, but I am open to any further improvements by @KenWhitesell.

Thanks @Drone4four and @KenWhitesell

15

If I were writing this, it would probably look something like:

DATABASES = {
    'default': dj_database_url.config(
                   default='sqlite:///'+os.path.join(BASE_DIR, 'db.sqlite3'),
                   conn_max_age=600)
}

(Or the equivalent version if you’re using Django 3.1+ with the pathlib module.)

If you read the source code for the .config method in dj_database_url, you will see that it already checks for the existence of the default environment variable. If that variable exists, that’s the value being used to parse the URL. If that environment variable doesn’t exist, it will use the default.
So all that extra code is doing is repeating the work already provided by the module.
(Also, see the docs for how to format a URL for a sqlite database. Pay particular attention to footnote 3.)

1 Like
16

@KenWhitesell , exactly !

So I can format the code to swap three DBs as:

def check_env(environmental_variable):
    if environmental_variable in os.environ:
        return environmental_variable
    else:
        return ""

DATABASES = {
    'default': dj_database_url.config(
        env=check_env("FIRST_DB") or check_env("THIRD_DB"),
        default='sqlite:///'+os.path.join(BASE_DIR, 'db.sqlite3'), 
        conn_max_age=600)
    }

Now it will check for two env vars if none of them is present then it will fallback to default using the sqlite DB

Thank @KenWhitesell and @Drone4four

17

Can you do it this way? Sure, that’ll work. It doesn’t make sense to me to do it that way, but hey, if that’s how you want to approach it, ok.

The purpose of having an environment variable is that it can be changed for each runtime instance. I see no value in selecting between two environment variables when I can just change the value of that variable for a particular instance.