duanjue2576
duanjue2576
2016-03-16 17:14

Golang SSH隧道连接到远程Postgres DB

已采纳

I have looked at various resources. I'm not sure how to accomplish this task. I can connect locally no problem, but I cannot connect to the remote easily. I need to pass on an RSA .pem key, and I'm not quite sure how to do this w/o forcing a connection unsecured

package main

import (
    "database/sql"
    "fmt"
    "os"

    _ "github.com/lib/pq"
)

var (
    dbUser  = os.Getenv("DB_USER")
    dbPass  = os.Getenv("DB_PASS")
    dbName  = os.Getenv("DB_NAME")
    dbHost  = os.Getenv("DB_HOST")
    dbPort  = os.Getenv("DB_PORT")
    sslMode = os.Getenv("SSLMODE")
)

// ConnectDb is a short cut function that takes parameters through
// CLI that returns a pointer to a sql.DB connection.
// It takes no arguments.
func ConnectDb() (*sql.DB, error) {

    db, err := sql.Open("postgres", getDbInfo())
    CheckErr(err, "Unable to connecto tthe DB")

    if err := db.Ping(); err != nil {
        return nil, err
    }

    return db, nil
}

func getDbInfo() string {
    var dbInfo string

    if dbName != "" {
        dbInfo += fmt.Sprintf("dbname=%s ", dbName)
    } else {
        dbInfo += fmt.Sprintf("dbname=%s ", "development")
    }

    // checks for nil value
    if dbUser != "" {
        dbInfo += fmt.Sprintf("dbuser=%s ", "user")
    }

    // checks for nil value
    if dbPass != "" {
        dbInfo += fmt.Sprintf("dbpass=%s ", dbPass)
    }

    if sslMode != "" {
        dbInfo += fmt.Sprintf("sslmode=%s", sslMode)
    } else {
        dbInfo += fmt.Sprintf("sslmode=disable")
    }

    return dbInfo
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongli4711 dongli4711 5年前

    It is my understanding that you need to open connection to postgre database. I don't know if a native postgre ssh tunneling support exists. So, this answer about SSH tunneling to DB machine.

    I have not tested postgre this way, but I have had this model used in some proprietary server connections.

    The process goes like that:

    1. open ssh connection to db machine
    2. establish tunnel from local port to remote db port
    3. open db connection on local port

    You can accomplish #1 and #2 with ssh client like OpenSSH or putty. You should probably do that 1st. If external client works, then you can attempt to put it all into go language code without external SSH client.

    In go you would use

    "golang.org/x/crypto/ssh"

    package.

    There are tutorials out there how to use GO ssh tunneling. Below is not a tested sample without error checking:

    var buffer []byte
    var err error
    buffer, err = ioutil.ReadFile(sshKeyFile)
    var key ssh.Signer
    key, err = ssh.ParsePrivateKey(buffer)
    var authMethod ssh.AuthMethod
    authMethod = ssh.PublicKeys(key)
    sshConfig = &ssh.ClientConfig{
        User: "user_id",
        Auth: []ssh.AuthMethod{authMethod},
    }
    conn, err := ssh.Dial("tcp", endpoint, sshConfig)
    // open connection on postgre:
    dbConn, err = conn.Dial("tcp", dbEndpoint)
    

    The last line above is not exactly tunneling, but an TCP connection open to DB server. You might be able to pass that connection into db library. If not, you would have to setup a tunnel.

    点赞 评论 复制链接分享