Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Use postgres via SSH in Golang
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())
}
}
}
@GrailFinder

This comment has been minimized.

Copy link

GrailFinder commented Aug 21, 2017

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

@vinzenz

This comment has been minimized.

Copy link
Owner Author

vinzenz commented Sep 15, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.