How do I configure Rails for password-less access to remote database

Note: this is similar to Use Ruby on Rails and SSH to access remote MySQL database on remote server, but the OP didn’t provide much info, and the only answer given doesn’t answer the question.

background

We recently switched our remote database from password authentication to ssh key based authentication. I have verified that I can access the db through the elegant Sequel Pro graphical db client with the following settings (some names intentionally obfuscated):

MySQL Host: woofwoof.us-west-2.rds.amazonaws.com
Username:   bowser
Database:   canine
Port:       3306

SSH Host:   salt.woofwoof.com
SSH User:   guardian
SSH Key:    ~/.ssh/id_rsa

Now I need Rails to connect to the same database, also using ssh key-based authentication.

the question

What goes in my config/database.yml file?

So far I have:

canine:
    adapter: mysql2
    database: canine
    username: bowser
    host: woofwoof.us-west-2.rds.amazonaws.com
    port: 3306

… but how do I specify SSH Host, SSH User and SSH Key in the config/database.yml file?

additional info

Back when our database had password authentication, the following worked:

canine:
    adapter: mysql2
    database: canine
    username: bowser
    password: *secret*
    host: woofwoof.us-west-2.rds.amazonaws.com
    port: 3306

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

First, you need to establish an SSH tunnel the MySQL server. On the client machine, run:

ssh -fNg -L 3307:127.0.0.1:3306 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2245574350464b434c6251434e560c554d4d44554d4d440c414d4f">[email protected]</a>

That will establish an SSH tunnel to the salt.woofwoof.com server. Any connections to localhost port 3307 will get sent through the tunnel to the remote host on port 3306.

Then just configure your database.yml like you would for a local connection, but specify the forwarded port 3307:

canine:
  adapater: mysql2
  database: canine
  username: bowser
  password: *secret*
  port: 3307

You may also want to add the ssh tunnel setup to /etc/inittab so that the tunnel is establish after boot. See http://chxo.com/be2/20040511_5667.html for one example of how to do that.

Method 2

There is also a pure rails solution

add the following to your Gemfile

 gem 'net-ssh-gateway'

then create a class

module RemoteConnectionManager
  SSH_USER = 'YOUR_SSH_USER'

  def self.port_through_tunnel(remote_host, port, local_port: nil, db_host:'localhost')
    return Net::SSH::Gateway.new(remote_host, SSH_USER)
      .open(db_host,port,local_port)
  end
end

last change your database.yml

  adapter: mysql2
    host: 127.0.0.1
    port:  <%= RemoteConnectionManager.port_through_tunnel('your_ssh_host', 3306, db_host: 'your_db_host_eg_some_aws_rds_db' ) %>
    username: your_db_username
    password: your_db_password
    database: your_db_name

if local_port is nil Net/ssh will pick a free one

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0