How to use ssh2 with node-postgres

[BrandonCopley]

I am trying to redirect my app traffic (10's of ip's) into only 1, I should be able to use a tunnel, but I'm noticing that if I try to ssh tunnel and then call pg, pg doesn't know how to handle this and requests are STILL coming from my local server.

[BrandonCopley]

c.connect({
    host : '192.168.1.1',
    port : 22,
    username : 'ubuntu',
    privateKey : require('fs').readFileSync('./ssh_keys/my_key')
  });
  c.on('connect', function() {
    console.log('Connection :: connect');
  });
  c.on('ready', function() {
    console.log('Connection :: ready');
    var pg = require('pg');
    var conString = "postgres://user:password@host:5432/postgres";
    var client = new pg.Client(conString);
    client.connect(function(err) {

    });
  });

[mscdex]

The issue here is that when creating a remote outbound connection (forwardOut()), the TCP stream is passed to the callback.

So, the 'pg' module here needs to be able to accept a stream instead of connecting itself. Or you can create a TCP proxy server (for 'pg' to connect to) that on connection, will call fowardOut() and pipe to the incoming socket and back. The latter might look something like:

var pg = require('pg'),
    ssh2 = require('ssh2');
var pgHost = 'localhost', // remote hostname/ip
    pgPort = 5432,
    proxyPort = 9090,
    ready = false;

var proxy = require('net').createServer(function(sock) {
  if (!ready)
    return sock.destroy();
  c.forwardOut(sock.remoteAddress, sock.remotePort, pgHost, pgPort, function(err, stream) {
    if (err)
      return sock.destroy();
    sock.pipe(stream);
    stream.pipe(sock);
  });
});
proxy.listen(proxyPort, '127.0.0.1');

var c = new ssh2();
c.connect({
  host : '192.168.1.1',
  port : 22,
  username : 'ubuntu',
  privateKey : require('fs').readFileSync('./ssh_keys/my_key')
});
c.on('connect', function() {
  console.log('Connection :: connect');
});
c.on('ready', function() {
  ready = true;
  var conString = 'postgres://user:password@127.0.0.1:' + proxyPort + '/postgres',
      client = new pg.Client(conString);
  client.connect(function(err) {
    // ....
  });
});

[BrandonCopley]

This worked perfectly. I set it up so I can have cycling ports and connect
to multiple databases, this looks great!

[alesak23]

still works great in 2020!

[andreikho]

This just made my day, spent the last 24h banging the wall haha.
Still works great in 2021!

[salvadorplj]

If you need to work async you should defenitely look into using ssh2-promise instead.

All you'd need is something like this:

    let sshConnectionString = {
      host: '123.123.123.123', // bastionip,
      port: 22, // bastionport
      username: 'username', // bastionuser
      privateKey: key // bastionkey
    }

    // Create ssh connection and tunnel the target server through it
    var sshConn = new SSH2Promise(sshConnectionString);
    await sshConn.connect();
    await sshConn.addTunnel({remoteAddr: fqdn, remotePort: port}).then((tunnel) => {
      port = tunnel.localPort; //Local port 
      fqdn = '127.0.0.1';
    });
  }

[renschler]

thanks @mscdex ! this was a big help

[CaliforniaLuv]

In 2022, this code still works 😁
Thanks to you, I lived in my company.

[jamie-legg]

Still works in 2023. Thank you.

[amsanket22]

https://github.com/amsanket22/ssh-pg there is the repo you can use, just set up the env and ready to go