`sequelize db:migrate` fails for custom schema if user does not have CREATE permissions

What you are doing?

Attempting to use a pre-existing schema/migrations file with a user does not have CREATE permission on the database.

// .sequelizerc

const path = require('path')

module.exports = {
  config: path.resolve('./database/config', 'config.js'),
  'models-path': path.resolve('./database/models'),
  'seeders-path': path.resolve('./database/seeds'),
  'migrations-path': path.resolve('./database/migrations'),
}

// database/config/config.js

module.exports = {
  "development": {
    "username": "devuser",
    "password": "devpass",
    "database": "devdb",
    "host": "127.0.0.1",
    "dialect": "postgres",
    logging: console.log,
    // Use a different table name. Default: SequelizeMeta
    "migrationStorageTableName": "migrations",
    // Use a different schema (Postgres-only). Default: undefined
    "migrationStorageTableSchema": "sequelize"
  }
}

PSQL:

psql devdb

-- 1. logged in as superuser (not devuser), create the schema
CREATE SCHEMA sequelize;

-- 2. grant devuser the ability to create tables/objects (like migration table) within the schema
GRANT ALL ON SCHEMA sequelize TO devuser;

Terminal:

npx sequelize init # works
npx sequelize db:migrate # error

What do you expect to happen?

I expect it to detect that the schema already exists and proceed to use it (not fail) even though the user cannot create a schema.

What is actually happening?

The user doesn't have the ability to create a schema on the database so CREATE SCHEMA .. IF NOT EXISTS still fails even if the schema does exist. This has been raised recently with the PostgreSQL mailing list here: https://www.postgresql.org/message-id/flat/CADT4RqC99JiYHs3sOwmoxemzYEfUFwGDw2sFrz7FoGCOyX3_cw@mail.gmail.com.

Error Output:

Sequelize CLI [Node: 16.14.2, CLI: 6.4.1, ORM: 6.19.0]

Loaded configuration file "database/config/config.js".
Using environment "development".
Executing (default): SELECT 1+1 AS result
Executing (default): CREATE SCHEMA IF NOT EXISTS sequelize;

ERROR: permission denied for database devdb

One solution may be to check the INFORMATION_SCHEMA if the schema exists before trying to create it:

SELECT EXISTS (select 1 from information_schema.schemata where schema_name='sequelize'); -- 'sequelize' is the custom schema value

If it doesn't exist it could try to CREATE SCHEMA ... IF NOT EXISTS (and still result in error); otherwise, if the DBA set it up beforehand, it will not error and proceed to create the table if it doesn't exist. This way the user can have permission on the schema and not the ability to create other schemas at-will.

Dialect: postgres Database version: 14.2 Sequelize CLI version: 6.4.1 Sequelize version: 6.19.0

As posted in the other comment. The following patch to node_modules/sequelize-cli/lib/core/migrator.js seems to work.

Replace Line 67-77 with:

  return sequelize.authenticate().then(async () => {
    // Check if this is a PostgreSQL run and if there is a custom schema specified, and if there is, check if it's
    // been created. If not, attempt to create it.
    if (_index.default.version.getDialectName() === 'pg') {
      const customSchemaName = _index.default.umzug.getSchema('migration');
      const schemas = await sequelize.showAllSchemas();

      if (customSchemaName && customSchemaName !== 'public' && !schemas.includes(customSchemaName)) {
        return sequelize.createSchema(customSchemaName);
      }
    }
  }).then(() => migrator).catch(e => _index.default.view.error(e));

Make the anonymous function asynchronous with async
Add an asynchronous sequelize lookup to retrieve all existing schemas (and wait with await)
In the if-block, check if the custom schema is in the list of existing schemas

Credit: @arbazkhaan

Additional

The npm code is slightly different from this repo's. In this repo, the lines in question are (54-63) here: https://github.com/sequelize/cli/blob/e30625e82de71b7b41c9ad6f0e30f119d758b2c0/src/core/migrator.js#L54-L63

I think this is what it would look like with the changes outlined above:

    .then(async () => {
      // Check if this is a PostgreSQL run and if there is a custom schema specified, and if there is, check if it's
      // been created. If not, attempt to create it.
      if (helpers.version.getDialectName() === 'pg') {
        const customSchemaName = helpers.umzug.getSchema('migration');
        const schemas = await sequelize.showAllSchemas();

        if (customSchemaName && customSchemaName !== 'public' && !schemas.includes(customSchemaName)) {
          return sequelize.createSchema(customSchemaName);
        }
      }
    })

sequelize / cli