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,
}
}
localhost:3306as your connection endpoint instead ofremoteserver:3306. Note thatlocalhost:3306is only true iff your SSH tunnel is listening on localhost and on the TCP port 3306. – kostix