Changing PostgreSQL Role before executing any migration command

[alvin-nt]

What you are doing?

Since PostgreSQL enforces that only the owner of the table is allowed to do CREATE TABLE, ALTER TABLE and DROP TABLE, I need to switch to that particular role before executing any CREATE TABLE, ALTER TABLE or DROP TABLE commands. This is done due to limitations of PostgreSQL and security requirements.

What do you expect to happen?

There is a config/code that allows me to configure this.

What is actually happening?

I cannot find any documentation/feature related to this issue.

Dialect: postgres Database version: AWS Aurora - PostgreSQL Compatible 12.4 Sequelize CLI version: 6.4.1 Sequelize version: 6.17.0

[ephys]

Potential solutions:

• Create a new Sequelize instance inside your migration with the owning user, and use that instance's queryInterface to alter your tables
• Change your config file to run the migrations with the user that owns the tables (assuming all tables belong to the same user)
• Change the owner of the tables manually before running the migrations

[alvin-nt]

in my case, changing to the user would only be possible by executing SET ROLE command before executing any DDL-affecting command, not by logging into the user who owns the table. The admin does not give the access to the owner user. The migration script would be like this

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) {
    await queryInterface.sequelize.query('SET ROLE tbl_owner_role');
    return queryInterface.sequelize.transaction(async (transaction) {
      // do migration
    });
  },
  
  down: async (queryInterface, Sequelize) {
    await queryInterface.sequelize.query('SET ROLE tbl_owner_role');
    return queryInterface.sequelize.transaction(async (transaction) {
      // do migration
    });
  }
}

This works well as long as SequelizeMeta has been created. If SequelizeMeta is not present (in the case of new DB), the table needs to be created with proper permissions beforehand (manually).

It would be nice to have something like this

'use strict';

module.exports = {
  beforeUpOrDown: async (queryInterface, Sequelize) { // or better yet, can be put in the config.js
    await queryInterface.sequelize.query('SET ROLE tbl_owner_role'); // assuming that the same sequelize connection will be reused
  },
  up: async (queryInterface, Sequelize) {
    return queryInterface.sequelize.transaction(async (transaction) {
      // do migration
    });
  },
  
  down: async (queryInterface, Sequelize) {
    return queryInterface.sequelize.transaction(async (transaction) {
      // do migration
    });
  }
}

[mike-usa]

I concur with these statements. I'd also like to separate the database management roles from the data management role. Please consider custom schemas in the solution.