tunnel crystal-pg through ssh

a_ssh_pg_tunnel.cr

require "pg"
require "ssh2"

# Add an initlizer that directly sets the socket
class PQ::Connection
  def initialize(@soc, @conninfo)
  end
end

# Add a new crystal-pg database context that can store a proc that returns a
# socket for the new PQ::Connection initalizer
class DB::TunneledDatabase < DB::Database
  getter setup_tunnel : Proc(IO)

  def initialize(@setup_tunnel : Proc(IO), driver : Driver, uri : URI)
    super(driver, uri)
  end
end

module PG
  # Add a new connection class that is mostly the same as PG::Connection,
  # except it calls the setup tunnel proc to get the socket
  class TunneledConnection < Connection
    def initialize(@context)
      @prepared_statements = context.prepared_statements?
      @connection = uninitialized PQ::Connection

      begin
        conn_info = PQ::ConnInfo.new(context.uri)
        channel = context.setup_tunnel.call                  # <=== new line
        @connection = PQ::Connection.new(channel, conn_info) # <=== changed line
        @connection.connect
      rescue ex
        raise DB::ConnectionRefused.new(cause: ex)
      end
    end
  end

  # Add a new crystal-db driver for the new connection class
  class TunneledDriver < ::DB::Driver
    def build_connection(context : ::DB::ConnectionContext) : Connection
      TunneledConnection.new(context)
    end
  end
end



# ssh into the postgres server 
session = SSH2::Session.connect("p.someid.db.postgresbridge.com", 22)
session.login_with_pubkey "username", "./key", "./key.pub"

# create a proc that returns an IO talking to the postgres socket
connect_proc = -> do
  channel = session.open_session
  channel.command("nc -U /var/run/postgresql/.s.PGSQL.5432")
  channel.as IO
end


# instead of DB.open, start the connection manually since that's the only way
# to pass in the new proc
db = DB::TunneledDatabase.new(
  setup_tunnel: connect_proc,
  driver: PG::TunneledDriver.new,
  # the host part is ignored, but user and database name all work normally
  uri: URI.parse("postgres://my_pg_user@whatever/db_name")
)

p db.query_one("select current_user || now()", &.read)
db.close

shard.yml

name: sshpg
version: 0.1.0


dependencies:
  pg:
    github: will/crystal-pg
    commit: cafe599
  ssh2:
   github: spider-gazelle/ssh2.cr