cg-ssh-tunneling.md

Using an SSH tunnel to remotely administer a cloud.gov RDS

Cloud.gov let's you interact with your apps and bound services via SSH.

The cf-service-connect plugin lets you connect to your bound services, and is often your beset choice for interacting with services in cloud.gov. But what if cf-service-connect doesn't work for you (e.g., if you are using a newer version of the cf CLI?

SSH tunneling to the rescue!

Usage

This example will demonstrate this technique for a PostgreSQL DB instance running in cloud.gov. To do this, you'll want to have psql installed locally. If you already have Postgres installed on your machine, you already have this tool. If not, or if you don't want to do a full Postgres install, you can do the following:

~$ brew install libpq

This will give you access to psql, pg_dump and a number of other Postgres tools. Once the installation is complete, add the location of these tools to your PATH variable. A typical install location on Mac is /usr/local/Cellar/libpq/12.2/bin.

You can also use this approach on a MySQL instance by installing the mysql-client locally.

Create a service instance

Create a new Postgres service:

~$ cf create-service aws-rds medium-psql postgrest-example

Once the DB becomes available, generate a service key:

~$ cf create-service-key postgrest-example EXTERNAL-ACCESS-KEY
~$ cf service-key postgrest-example EXTERNAL-ACCESS-KEY

You'll see a response like this:

{
    "db_name": "db-name",
    "host": "db-host",
    "password": "password",
    "port": "5432",
    "uri": "postgres://url",
    "username": "user"
}

If you don't have an app already created that you will bind your service to, you'll need to cf push one. You'll use the app name in the next step.

Set up an SSH tunnel

In a terminal window, set up port forwarding through the app host like this, using a local port and the host value from the previous cf service-key response:

~$ cf ssh -L {local-port}:{service-host}:5432 {your-app-name}

In another terminal window, you can use psql to connect to your bound service, through the SSH tunnel you just set up. You'll access the service with the local port number used in the previous step, and the database name and user name from the cf service-key response.

psql -h localhost -p {local-port} -d {database-name} -U {username} -W 

You can run a local SQL file on your bound service by using the -f flag on the psql utility:

psql -h localhost -p {local-port} -d {database-name} -U {username} -W -f update-db.sql

thank you for this! One quick note - I had to use cf v3-ssh in the port forwarding step, since I'm using a newer version of the cf CLI - not sure if others have run into that, too.

Ah, good call @nick-jones-gov. I originally wrote this up with the older v6 of the CLI. Need to test and update this.