How to connect to MySQL via Standard TCP/IP over SSH using go-sql-driver?

I'm currently using MySQL Workbench on Windows 8.1 to access a remote MySQL database on a Linux server using Standard TCP/IP over SSH. Basically I have the following information:

• SSH Hostname: dbserver.myorg.com:ssh-port

• SSH Username: myRemoteLoginUsername

• SSH Password: (stored in vault)

• SSH Key File: path to a local .ppk file

• MySQL Hostname: 127.0.0.1

• MySQL Server Port: 3306

• Username: myRemoteDbUsername

• Password: (stored in vault)

• Default schema: myRemoteDatabaseName

How can I connect to the database from a Go command application using github.com/go-sql-driver/mysql?

how should my DataSourceName string in the sql.Open statement look like?

    db, err := sql.Open("mysql", <DataSourceName> ) {}

Is there any extra work needed to prepare a working DataSourceName string?

On my Windows PC I have putty installed. I read about tunneling and added a Dynamic tunnel for port 3306 (D3306). I expected this will let me connect using a connection to localhost:3306 and automatically forward the request to the remote db whenever I'm connected to the remote host with putty, but this didn't work as expected either.

I promised to provide my example, here it comes. Basically my solution establishes an ssh tunnel to the remote server and queries the remote database through this tunnel. The ssh tunnel is part of the solution.

The first thing I had to do is to convert my PuTTY .ppk private key file into a valid OpenSSH .pem key file. This can easily be done using the Export feature in PuTTYgen. As I wanted to support password encrypted private keys I also needed a function to decrypt the key and reformat it from its decrypted raw format into a valid format accepted by golang.org/x/crypto/ssh/ParsePrivateKey, which is needed to get the list of signers for authentication.

The solution itself consists of a package contained in two files. The main part of the application is done in main.go which contains all relevant data assignments as well as the code related to the database query. Everything related to ssh tunneling and key handling is contained in sshTunnel.go.

The solution does not provide a mechanismn for a secure password store, nor does it ask for a password. The password is provided in the code. However, it would not be too complicated to implement a callback method for password requests.

Please note: from a performance perspective this is not an ideal solution. It also lacks of proper error handling. I have provided this as an example.

The example is a tested and working example. I developed and used this from a Windows 8.1 PC. The database server is on a remote Linux system. All you need to change is the data and the query part in main.go.

Here is the first part contained in main.go:

// mysqlSSHtunnel project main.go
// Establish an ssh tunnel and connect to a remote mysql server using
// go-sql-driver for database queries. Encrypted private key pem files
// are supported.
//
// This is an example to give an idea. It's far from a performant solution. It 
// lacks of proper error handling and I'm sure it could really be much better 
// implemented. Please forgive me, as I just started with Go about 2 weeks ago.
//
// The database used in this example is from a real Opensimulator installation.
// It queries the migrations table in the opensim database.
//
package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"os"
)

// Declare your connection data and user credentials here
const (
	// ssh connection related data
	sshServerHost     = "test.example.com"
	sshServerPort     = 22
	sshUserName       = "tester"
	sshPrivateKeyFile = "testkey.pem" // exported as OpenSSH key from .ppk
	sshKeyPassphrase  = "testoster0n" // key file encrytion password

	// ssh tunneling related data
	sshLocalHost  = "localhost"	// local localhost ip (client side)
	sshLocalPort  = 9000		// local port used to forward the connection
	sshRemoteHost = "127.0.0.1" // remote local ip (server side)
	sshRemotePort = 3306        // remote MySQL port

	// MySQL access data
	mySqlUsername = "opensim"
	mySqlPassword = "h0tgrits"
	mySqlDatabase = "opensimdb"
)

