CODEWITHSUNDEEP
databasepostgresqlpostgresql-9.1
user9903
user9903

Reputation:

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.

Upvotes: 96

Views: 128690

Answers (8)

FlipperPA
FlipperPA

Reputation: 14331

As several have mentioned here, the CREATE EXTENSION command can be run on trusted extensions. Several packaged PostgreSQL extensions are considered trusted out of the box. If you want to mark another extension trusted, you need to modify the control file. This will be found in the shared extension directory.

To find your shared directory, type pg_config --sharedir, which will be something like /usr/pgsql-##/share.

Next, cd /usr/pgsql-##/share/extensions, and ls *.control. You'll see control files for each extension, such as vector.control.

Edit the control file for the extension in question, and ensure that trusted = true exists on a line. Then restart the PostgreSQL server and both superusers AND individual database owners should be able to run CREATE EXTENSION.

Upvotes: 0

Mark
Mark

Reputation: 5622

Somewhat related to the question, but in the context of Helm charts: I attempted to add the vector extension to the bitnami/postgresql Helm chart. I was able to create a custom Docker image using the Dockerfile here and tagged this with a custom tag (acme/postgresql:1.0.0).

I then overrode settings as follows in the Bitnami Postgres Helm chart to use this new image:

postgresql:
  enabled: true
  auth:
    postgresPassword: password
    database: foo
  image:
    repository: acme/postgresql
    tag: 1.0.0
  primary:
    initdb:
      user: postgres
      password: password
      scripts:
        dbinit.sql: |
          CREATE USER foo WITH ENCRYPTED PASSWORD 'password';
          GRANT USAGE, CREATE ON SCHEMA public TO foo;
          GRANT ALL PRIVILEGES ON DATABASE foo TO foo;
          ALTER DATABASE foo OWNER TO foo;
          \c foo;
          CREATE EXTENSION vector;

Note that the postgres user that I'm running the dbinit.sql script with is a superuser.

An important point here is the \c foo line. Without this, the vector extension doesn't install, because Postgres doesn't know which database to install it on.

Upvotes: 0

user9903
user9903

Reputation:

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

In the worst case you can create a new database superuser for PostgreSQL:

$ createuser --superuser <user_name>

or alter an existing database user's role:

postgres# ALTER ROLE <user_name> SUPERUSER;

Upvotes: 147

Peter Thomassen
Peter Thomassen

Reputation: 1880

As of Postgres 13, some modules / extensions are considered "trusted", and can be installed by non-superusers who have CREATE privilege on the current database.

The trusted modules are: btree_gin, btree_gist, citext, cube, dict_int, fuzzystrmatch, hstore, intarray, isn, lo, ltree, pgcrypto, pg_trgm, seg, tablefunc, tcn, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp

To check whether a given module is eligible, visit https://www.postgresql.org/docs/13/contrib.html and select the module in question. If it is considered "trusted", the page will contain the sentence:

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

Upvotes: 11

Braian Coronel
Braian Coronel

Reputation: 22907

A safe way to do this without delegating superuser privileges would be to access the database in which we are executing the query with a user with a superuser role such as postgres.

$ sudo -u postgres psql <db_name>

<db_name>#= CREATE EXTENSION IF NOT EXISTS <your-extension>;

This way you don't expose security and you can believe the extension in the db.

GL

Upvotes: 6

user73657
user73657

Reputation: 668

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',
        },
    },
}

Upvotes: 25

Harry Moreno
Harry Moreno

Reputation: 11643

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;

Upvotes: 45

Ariel
Ariel

Reputation: 26773

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.

Upvotes: 83

Related Questions