Is there a way to create connection pool over ssh?

[romanoff]

Is there a way to create pgx connection pool over ssh? I found this example for pq:
https://gist.github.com/vinzenz/7b6b1bf8d0c2b2b1e0d69a15ba9f02c7

[romanoff]

Looks like this should work:

package main

import (
	"fmt"
	"net"
	"os"

	"github.com/jackc/pgx"
	"golang.org/x/crypto/ssh"
	"golang.org/x/crypto/ssh/agent"
)

func main() {

	sshHost := "example.com" // SSH Server Hostname/IP
	sshPort := 22            // SSH Port
	sshUser := "user"        // SSH Username
	sshPass := ""            // Empty string for no password
	dbUser := "user"         // DB username
	dbPass := "dbpass"       // DB Password
	dbHost := "localhost"    // DB Hostname/IP
	dbName := "dbname"       // 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{},
		HostKeyCallback: ssh.InsecureIgnoreHostKey(), //this line
	}

	// 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
		}))
	}

	if sshcon, err := ssh.Dial("tcp", fmt.Sprintf("%s:%d", sshHost, sshPort), sshConfig); err == nil {
		defer sshcon.Close()
		// netconn, err := sshcon.Dial("tcp", fmt.Sprintf("%s:%d", sshHost, sshPort))
		// if err != nil {
		// 	fmt.Println(err)
		// 	return
		// }
		connPoolConfig := pgx.ConnPoolConfig{
			ConnConfig: pgx.ConnConfig{
				Host:     dbHost,
				User:     dbUser,
				Password: dbPass,
				Database: dbName,
				Dial: func(network, addr string) (net.Conn, error) {
					return sshcon.Dial(network, addr)
				},
			},
			MaxConnections: 20, //TODO: we can setup this in config
		}
		pool, err := pgx.NewConnPool(connPoolConfig)
		if err != nil {
			fmt.Println(err)
			return 
		}
		fmt.Printf("Successfully connected to the db\n")
		if rows, err := pool.Query("SELECT id, name FROM holidays ORDER BY id asc"); 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()
		} else {
			fmt.Println(err)
		}

		pool.Close()		
	} else {
		fmt.Println(err)
	}
}

[Carson-JG]

I may be missing something, but I did have issues connecting to an AWS RDS through an EC2 bastion this way.

ssh: tcpChan: deadline not supported

the solution was to implement a wrapper around net.Conn that ignores deadline-related methods.

type noDeadlineConn struct{ net.Conn }

func (c *noDeadlineConn) SetDeadline(t time.Time) error      { return nil }
func (c *noDeadlineConn) SetReadDeadline(t time.Time) error  { return nil }
func (c *noDeadlineConn) SetWriteDeadline(t time.Time) error { return nil }

I then updated the dial function as such:

Dial: func(network, addr string) (net.Conn, error) {
	conn, err := sshConn.Dial(network, addr)
	if err != nil {
		return nil, err
	}
	return &noDeadlineConn{Conn: conn}, nil
},

This seems pretty hacky to me, so I welcome another solution, but this was the most simple one I could come up with.

[d-bytebase]

I'm encountering the same issue "ssh: tcpChan: deadline not supported". Hope there's a standard support.