dongyanzhui0524
dongyanzhui0524
2015-11-16 17:45
浏览 288

如何使用go-sql-driver通过SSH通过标准TCP / IP连接到MySQL?

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • drxrgundk062317205
    drxrgundk062317205 2015-11-17 18:30
    已采纳

    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.

    点赞 评论
  • drrhr20884
    drrhr20884 2015-11-29 21:12

    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
    ", 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
    ", 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
    ", err)
            return
        }
    
        // access the target server
        remoteConn, err := serverConn.Dial("tcp", tunnel.Remote.String())
        if err != nil {
            fmt.Printf("Remote dial error: %s
    ", 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-----
    " + encoded +
            "
    -----END RSA PRIVATE KEY-----
    "
        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,
        }
    }
    
    点赞 评论

相关推荐