Best practices for accessing a custom DB via an SSH tunnel

Help
#1

Hi,

I’m trying to setup Auth0 with a database we host on AWS (their RDS service).

To access the databases we need to setup an SSH Tunnel. I’m wondering what the best practice for this is? I’ve seen this thread, which suggests using a node package called ssh2.

First of all, can I confirm there is no inbuilt way for Auth0 to access a postgres DB via an SSH tunnel? I notice the thread is a few years old

Secondly if this is recommended practice, where would I attach the private key we’d be using to establish the tunnel?

Thanks in advance,
Mark

1 Like
#2

Hi Mark,

That sounds like the right approach - you need a package to do the ssh part.

The keys/certs should be stored in the rules configuration, not in the rules.

I did this (or something similar) a couple of years ago, I’ve since lost the code. But you are on the right track.

John

2 Likes
#3

Thanks for the confirmation John,

All the best,
Mark

#4

hey guys, I’m in the same situation.
Do you mind sharing your custom DB code?
I tried using the ssh2 package but it complains that it cannot read the SSH private key that I saved in the custom DB settings (so it’s reading it from a variable instead of parsing a file as would be normal). Is there any trick with formatting the key when saving it?
Thanks

#5

I solved the key coding issue by encoding to base64 as suggested here

I tried to run some code but it always times out
Here’s the code:


function login(email, password, callback) {

    const { Client } = require('ssh2');
    const conn = new Client();
    
    conn.on('ready', () => {

        var mysql = require('mysql');
        var bcrypt = require('bcrypt');

        var connection = mysql.createConnection({
            host: configuration.DB_HOST,
            user: configuration.DB_USER,
            password: configuration.DB_PASS,
            database: configuration.DB_NAME
        });

        connection.connect(function(err) {
            if (err) 
                return console.error('error: ' + err.message);

            console.log('Connected to the MySQL server.');
        });

        //var hasher = require('wordpress-hash-node');

        var query = "SELECT id, email, password FROM users WHERE email = ?";

        connection.query(query, [email], (err, results) => {

            if (err)  
                return callback(err);

            if (results.length === 0)
                return callback(new WrongUsernameOrPasswordError(email));

            var user = results[0];

            bcrypt.compare(password, user.password, function (err, isValid) {

                if (err) {
                //if (!hasher.CheckPassword(password, user.password)) {
                    callback(err);
                } else if (!isValid) {
                    callback(new WrongUsernameOrPasswordError(email));
                } else {
                    // success
                    callback(null, {
                        // This prefix (replace with your own custom DB name)
                        // ensure uniqueness across different custom DBs if there's the
                        // possibility of collisions (e.g. if the user ID is an email address or an integer)
                        id: 'my_conn_ID|' + user.id.toString(),
                        email: user.email
                    });
                }
            });
        });

        connection.end(function(err) {
            if (err)
                return console.log('error:' + err.message);

            console.log('Close the database connection.');
        });

    }).connect({
        host: configuration.EC2_HOST,
        port: configuration.EC2_SSH_PORT,
        username: configuration.EC2_USER,
        privateKey: Buffer.from(configuration.EC2_SSH_PKEY, 'base64').toString('utf8')
    });
}

on the other hand, this test code works, so I think that the ssh connection is ok (?). I’m not familiar with nodeJs (I know jquery…). I’d appreciate some help diagnosticating this…

function login(email, password, callback) {

    const { Client } = require('ssh2');
    var conn = new Client();
    
    conn.on('ready', () => {

        callback(null, {
            id: 'my_conn_ID|1',
            email: 'theuser@email.com'
        });

    }).connect({
        host: configuration.EC2_HOST,
        port: configuration.EC2_SSH_PORT,
        username: configuration.EC2_USER,
        privateKey: Buffer.from(configuration.EC2_SSH_PKEY, 'base64').toString('utf8')
    });
}