Index ¦ Archives ¦ Atom

Easier PostgreSQL Extension Installations

Postgres has a number of extensions which require superuser access for a user to install them, PostGIS being one of them. If you're wanting to install a GeoDjango app, you'll probably encounter an error such as:

$ ./ migrate
Traceback (most recent call last):
django.db.utils.ProgrammingError: permission denied to create extension "postgis"
HINT:  Must be superuser to create this extension.

For security reasons you don't want to give the user superuser access, and for convenience you don't want to go and manually go into each database just to add PostGIS support.

PostgreSQL Extension Whitelisting

The pgextwlist extension can allow regular users to use the CREATE EXTENSION command, allowing them to install the extension without getting the permission denied error.

To install on Ubuntu 16.04 (Xenial Xerus):

$ sudo apt-get install postgresql-9.5-pgextwlist

To install on Debian 8 (Jessie):

$ sudo apt-get install postgresql-9.4-pgextwlist

After installing, edit the relevant postgresql.conf file for your Postgres instance and add the following lines:

# Allow PostGIS to be loaded easily
local_preload_libraries = 'pgextwlist'
extwlist.extensions = 'postgis'

Then run:

$ sudo service postgresql restart

Now you should be able to use a GeoDjango app/database without any errors:

$ ./ migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:

© Alex Tomkins.