Automating Use of the PostGIS Backend for Django Tests

Ever had the problem that your automated Django tests wouldn’t work because you didn’t have permissions to create the given extension and you thought: “WTF? I’ve solved this problem already!”? Yup, that happens to me too, so I’m writing this up here to help my future self and hopefully others as well.

Note that this tip is already well described on StackOverflow. Nevertheless, a slightly different explanation of the same problem can also be useful.

Django tests need the PostGIS extension set up automatically

At Drift+Noise, we work a lot with geospatial datasets (sometimes also called GIS data, where GIS stands for Geographic Information System). We also use Postgres for our databases which means that we make use of the PostGIS extensions to store and make queries on geospatial data. On the frontend we use Django, where the issue manifests itself in the Django tests.

Normally you don’t have to worry about superuser access to the database when using the Postgres backend in order to run automated tests for Django applications, since the test database just gets created, migrated and destroyed automatically in each test run. However, if you need an extension such as PostGIS in the database being developed, then you’ll need to use the PostGIS Django backend (django.contrib.gis.db.backends.postgis) and to create the extension on the database.

For the production database, you use a command similar to this:

$ sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS postgis;" <database_name>

Note that we had to have superuser permissions in order to do this. The usual automated test database setup won’t have this permission level, hence you’ll run into an error like this:

django.db.utils.ProgrammingError: permission denied to create extension "postgis" test

What to do?

The solution: add the PostGIS extension to your Postgres template

What this error message is trying to tell you is that the Django test runner can’t run the CREATE EXTENSION command in order to add the PostGIS extensions (well, it can run it, it just doesn’t have the permissions to create the extension). The trick is to add the extension to the template database that the test runner uses when setting up the test database, i.e. the obviously-named template1 database. Thus, one just needs to run the CREATE EXTENSION command on template1 and the extension will simply be available when the test database is automatically created, i.e.:

$ sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS postgis;" template1

Problem solved!

Automate all the things!

We use use Jenkins for continuous integration and delivery. If you do too and want everything to “just work”, then you’ll need run this command on your Jenkins server (or servers) so that the error doesn’t turn up there.

We also use Ansible for provisioning and configuration management of all our servers and applications, including Jenkins. Hence, to ensure that we always have the PostGIS extensions set up we use the following step in our jenkins Ansible role:

- name: install PostGIS extensions into template1 (needed for framsat-data-hub)
  shell: /usr/bin/psql -c 'CREATE EXTENSION IF NOT EXISTS postgis;' template1 postgres
  args:
      executable: /bin/bash
  become_user: postgres

This way we’ve just automated away a problem which could otherwise keep appearing.

Hope that helped!