Ecto: Using different role for migrations

Questions / Help
1

Hi there!

Im trying to set a different role for the tables than the one I have in the settings This is due to how we deploy the app - each time it’s deployed a new pg user is created and used by the app. But all those new users inherit from the postgres one.

The problem is that migrations need to be run using the postgres role and not the dynamic user. To achieve that I need to do something like:

defmodule Wonderbot.Repo.Migrations.CreateServices do
  use Ecto.Migration

  def up do
      execute "SET ROLE postgres;"
      execute "CREATE EXTENSION IF NOT EXISTS hstore;"
.....

So that’s working correctly, but the problem is with the schema_migrations table, which I don’t control. Since ecto creates it automatically and using the dynamic user.

And if I try to run a migration like

defmodule Wonderbot.Repo.Migrations.AlterMigration do
  use Ecto.Migration
  @disable_ddl_transaction true

  def up do
      execute "SET ROLE postgres;"
      execute "ALTER TABLE schema_migrations OWNER TO postgres;"
    end
  end
end

It just hangs for ever, I guess it is because the schema_migration table is being locked by the migration process.

So is there any way to control how the schema_migration is being created so I can set the role?

Thanks!

2 Likes
2

Can you supply a different user/password via an environment variable?

Something like:

DB_URL=$ADMIN_DB_URL run_migrations.sh
DB_URL=$APP_DB_URL start_the_app.sh
3

No, because we don’t have access to the super user. So a role needs to be defined. :confused:

4

Im looking for something like this https://hexdocs.pm/ecto_sql/Ecto.Migration.html#module-transaction-callbacks but for the moment when the schema_migrations table is created.

5

As a hack, you could try disabling the migration table lock when deploying the migration that changes the owner of that table.

config :app, App.Repo, migration_lock: nil

Then re-enable it on the next deploy.

6

Yes, that’s what I ended up doing. Setting migration_lock: nil and also @disable_ddl_transaction true to run executeALTER TABLE schema_migrations OWNER TO xxx;", not using that, was causingecto.migrate` to hang for ever.

Thanks!

1 Like
7

Migration lock can be disabled in 3.1.5 per migration by setting @disable_migration_lock true