GeoDjango and PostgreSQL

I recently had to set up GeoDjango on a site that used PostgreSQL as a database. As the online instructions for doing this were less than totally clear, I decided to write down how I did it.

In order to use GeoDjango with a PostgreSQL database, you must install PostGIS. Only the DB superuser can add this extension (as it's C code and can run anything). We have 2 scenarios:

Scenario 1: your Django app connects to the PostgreSQL database as the DB superuser

In practical terms: in your Django settings you have specified that the DATABASES USER is 'postgres'. You might be doing this if security is a non-issue, or if you're using Docker (where the convention seems to be: run everything as superuser).

In this case, Django helpfully provides a database migration to automatically add the extension. Read the Django docs, ignore the part on creating the extension manually, and instead create a new database migration as suggested in the docs:

from django.contrib.postgres.operations import CreateExtension
from django.db import migrations

class Migration(migrations.Migration):

    operations = [
        CreateExtension('postgis'),
        ...
    ]

That's it - you've finished.

Scenario 2: your Django app uses a PostgreSQL user with restricted permissions (NOT a superuser)

This was the situation that I found myself in. I have a single database used by several websites - and each website has its own database user for security reasons. In this case, there's a few lines of code to type at the command line.
When you initially create the DB, connect to the DB as user postgres and add the extension:

sudo -i -u postgres
psql <database name>
> CREATE EXTENSION postgis;
> \q

This is enough for the main database to work. Note that you will have to repeat this operation if you drop and recreate the database.

For the test database, there's an extra step. Django will create a new DB on every test run - but the unprivileged user used by Django to connect/create databases cannot add the postgis extension. However - what we can do is manually create an empty 'template' database:

sudo -i -u postgres
psql
> CREATE DATABASE template_<database name>_test WITH owner=<username> IS_TEMPLATE=true;
> \q
psql template_<database name>_test
> CREATE EXTENSION postgis;
> \q

And then tell Django to use that as a template for the unit tests database:

DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.postgis',
        'NAME': '<database name>',
        'USER': '<user>',
        'PASSWORD': '<password>',
        'TEST': {
            'TEMPLATE': 'template_<database name>_test'
        }
    }
}

And that's all. It's a bit more work than just running in superuser mode, but potentially it's a lot more secure.