Building a tunnel to access databases

Context

Databases hosted on Scalingo are not directly available on the Internet. By default access to most databases are unencrypted, so unsecure. To access them remotely from your workstation you need to setup an encrypted connection.

Requirements

You need to have Scalingo’s command line interface to achieve this action.

Build the tunnel

By running the following command, an encrypted SSH tunnel will be built between you and your database.

$ scalingo -a example-app db-tunnel MONGO_URL
Building tunnel to <dbhost>:<dbport>
You can access your database on '127.0.0.1:<localport>'

We use MONGO_URL in the example, but it can be REDIS_URL or DATABASE_URL according to the database you’re using.

Use any client to read, import or export your data

Once the tunnel has been built, you can use any tool you need by connecting it to the 127.0.0.1:<localport> host.

Credentials to connect to the database are still the same, so read them from scalingo -a example-app env

Example

$ scalingo -a example-app db-tunnel MONGO_URL
Building tunnel to <dbhost>:<dbport>
You can access your database on '127.0.0.1:10000'

# In another terminal
$ scalingo -a example-app env | grep MONGO_URL
MONGO_URL=mongo://user:secret@<dbhost>:<dbport>/database

$ mongo -u user -p secret localhost:10000/database
$ mongodump -u user -p secret -h localhost:10000 -d database

Build the tunnel with the OpenSSH client

Our command line tool handles it in a simple command, but you might want to use the tunnel without it. With the standard OpenSSH client, the way to build the tunnel is:

ssh -L <local port>:<database host>:<database port> git@scalingo.com -N

The database host and database port can be found in the environment variable representing the connection string of your database instance. Get it from the dashboard or with the env command of the CLI. It should look like:

  • SCALINGO_<TYPE>_URL

The value of this variable is an URL which represents:

scheme://user:password@host:port/database_name

As stated previously, you need to get the host and port from the URL.

Example:

If the environment variable is the following:

SCALINGO_POSTGRESQL_URL=postgresql://user:secret@my-db.postgresql.dbs.appsdeck.eu:30000/my-db

The command to run is:

ssh -L 10000:my-db.postgresql.dbs.appsdeck.eu:30000 git@scalingo.com -N

Then you connect on localhost:10000 to reach your Scalingo database. (You’ll still need to authenticate to the database with the credential you can get from the connection string)

schedule 09 Jan 2017