How can I run `SET ROLE` automatically for postgres ActiveRecord migrations?

Question

I'd like to setup app-specific users for a database, just for auditing/debugging purposes, but use the root "postgres" user role for table creation, since it already has the default privileges set up appropriately (and also because in my case, most of the tables are already owned by that user).

The app would only need this table creation/modification permission when running migrations, but I would be fine with them having it all the time if that's easier. However, I haven't found any way to use SET ROLE successfully in Rails.

I thought it would work to add a new rake task that sets the role before the db:migrate task runs, like this:

# in lib/tasks/set_role.rake
namespace :db do
  task :set_role do
    ActiveRecord::Base.connection.execute("SET ROLE postgres")
  end
end

# in initializers/migration_hooks.rb
Rake::Task["db:migrate"].enhance ["db:set_role_postsgres"]

But that didn't work...

mltsy mltsy · Accepted Answer · 2020-06-23T17:52:34

I'm not sure why the above didn't work, but I just had to put this all in the .rake file, and then it worked:

# lib/tasks/migration_hooks.rb

namespace :db do
  task set_role_postsgres: :environment do
    puts "Setting Role to 'postgres'"
    ActiveRecord::Base.connection.execute("SET ROLE postgres")
  end
end

Rake::Task["db:load_config"].enhance ["db:set_role_postsgres"]

Note: The db:load_config task is a prerequisite for all db tasks, so this should handle setting the role for not only db:migrate, but also db:migrate:up, db:migrate:down, db:rollback, etc.

How can I run `SET ROLE` automatically for postgres ActiveRecord migrations?

1 Answers