cannot create extension without superuser role

I'm trying to run unit tests in Django, and it creates a new database. The database has postgis extensions and when I regularly create the database, I use "CREATE ExTENSION postgis".

However, when I run tests, it gives me the following error:

$ ./manage.py test
Creating test database for alias 'default'...
Got an error creating the test database: database "test_project" already exists

Type 'yes' if you would like to try deleting the test database 'test_project', or 'no' to cancel: yes
Destroying old test database 'default'...
DatabaseError: permission denied to create extension "postgis"
HINT:  Must be superuser to create this extension.

The user has the Create DB privilege already, I'm using PostgreSQL 9.1 on Ubuntu 12.04 with Postgis 2.0.

Solution 1:

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;

Solution 2:

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.