Yes, you can.
Having now gone down this path: yes, it is possible to set up an SSH tunnel from heroku to an external database. [NOTE: My particular app was written in Ruby on Rails, but the solution given here should work for any language hosted on Heroku.]
Statement of the problem
I am running an app on Heroku. The app needs to access an external MySQL database (hosted on AWS) from which it grabs data for analysis. Access to the MySQL database is protected by ssh keys, i.e. you cannot access it with a password: you need an ssh key pair. Since Heroku starts each dyno fresh, how can you set up an SSH tunnel with the proper credentials?
Short Answer
Create a script file, say ssh_setup.sh. Put it in ${HOME}/.profile.d/ssh_setup.sh. Heroku will notice any file in ${HOME}/.profile.d and execute it when it creates your dyno. Use the script file to set up ~/.ssh/id_rsa and ~/.ssh/id_rsa.pub and then launch ssh in tunneling mode.
The Full Recipe
1. Generate keypair for access to the external DB
Create a key pair and save it in ~/.ssh/heroku_id_rsa and ~/.ssh/heroku_id_rsa.pub. Use an empty passphrase (otherwise the Heroku dyno will try to prompt for it when it starts up):
$ ssh-keygen -t rsa -C "[email protected]"
Generating public/private rsa key pair.
Enter file in which to save the key (/home/.ssh/id_rsa): /home/.ssh/heroku_id_rsa
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/.ssh/heroku_id_rsa.
Your public key has been saved in /home/.ssh/heroku_id_rsa.pub.
2. Test ssh access to the external DB
Send your PUBLIC key (~/.ssh/heroku_id_rsa.pub) to the administrator for the external DB and ask for access using that key. After that, you should be able to type the following into a shell window on your local machine:
$ ssh -v -i ~/.ssh/heroku_id_rsa -N -L 3307:${REMOTE_MYSQL_HOST}:3306 ${TUNNEL_USER}@${TUNNEL_SITE}
where
- ${REMOTE_MYSQL_HOST} is the address of the remote database. In our case, it is something like long_complicated_string.us-west-2.rds.amazonaws.com
- ${TUNNEL_USER} is the user account on the site that accesses the database
- ${TUNNEL_SITE} is the address of the machine that accesses the database
You should get a long string of debugging output that include the following:
debug1: Authentication succeeded (publickey).
...
debug1: forking to background
debug1: Entering interactive session.
Congratulations. You've set up tunneling on your own machine to the external database. Now to convince Heroku to do the same...
3. Set up configuration variables
The goal is to copy the contents of ~/.ssh/heroku_id_rsa and ~/.ssh/heroku_id_rsa.pub to the corresponding directories on your Heroku dyno whenever it starts up, but you REALLY don't want to expose your private key in a script file.
Instead, we'll use Heroku's configuration variables, which simply (and safely) sets up shell environment variables when launching the dyno.
$ heroku config:set HEROKU_PRIVATE_KEY=`cat ~/.ssh/heroku_rsa_id`
$ heroku config:set HEROKU_PUBLIC_KEY=`cat ~/.ssh/heroku_rsa_id.pub`
While we're at it, we'll set up a few other potentially sensitive variables as well:
$ heroku config:set REMOTE_MYSQL_HOST=<your value of REMOTE_MYSQL_HOST from above>
$ heroku config:set TUNNEL_USER=<your value of TUNNEL_USER from above>
$ heroku config:set TUNNEL_SITE=<your value of TUNNEL_SITE from above>
4. Create version 1.0 of your script file
In your project home directory, create a directory .profile.d. In that directory, create the following:
# file: .profile.d/ssh-setup.sh
#!/bin/bash
echo $0: creating public and private key files
# Create the .ssh directory
mkdir -p ${HOME}/.ssh
chmod 700 ${HOME}/.ssh
# Create the public and private key files from the environment variables.
echo "${HEROKU_PUBLIC_KEY}" > ${HOME}/.ssh/heroku_id_rsa.pub
chmod 644 ${HOME}/.ssh/heroku_id_rsa.pub
# Note use of double quotes, required to preserve newlines
echo "${HEROKU_PRIVATE_KEY}" > ${HOME}/.ssh/heroku_id_rsa
chmod 600 ${HOME}/.ssh/heroku_id_rsa
# Preload the known_hosts file (see "version 2" below)
# Start the SSH tunnel if not already running
SSH_CMD="ssh -f -i ${HOME}/.ssh/heroku_id_rsa -N -L 3307:${REMOTE_MYSQL_HOST}:3306 ${REMOTE_USER}@${REMOTE_SITE}"
PID=`pgrep -f "${SSH_CMD}"`
if [ $PID ] ; then
echo $0: tunnel already running on ${PID}
else
echo $0 launching tunnel
$SSH_CMD
fi
5. Push the configuration and test it on Heroku
You know the drill...
$ git add .
$ git commit -m 'launching ssh when Heroku dyno starts up'
$ git push heroku master
Give it a whirl...
$ heroku run sh
You may see something like:
Running `sh` attached to terminal... up, run.1926
bash: creating public and private key files
bash: launching tunnel
The authenticity of host 'example.com (11.22.33.44)' can't be established.
ECDSA key fingerprint is 1f:aa:bb:cc:dd:ee:ff:11:22:33:44:55:66:77:88:99.
Are you sure you want to continue connecting (yes/no)?
This is a problem, since it means the dyno needs user input to continue. But we're about to fix that. What follows is a somewhat ugly hack, but it works. (If someone has a better solution, please comment!)
6. Create version 2.0 of your script file
(Continuing from above) Answer yes to the prompt and let the script run to completion. We're now going to capture the output of the known_hosts file:
heroku $ cat ~/.ssh/known_hosts
|1|longstringofstuff= ecdsa-sha2-nistp256 more stuff=
|1|morestuff= ecdsa-sha2-nistp256 yetmorestuff=
Copy that output and paste it into your ssh-setup.sh file under the "Preload the known_hosts" comment, and edit so it looks like this:
# Preload the known_hosts file (see "version 2" below)
echo '|1|longstringofstuff= ecdsa-sha2-nistp256 more stuff=
|1|morestuff= ecdsa-sha2-nistp256 yetmorestuff=' > ${HOME}/.ssh/known_hosts
# Start the SSH tunnel if not already running
... etc ...
7. Push and test v2
You know the drill...
$ git add .
$ git commit -m 'preload known_hosts file to avoid prompt'
$ git push heroku master
Give it a whirl. With luck, you should see something like this:
$ heroku run sh
Running `sh` attached to terminal... up, run.1926
bash: creating public and private key files
bash: launching tunnel
8. Debugging
If the tunnel isn't getting set up properly, try pre-pending a -v (verbose) argument to the SSH command in the script file:
SSH_CMD="ssh -v -f -i ${HOME}/.ssh/heroku_id_rsa -N -L ${LOCAL_PORT}:${REMOTE_MYSQL_HOST}:${MYSQL_PORT} ${REMOTE_USER}@${REMOTE_SITE}"
Repeat the git add ... git commit ... git push sequence and call heroku run sh. It will print a lot of debug output. A sysadmin friend with more brains than I have should be able to decode that output to tell you where the problem lies.
9. (Rails only): Configuring the DB
If you're running Rails, you'll need a way to access the database within your Rails app, right? Add the following to your config/database.yml file (changing the names appropriate):
mysql_legacy:
adapter: mysql2
database: mysql_legacy
username: <%= ENV['LEGACY_DB_USERNAME'] || 'root' %>
password: <%= ENV['LEGACY_DB_PASSWORD'] || '' %>
host: 127.0.0.1
port: 3307
The important thing to note is that the host is the local host (127.0.0.1) and the port (3307) must match the -L argument given to ssh in the script:
-L 3307:${REMOTE_MYSQL_HOST}:3306
In summary
Despite what's been said elsewhere, you can tunnel out of Heroku to access a remote database. The above recipe makes a LOT of assumptions, but with some customizations it should work for your specific needs.
Now I'll go get some sleep...