// The main entry point of the application
func main() {
	fmt.Println("-> mysqlSSHtunnel")

	tunnel := sshTunnel() // Initialize sshTunnel
	go tunnel.Start()     // Start the sshTunnel

	// Declare the dsn (aka database connection string)
	// dsn := "opensim:h0tgrits@tcp(localhost:9000)/opensimdb"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s",
		mySqlUsername, mySqlPassword, sshLocalHost, sshLocalPort, mySqlDatabase)

	// Open the database
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		dbErrorHandler(err)
	}
	defer db.Close() // keep it open until we are finished

	// Simple select query to check migrations (provided here as an example)
	rows, err := db.Query("SELECT * FROM migrations")
	if err != nil {
		dbErrorHandler(err)
	}
	defer rows.Close()

	// Iterate though the rows returned and print them
	for rows.Next() {
		var version int
		var name string
		if err := rows.Scan(&name, &version); err != nil {
			dbErrorHandler(err)
		}
		fmt.Printf("%s, %d\n", name, version)
	}
	if err := rows.Err(); err != nil {
		dbErrorHandler(err)
	}

	// Done for now
	fmt.Println("<- mysqlSSHtunnel")
}

// Simple mySql error handling (yet to implement)
func dbErrorHandler(err error) {
	switch err := err.(type) {
	default:
		fmt.Printf("Error %s\n", err)
		os.Exit(-1)
	}
}

Now the second part in sshTunnel.go:

// mysqlSSHtunnel project sshTunnel.go
//
// Everything regarding the ssh tunnel goes here. Credits go to Svett Ralchev.
// Look at http://blog.ralch.com/tutorial/golang-ssh-tunneling for an excellent
// explanation and most ssh-tunneling related details used in this code.
//
// PEM key decryption is valid for password proected SSH-2 RSA Keys generated as
// .ppk files for putty and exported as OpenSSH .pem keyfile using PuTTYgen.
//
package main

import (
	"bytes"
	"crypto/x509"
	"encoding/base64"
	"encoding/pem"
	"fmt"
	"golang.org/x/crypto/ssh"
	"io"
	"io/ioutil"
	"net"
)

// Define an endpoint with ip and port
type Endpoint struct {
	Host string
	Port int
}

// Returns an endpoint as ip:port formatted string
func (endpoint *Endpoint) String() string {
	return fmt.Sprintf("%s:%d", endpoint.Host, endpoint.Port)
}

// Define the endpoints along the tunnel
type SSHtunnel struct {
	Local  *Endpoint
	Server *Endpoint
	Remote *Endpoint
	Config *ssh.ClientConfig
}

// Start the tunnel
func (tunnel *SSHtunnel) Start() error {
	listener, err := net.Listen("tcp", tunnel.Local.String())
	if err != nil {
		return err
	}
	defer listener.Close()

	for {
		conn, err := listener.Accept()
		if err != nil {
			return err
		}
		go tunnel.forward(conn)
	}
}

// Port forwarding
func (tunnel *SSHtunnel) forward(localConn net.Conn) {
	// Establish connection to the intermediate server
	serverConn, err := ssh.Dial("tcp", tunnel.Server.String(), tunnel.Config)
	if err != nil {
		fmt.Printf("Server dial error: %s\n", err)
		return
	}

	// access the target server
	remoteConn, err := serverConn.Dial("tcp", tunnel.Remote.String())
	if err != nil {
		fmt.Printf("Remote dial error: %s\n", err)
		return
	}

	// Transfer the data between  and the remote server
	copyConn := func(writer, reader net.Conn) {
		_, err := io.Copy(writer, reader)
		if err != nil {
			fmt.Printf("io.Copy error: %s", err)
		}
	}

	go copyConn(localConn, remoteConn)
	go copyConn(remoteConn, localConn)
}

// Decrypt encrypted PEM key data with a passphrase and embed it to key prefix
// and postfix header data to make it valid for further private key parsing.
func DecryptPEMkey(buffer []byte, passphrase string) []byte {
	block, _ := pem.Decode(buffer)
	der, err := x509.DecryptPEMBlock(block, []byte(passphrase))
	if err != nil {
		fmt.Println("decrypt failed: ", err)
	}
	encoded := base64.StdEncoding.EncodeToString(der)
	encoded = "-----BEGIN RSA PRIVATE KEY-----\n" + encoded +
		"\n-----END RSA PRIVATE KEY-----\n"
	return []byte(encoded)
}

