Install extension in PostgreSQL database via migration without superuser role?

Question

We are using migrations (via Sequelize, in JavaScript) to maintain changes to our database. I have a need to add a CREATE EXTENSION call but since I am running as the database creator, and not superuser, I get a permission denied to create extension.

Is there a way to modify security on a single database to allow a user to install an extension via a migration file? IOW, when I create the "naked" database and apply my permissions, can I set security up to allow CREATE EXTENSION and DROP EXTENSION for a specific user?

Answer 1

For anyone still looking, here is how to create an extension via a sequelize migration. It doesn't address the secondary question of user roles however. But this works for me.

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query('CREATE EXTENSION extensionName;');
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query('DROP EXTENSION extensionName;');
  }
};

Answer 2

No, there is no setting for CREATE ROLE for granular CREATE/DROP EXTENSION control. I assume, it depends on the extension you use, but you based on the documentation:

Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script.

I was running into the same issue with pg_trgm extension, but even setting role to database owner did not resolve the issue.