connecting to remote server and creating sequelize instance

[jamesaspence]

I'm pretty new at ssh tunneling and the like, so pleas excuse my lack of proper terminology / understanding.

I'm attempting to use ssh2 to ssh into a remote server, so I can then create a local database connection to that remote host's database, and insert data into the database.

Here's what I've got so far:

var Client = require('ssh2').Client;

var conn = new Client();
conn.on('ready', function() {
    console.log('Client :: ready');
    conn.forwardOut(
        '127.0.0.1',
        24000,
        '127.0.0.1',
        3306,
        function (err, stream) {
            if (err) {
                throw err;
            }

            console.log(stream);

            var sequelize = new Sequelize(<DATABASE>, <USER>, <PASSWORD>, {
                host: 'localhost',
                dialect: 'mysql',
                pool: {
                    max: 5,
                    min: 0,
                    idle: 10000
                }
            });

            sequelize.query('SELECT * FROM `orders` LIMIT 1',
                {
                    type: sequelize.QueryTypes.SELECT
                }).then(function (results) {
                    console.log(results);
                });
        }
    )
}).connect({
    host: '<IP ADDRESS>',
    port: 22,
    username: <SSH USER>,
    privateKey: require('fs').readFileSync('/path/to/id_rsa')
});

Attempting to connect to sequelize in the code results in a CONNECTION refused error.

I'm just wondering what I'm doing wrong here. My lack of a really strong understanding of what's going on here means it's been difficult to search for answers, so I'm hoping posting this here will be a good start.

[mscdex]

forwardOut() just creates one outgoing connection.

You need to create a TCP server that listens on a local port and calls forwardOut() for every local connection and then pipes between the local socket and the forwarded connection.

For example:

var sshConnected = false;
var conn = new Client();

conn.on('ready', function() {
  sshConnected = true;
  var sequelize = new Sequelize(<DATABASE>, <USER>, <PASSWORD>, {
    host: 'localhost',
    dialect: 'mysql',
    pool: {
      max: 5,
      min: 0,
      idle: 10000
    }
  });
  sequelize.query('SELECT * FROM `orders` LIMIT 1',
    {
      type: sequelize.QueryTypes.SELECT
     }).then(function (results) {
       console.log(results);
   });
}).on('close', function() {
  sshConnected = false;
}).connect({
  // ...
});

net.createServer(function(sock) {
  sock.on('error', function() {});
  if (!sshConnected) return sock.end();
  conn.forwardOut(
    '127.0.0.1',
    sock.remotePort,
    '127.0.0.1',
    3306,
    function (err, stream) {
      if (err) return sock.end();
      stream.pipe(sock).pipe(stream);
    });
}).listen(3306);

[jamesaspence]

That works perfectly! Thanks. Do you have a link where I could learn more about TCP servers and what's going on in this code?

[mscdex]

Well, information on TCP servers (or TCP in general) is available anywhere on google. For information specifically on net, you can read the node documentation here.

ssh2 does not listen on a port for forwarding connections like the way the OpenSSH client does when you use -L xx:nnnn:xx. So creating a TCP server in node and then creating a new SSH forwarded connection for each connection to the TCP server is needed. This way when Sequelize (or even non-node processes) connects to 3306 locally, it will automatically get piped to the MySQL server running on the SSH server.

Since these forwarded connections are being multiplexed over the same SSH connection, you may need to make sure that your pool size does not exceed the maximum number of channels permitted by the server. I think the default is ~10 or so, so I think your current config should work if the SSH server config hasn't changed its default on this particular setting. Otherwise you could modify the TCP server connection handler to spawn a separate SSH connection for each database connection instead of multiplexing.

[jamesaspence]

Not really following the last part. that pool config was something I grabbed from Sequelize's documentation. I'll look more into that to try and understand. Thanks for the help!

[kp96]

@mscdex What if the server (hosted on heroku) has port 3306 already in use?

[mscdex]

@kp96 If you're talking about the bound local port, just change it to something else that isn't being used, or use zero to get a random free port and get the bound port with netServer.address().port once the server is listening.

[kp96]

Ok.I am implementing a rest api using node.js that uses ssh2 package tunneling to connect to the remote mysql database. Here is the code I am using to connect via ssh and to execute query

