Use postgres via SSH in Golang

dial-pq-via-ssh.go

package main

import (
	"database/sql"
	"database/sql/driver"
	"fmt"
	"net"
	"os"
	"time"

	"github.com/lib/pq"
	"golang.org/x/crypto/ssh"
	"golang.org/x/crypto/ssh/agent"
)

type ViaSSHDialer struct {
	client *ssh.Client
}

func (self *ViaSSHDialer) Open(s string) (_ driver.Conn, err error) {
	return pq.DialOpen(self, s)
}

func (self *ViaSSHDialer) Dial(network, address string) (net.Conn, error) {
	return self.client.Dial(network, address)
}

func (self *ViaSSHDialer) DialTimeout(network, address string, timeout time.Duration) (net.Conn, error) {
	return self.client.Dial(network, address)
}

func main() {

	sshHost := "example.com" // SSH Server Hostname/IP
	sshPort := 22            // SSH Port
	sshUser := "ssh-user"    // SSH Username
	sshPass := "ssh-pass"    // Empty string for no password
	dbUser := "user"         // DB username
	dbPass := "password"     // DB Password
	dbHost := "localhost"    // DB Hostname/IP
	dbName := "database"     // Database name

	var agentClient agent.Agent
	// Establish a connection to the local ssh-agent
	if conn, err := net.Dial("unix", os.Getenv("SSH_AUTH_SOCK")); err == nil {
		defer conn.Close()

		// Create a new instance of the ssh agent
		agentClient = agent.NewClient(conn)
	}

	// The client configuration with configuration option to use the ssh-agent
	sshConfig := &ssh.ClientConfig{
		User: sshUser,
		Auth: []ssh.AuthMethod{},
	}

	// When the agentClient connection succeeded, add them as AuthMethod
	if agentClient != nil {
		sshConfig.Auth = append(sshConfig.Auth, ssh.PublicKeysCallback(agentClient.Signers))
	}
	// When there's a non empty password add the password AuthMethod
	if sshPass != "" {
		sshConfig.Auth = append(sshConfig.Auth, ssh.PasswordCallback(func() (string, error) {
			return sshPass, nil
		}))
	}

	// Connect to the SSH Server
	if sshcon, err := ssh.Dial("tcp", fmt.Sprintf("%s:%d", sshHost, sshPort), sshConfig); err == nil {
		defer sshcon.Close()

		// Now we register the ViaSSHDialer with the ssh connection as a parameter
		sql.Register("postgres+ssh", &ViaSSHDialer{sshcon})

		// And now we can use our new driver with the regular postgres connection string tunneled through the SSH connection
		if db, err := sql.Open("postgres+ssh", fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable", dbUser, dbPass, dbHost, dbName)); err == nil {

			fmt.Printf("Successfully connected to the db\n")

			if rows, err := db.Query("SELECT id, name FROM table ORDER BY id"); err == nil {
				for rows.Next() {
					var id int64
					var name string
					rows.Scan(&id, &name)
					fmt.Printf("ID: %d  Name: %s\n", id, name)
				}
				rows.Close()
			}

			db.Close()

		} else {

			fmt.Printf("Failed to connect to the db: %s\n", err.Error())
		}

	}
}

maybe it would be right to add

sshConfig := &ssh.ClientConfig{
		User:            sshUser,
		Auth:            []ssh.AuthMethod{},
		HostKeyCallback: ssh.InsecureIgnoreHostKey(), //this line
}

from https://stackoverflow.com/questions/44269142/golang-ssh-getting-must-specify-hoskeycallback-error-despite-setting-it-to-n

Right, that's a good point :-)

Edit: However then again - Maybe not - It's fair to assume that you should have been connected to that before and it should fail if the host key changes - It's anyway probably more about the fact that this doesn't need a port forwarding process on the target to achieve this functionality

Unfortunately we cannot use sqlx together with this method (driverName is used by sqlx as db type to tune sql parsing)

Unfortunately we cannot use sqlx together with this method (driverName is used by sqlx as db type to tune sql parsing)

Actually you can:

Just use the resulting connection and pass it to sqlx.NewDb like this:

    fmt.Printf("Successfully connected to the db\n")
    dbx := sqlx.NewDb(db, "postgres")
    // If you want to do the same as sqlx.Connect then just call Ping too
    err := dbx.Ping()
    if err != nil {
        // Handle error
    }

@vinzenz Thank you much.

Is there any possible way I can convert this sqlx.NewDb(db, "postgres") to a gorm instance?

Hello @vinzenz , how might one be able to adapt this code to run in a RESTful service? Right now, running it more than once from a handler triggers various connection and SQL errors, even when the whole routine is wrapped in a function and spawned from a Goroutine? Apologies if the answer is very obvious. I am a novice Go programmer, though quite well versed in Java.

Running this on every SQL request multiple times is a bad idea.
Usually you would want to make this a long running connection not even for every session but shared by multiple sessions.
One way could be to run this in a go routine on startup of the application which will also ensure that the connection is established and the connection to the database is alive and then you would communicate with that go routine over channels.

Just to make it clear, this GIST is meant to be a recipe how to use it in a real world application, it just shows the approach how one could establish a database connection over ssh in general, without having to setup port forwarding.

Thanks @vinzenz . I'll wrap the whole thing in a class and then launch it with a goroutine and hit it via channels, as you say. That sounds like a reasonable approach. Am using this for some convenience debugging so is not going to require super high throughput.

Do you know the pq.DialOpen equivalent with pgx ?

@jypelle Found this that might work with pqx: jackc/pgx#382