How to change data_migrations table owner?

[mike-usa]

For rake/rails db:migrate (i.e., schema_migrations), a rake task can be used to intercept all migrations using the db:load_config hook. A new connection can be created and then database users/roles can be set on that ActiveRecord base connection, which is then used by all subsequent tasks.

The database.yml might use an application user that has write access to tables that are owned by the DBA. For safety, we might want to perform migrations by the enduser’s credentials (ensuring they’re the DBA).

So the load_config hook loads the database config for the environment and then overwrites the user (inherited from caller). The hook then executes SQL to set its role (e.g., in PostgreSQL SET ROLE dba;). Any subsequent migration is authored and executed by that role (including table creation).

In attempting to do this with data-migrate, I can intercept the connection and set the user, but it doesn’t seem to maintain the session. The role setting is lost. I assume:

1. this may be related to threading
2. data migration maybe creating a copy of the configuration, but not using the same database session

Is there a recommended way to set user and role only for data-migration tasks?

[ilyakatz]

Hm, this gem doesn't set configuration for db connection, it uses whatever rails provides (so database.yml). But, I think you're right, it's not in the same process. If you look into the rake definitions https://github.com/ilyakatz/data-migrate/blob/main/tasks/databases.rake#L201, they are spawning new process, so if you do anything prior, that probably won't take effect.

This use case hasn't come up before. Is this something that can be set directly in the data migration files?

[mike-usa]

I think it could be set in that file, but then we’d have to set for every migration. I generally like those files to be relatively light. The fewer custom configuration, the fewer bugs when rolling back and fewer problems when starting fresh.

Data migrations are also less frequent than schema migrations, so the issue might be:

1. it’s something additional to remember — must remember to set role for each migration (and what if role changes?)
2. it would deviate from other conventions (the similarly structured schema migrations)
3. it may be a general DX pain

Our permission handling may be a little unorthodox due to how our CI/CD runners execute code and whom owns those processes.

For the time being, I’ve updated the data directly on the database, but I’d like to use this gem (I’m glad I found it) and capture these changes in source control.

If it helps, this is my approach:

# /lib/tasks/use_dba_for_data_migrations.rake
namespace :data
  task :load_config do
    config = ActiveRecord::Base.configurations.config_for(env_name: Rails.env, name: ‘primary’).configuration_hash
    unless ARGV.include?(db:test:prepare)
      current_user_config = config.merge({username: ENV[‘LOGNAME’], …})
      ActiveRecord::Base.configurations.clear
      ActiveRecord::Base.configurations.unshift(current_user_config)
      ActiveRecord::Base.establish_connection(current_user_config)
      ActiveRecord::Base.connection.execute(‘SET ROLE dba’)
    end
  end
      
  task :migrate :load_config do
    # first set dba role then run post-migration steps
    # usually GRANT statements that grant permissions to ALL TABLES in a namespace to the app account or a read-only account
  end
end

My version of this for schema changes works, but db migrations automatically calls load_config as part of a prepare step. data:migrate does not have this, I’ve only included it above for consistency.

I think there should be consistency, since the gem integrates with existing migration tasks (e.g., db:migrate:with_data), which I was hoping would also call (and borrow) migrate’s load config process.

---

Apologies upfront as all of this was typed from the phone.