Using Separate Database Users for Migrations in Rails

In the past year I have done a lot of work with PostgreSQL (PG) and managing DDL (Data Definition Language) changes. This work has been focused on Java applications which use Flyway to manage migrations, which has less abstractions than Rails migrations. I have been thinking about how to apply some of the lessons I’ve learnt to Rails applications.

One of the first changes - or best practices - I applied with PG migrations was to use separate roles (the PG concept for user or group) for running migrations and for the application itself. This is important for a few reasons:

1. It’s a basic best practice to allow only a privileged role to perform DDL changes to limit the impact of any unintended SQL queries from the application layer - bugs, hackers, etc
2. In PG the role that creates database objects (e.g. tables and schemas) is also set as the owner of that object, and by default the owner has certain (usually full) privileges (the PG concept for permissions) on those objects.

There are a few gems that provide guardrails around Rails migrations

• strong_migrations
• safe-pg-migrations
• pg_ha_migrations

These are all wrappers around the native Active Record migrations in various forms and focus on executing the migrations themselves, but don’t consider permissions or roles. The Active Record Migrations Guide also doesn’t mention anything about permissions, roles, or related topics like object ownership.

Active Record doesn’t provide any documented path for running migrations as a separate role from the application role. Here are a few options I have found on StackOverflow and related sites:

Create a separate environment for running migrations

This would involve create a new environment and related database role in config/database.yml. In order to run migrations, you would need to specify the new environment, eg. RAILS_ENV=migrations rails db:migrate. In practice you would need to have a migration environment for each of your existing environments, so development_migrations, test_migrations, production_migrations, etc - since you run migrations in all of these environments, and running with a different role only in production seems like a bad idea. The golden rule is that you want to have the same privileges in local development (and CI) as in production.

This idea could work, but I really don’t like the idea of adding a new configuration file for config/environments/production_migrations.rb (as well as all the other environments). Intuitively running migrations feels more like a concept that happens within a given environment, rather than a separate environment. If Rails has a configuration property to set the migration connection string - which defaulted to the environment’s existing connection string - that would feel like a pretty natural feature to me.

Modify the migration rake task

There are a few posts online suggesting this solution in various forms - creating a custom rake task, monkey patching or enhancing the db:migrate task, etc. ChatGPT was very keen on the idea of using an initializer to manually establish the database connection with a different role.

# config/initializers/override_connection_for_migrations.rb
if defined?(Rails::Command) && ARGV.include?("db:migrate")
  puts "[INFO] Using migration user"
  ActiveRecord::Base.establish_connection(
    Rails.configuration.database_configuration[Rails.env].merge(
      'username' => ENV['MIGRATION_DB_USERNAME'] || 'migration_user',
      'password' => ENV['MIGRATION_DB_PASSWORD'] || 'migration_password'
    )
  )
end

This could work, but I’m not sure if all the different ways of running migrations (eg. db:structure:load, db:reset, etc) would work correctly.

Environment Variables

Active Record allows you to specify the canonical database connection string via a DATABASE_URL variable. We can use this behavior to specify a different connection string and user when running migrations.

I found a nice way to structure this behavior in this post on PgAnalyze. The idea is to use the foreman gem with a Procfile entry to run your migrations.

web: bundle exec puma -C ./config/puma.rb
migrate: DATABASE_URL=${DATABASE_URL_MIGRATIONS:?DATABASE_URL_MIGRATIONS must be set} bundle exec rails db:migrate

This seems like the most sensible approach to me - since it ties into an existing Active Record feature. It’s also possible to write a gem to capture some of this behavior and make it more specific to the db:migrate task. This approach has the same problem as with the custom or modified rake task - it may not work with all the different ways of running migrations.

I’m going to try the environment variable approach first, since that also works nicely with Heroku (which is my default deployment platform). As part of this I also want to explore how to manage different database roles (locally and in production) and dump the entire database schema - which includes privileges and ownership.

Rails PostgreSQL