executeQuery : function(query, callback) {
        var sshConnected = false;
        var connection = require('ssh2').Client();
        var server = require('net').createServer(function(sock) {
            if (!sshConnected) return sock.end();
            connection.forwardOut(
                '127.0.0.1',
                sock.remotePort,
                '127.0.0.1',
                3306,
                function (err, stream) {
                    if (err) return sock.end();
                    stream.pipe(sock).pipe(stream);
                });
        });
        connection.on('ready', function() {
            console.log('Client :: ready');
            sshConnected = true;
            server.listen(3306);
            var sqlconn = require('mysql').createConnection(config.dbOrg);
            sqlconn.connect(function (conError) {
                if (!conError) {
                    console.log(query);
                    sqlconn.query({sql: query.sql, values: query.values}, function (qError, results, fields) {
                        callback(qError, results, fields);
                    });
                } else {
                    callback(response.dbError, null, null);
                }
                server.close();
            });
        }).connect(config.sshConfig);

However, as you can see it is not very efficient.

For every new query, I am establishing the connection again, starting the TCP Server and stopping it after query. Each query takes roughly around 3-4s which is really bad. Any approaches keeping both the ssh connection and the tcp server alive to execute queries as they arrive, would be of great help. Thanks!

[mscdex]

@kp96 In the case of using the database driver directly, if you use mysql2 instead of mysql, you can actually pass the underlying stream/connection to be used by the database driver. This will allow you to skip having to create an additional server that listens for incoming connections to be tunneled. So you can do something like this instead:

var mysql = require('mysql2');
var Client = require('ssh2').Client;

var ssh = new Client();
ssh.on('ready', function() {
  ssh.forwardOut(
    '127.0.0.1',
    12345,
    '127.0.0.1',
    3306,
    function (err, stream) {
      if (err) throw err;
      var sql = mysql.createConnection({
        user: 'foo',
        database: 'test',
        stream: stream // <--- this is the important part
      });
      // use sql connection as usual
  });
}).connect({
  // ssh connection config ...
});

[jomzxgithub]

@mscdex in your sample id do that and it works great. However when i try to do 3 queries at the same time it returns error like packets out of order..

[MihranH]

@mscdex I have tried to implement the code mentioned here
and it works fine when I tried to connect to my stage db which is located on the same server to which I have connected via ssh2,
However when I change srcIp and srcPort to some invalid values it still works and i am able get data from stage db.
here is the code I've tried

conn.forwardOut(
			'test',
			0,			
			'127.0.0.1',
			3306,
			async function (err, stream) {...}

1. What can be reason of such kind of behaviour?

And my main question is`
2.How should I set forwardOut function parameters
if my database is located on another remote server?
So to sum up
I want to connect to the remote server1 via ssh2 and then connect to remote server2 where the DB is located. And the database is accessible only from server1 via login and password.

Here is what I've tried

var sshConnected = false;
var conn = new Client();

conn.on('ready', function() {
  sshConnected = true;
  var sequelize = new Sequelize(<DATABASE>, <USER>, <PASSWORD>, {
    host: 'localhost',
    dialect: 'mysql',
    pool: {
      max: 5,
      min: 0,
      idle: 10000
    }
  });
  sequelize.query('SELECT * FROM `orders` LIMIT 1',
    {
      type: sequelize.QueryTypes.SELECT
     }).then(function (results) {
       console.log(results);
   });
}).on('close', function() {
  sshConnected = false;
}).connect({
  // ...
});

net.createServer(function(sock) {
  sock.on('error', function() {});
  if (!sshConnected) return sock.end();
  conn.forwardOut(
    config.server2,
    3306,
    '127.0.0.1',
    3306,
    function (err, stream) {
      if (err) return sock.end();
      stream.pipe(sock).pipe(stream);
    });
}).listen(3306);

[mscdex]

@MihranH

1. What can be reason of such kind of behaviour?

If you're asking why the server allows such values, I can't answer that as it's implementation-dependent. ssh2 just passes the values as-is to the server. It's possible some implementations may ignore them entirely and use their own values when connecting to the remote host and port.

How should I set forwardOut function parameters
if my database is located on another remote server?

You will need to perform connection hopping if the database is only listening on a loopback interface/unix socket on 'server2'.

[MihranH]

@mscdex as I undertand in connection hopping
there are two ssh connections created, and the first connection is forwarded to 22 port of the second server.
So if I try to implement it in my solution then I need to connect to database server(server2) via ssh.
But when I try to connect to the DB from terminal

1. ssh into server1
2. run mysql -h IP_ADDRESS_OF_SERVER_B -u MYSQL_USERNAME -PPASSWORD

Do I really need the second ssh connection to server2 ?

[mscdex]

I suggested connection hopping because you mentioned:

I want to connect to the remote server1 via ssh2 and then connect to remote server2 where the DB is located.

If the database is only available via localhost on server2, then you would need to connect via ssh from server1 to server2 after connecting to server1 via ssh. As I previously mentioned, if you can access the database running on server2 from server1 directly (e.g. the database is listening for connections on a network interface address instead of localhost), then you don't need to connection hop, you would just use the forwardOut() to connect directly to the database.

[MihranH]

@mscdex So after connecting from my localhost to server1 via ssh, I need to forwardOut() from server1 to server2? And then forwardOut() from server2 to my localhost for creating the connection as I have in my code above?