Support SSH tunnel

[oliviertassinari]

Duplicates

• I have searched the existing issues

Latest version

• I have tested the latest version

Summary 💡

Make it possible to connect to a database using a bastion.

Examples 🌈

• Metabase: https://www.metabase.com/docs/latest/databases/ssh-tunnel

  

• TablePlus: https://tableplus.com/blog/2019/08/ssh-tunnel-secure-database-connection.html

• Retool

• https://cloud.airbyte.io/

Motivation 🔦

https://serverfault.com/questions/283172/is-it-safe-to-open-port-3306-in-firewall-for-external-mysql-connection

[oliviertassinari]

I had this problem trying to access the MUI Store MySQL panel. Normally, we would use our Bastion with SSH tunnel. I couldn't so instead, I used https://api.ipify.org?format=json to find Toolpad's IP and whitelisted it to access MySQL.

But on render.com, the IP of the instance is not stable, so I can't easily whitelist the IPs.

---

In pipedream, I'm doing it manually as they don't support it natively:

module.exports = defineComponent({
  props: {
    mysql: {
      type: "app",
      app: "mysql",
    }
  },
  async run({steps, $}) {
    const mysql = require('mysql2/promise');
    const SSH2Promise = require('ssh2-promise');

    const { host, port, username, password, database } = this.mysql.$auth

    const ssh = new SSH2Promise({
      host: bastionHost,
      port: bastionPort,
      username: bastionUsername,
      privateKey: bastionPipedreamPrivateKey,
    });

    const tunnel = await ssh.addTunnel({
      remoteAddr: host,
      remotePort: port,
    })

    const connection = await mysql.createConnection({
      host: 'localhost',
      port: tunnel.localPort,
      user: username,
      password,
      database,
    });

    const [rows] = await connection.execute(
      // Note - manually created orders might not have u.id if created as guest.
      // That's okay, as emailing a customer or reseller AP mailbox isn't helpful
      `SELECT
          post.id as orderID,
          post.post_date as date,`,
      [fromDateString, toDateString]
    );

    await connection.end();
    await ssh.close();

    return {};
  },
})