Golang SSH隧道连接到远程Postgres DB

Question

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
}

Answer 1

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.