cannot create extension without superuser role

You can also install postgis to the template1 database template which is inherited by default by all newly created database.

$ psql -U postgres -d template1 -c "CREATE EXTENSION postgis;"

All new databases created from this point will have the postgis extension installed, including Django's test database, unless they specify a different template when creating a database.

If having postgis installed to all newly created databases is not desirable, you can create a new template, install postgis in it, and then have Django use this template when creating the test database.

$ createdb template_postgis;  # create a new database
$ psql -U postgres -c "UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';"  # make it a template
$ psql -U postgres -d template_postgis -c "CREATE EXTENSION postgis;"  # install postgis in it

Then in Django settings:

...
DATABASES = {
    'default': {
        ...
        'TEST': {
            'TEMPLATE': 'template_postgis',
        },
    },
}

Easiest way I found is to:

su postgres
psql
alter role user_name superuser;
#then create the extension as the user in a different screen
alter role user_name nosuperuser;

Basically give the user superuser powers for a short time, and create the extension. Then revoke the superuser powers.

You can also use \connect user_name to become that user and create the extension directly from the postgres user.


Another way to solve this that is suggested in the django docs

$ psql <db name>
> CREATE EXTENSION postgis;

you can log into a database as the superuser and create the extension once. The extension will then be available to your api's db user. When django executes CREATE EXTENSION IF NOT EXISTS postgis postgres will not throw.

If you are seeing errors when migrating doublecheck you created the extension in the correct database, a sample sesssion

$ psql
=> \l            - list databases
=> \c <db name>  - connect to django db
=> create extension postgis;

you can verify the extension is installed if you see the table spatial_ref_sys

=> \dt
                   List of relations
 Schema |            Name            | Type  |  Owner
--------+----------------------------+-------+----------
 public | spatial_ref_sys            | table | postgres

for tests I recommend running them against a local dev database and granting the user superuser abilities like > ALTER ROLE <user_name> SUPERUSER;


The Django documentation on postgis has some information on setting up user privileges.

In the worst case you can create a new superuser:

$ createuser --superuser <user_name>

or alter an existing user's role:

postgres# ALTER ROLE <user_name> SUPERUSER;