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...
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.
I just found myself needing such functionality, but for all ActiveRecord connections. In a Rails v7 application I used the following monkey-patch:
module PostgreSQLAdapterWithSetRole
def initialize(connection, logger, connection_parameters, config)
super(connection, logger, connection_parameters, config)
if config[:role]
connection.exec("SET ROLE #{config[:role]};")
end
end
end
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(PostgreSQLAdapterWithSetRole)
This reads a role configuration value from database.yml and if set issues SET ROLE immediately after the connection is established.