Rails migration with a different user than regular connections

Question

How can I connect to the DB with a different user when running rails migrations?

The use case is to create a different Postgres user for migrations than that used to run the webserver. Whereby the regular webserver DB user is limited to CRUD for data, but not have grants or abilities like DROP etc.

In order for that to happen, I need Rails migrations to run with a different user with full Create and Drop table access. (Creds could live in the Rails encrypted credentials file, but storing them in ENV on server is probably fine).

So, to be clear, setting up the users is not a problem, I can do all that. I just want to know if there is a best-practice way to change the user/creds used by rails db:migrate

Answer 1

You can create a new environment which has its own database user configured in config/database.yml. Example:

default: &default
    adapter: postgresql
    database: dev_db_name
    username: dev_user_name
    password: dev_password

development:
    <<: *default

production:
    <<: *default
    database: production_db_name
    username: production_user_name
    password: production_user_password

migrations:
    <<: *default
    database: production_db_name
    username: migration_user_name
    password: migration_user_password

So when you run migrations you have to specify the new environment:

RAILS_ENV=migrations rake db:migrate

IMPORTANT: When create a new environment be sure that you complete this required steps:

1. Create a new config/environments/YOUR_ENVIRONMENT.rb file
2. Create a new database configuration entry in config/database.yml if your application uses database
3. Create a new secret key base entry in config/secrets.yml for apps on Rails 4.1 and higher

More information click here

Answer 2

You can set the database connection through ENV["DATABASE_URL"]. This takes precedence over and is merged with over any settings in config/database.yml:

DATABASE_URL=postgresql://localhost/some_other_database?username=max&password=p4ssw0rd rails db:migrate

If you want to do anything more advanced like using the encrypted credentials to store the password I would recommend writing a custom rake task.