// Get the signers from the OpenSSH key file (.pem) and return them for use in
// the Authentication method. Decrypt encrypted key data with the passphrase.
func PublicKeyFile(file string, passphrase string) ssh.AuthMethod {
	buffer, err := ioutil.ReadFile(file)
	if err != nil {
		return nil
	}

	if bytes.Contains(buffer, []byte("ENCRYPTED")) {
		// Decrypt the key with the passphrase if it has been encrypted
		buffer = DecryptPEMkey(buffer, passphrase)
	}

	// Get the signers from the key
	signers, err := ssh.ParsePrivateKey(buffer)
	if err != nil {
		return nil
	}
	return ssh.PublicKeys(signers)
}

// Define the ssh tunnel using its endpoint and config data
func sshTunnel() *SSHtunnel {
	localEndpoint := &Endpoint{
		Host: sshLocalHost,
		Port: sshLocalPort,
	}

	serverEndpoint := &Endpoint{
		Host: sshServerHost,
		Port: sshServerPort,
	}

	remoteEndpoint := &Endpoint{
		Host: sshRemoteHost,
		Port: sshRemotePort,
	}

	sshConfig := &ssh.ClientConfig{
		User: sshUserName,
		Auth: []ssh.AuthMethod{
			PublicKeyFile(sshPrivateKeyFile, sshKeyPassphrase)},
	}

	return &SSHtunnel{
		Config: sshConfig,
		Local:  localEndpoint,
		Server: serverEndpoint,
		Remote: remoteEndpoint,
	}
}

Well, you could do that "full Go", I think.

The SSH part and port-forwarding

I'd start with something like this (I failed to google a better example).

Note two problems with this code:

1. It's not actually correct: it connects to a remote socket
   before accepting a client connection
   while it should do the reverse: accept a client connection to
   a port-forwarded local socket then use the active SSH session to connect
   to the remote socket and if this succeeds, spawn two goroutines to shovel
   the data between those two sockets.

2. When configuring the SSH client, it explicitly allows password-based
   authentication for unknown reason. You don't need this as you're using
   pubkey-based auth.

An obstacle which might trip you there is managing an access to your SSH key.
The problem with it is that a good key should be protected by a passphrase.

You say the key's password is "stored in valut" and I honestly have no idea what "the valut" is.

On the systems I use, an SSH client either asks for the password to decrypt the key or work with a so-called "SSH agent":

• On Linux-based systems it's typically an OpenSSH's ssh-agent binary working in the background which is accessed via a Unix-domain socket, and located by inspecting an environment variable named SSH_AUTH_SOCK.
• On Windows I use PuTTY, and it has its own agent, pageant.exe.
  I'm don't know which means PuTTY SSH client uses to locate it.

To access the OpenSSH agent, golang.org/x/crypto/ssh provides the agent subpackage which can be used to locate the agent and communicate with it.
If you need to obtain keys from pageant, I'm afraid you'll need to figure out what protocol that uses and implement it.

The MySQL part

The next step would be to integrate this with go-sql-driver.

I'd start the simplest way possible:

1. When you have your SSH port forwarding working,
   make it listen for incoming connections on a random port on localhost.
   When the connection is opened, get the port from the returned connection
   object.
2. Use that port number to constuct the connection string to pass to an instance of sql.DB you will create to use go-sql-driver.

The driver will then connect to your port-forwarded port, and your SSH layer will do the rest.

After you have this working, I'd explore whether the driver of your choice
allows some more fine-grained tweaking like allowing you to directly pass it an instance of io.ReadWriter (an opened socket) so that you could skip the port-forwarding setup entirely and just produce new TCP connections forwarded through SSH, that is, skip the "listening locally" step.