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()) | |
} | |
} | |
} |
This comment has been minimized.
This comment has been minimized.
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
This comment has been minimized.
GrailFinder commentedAug 21, 2017
maybe it would be right to add
from https://stackoverflow.com/questions/44269142/golang-ssh-getting-must-specify-hoskeycallback-error-despite-setting-it-to-n