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 |
This comment has been minimized.
This comment has been minimized.
Unfortunately we cannot use sqlx together with this method (driverName is used by sqlx as db type to tune sql parsing) |
This comment has been minimized.
This comment has been minimized.
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
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
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