Tunneling DBD::mysql connections over SSH without using external programs

[wwinfrey]

I am building a specialized client application, and need to make an encrypted connection to a remote MySQL server. The MySQL server I am connecting to is running on a Linux appliance where my ability to change the configuration is limited, thus the following caveats:

1. Encrypting the connection with SSL is not an option. The remote MySQL server that I am connecting to is not configured to accept SSL-encrypted connections and I cannot change this without extensive, intrusive bootstrapping on the remote appliance (bootstrapping that I am trying to avoid at all costs).

2. While Perl is installed on the appliance I am connecting to (it's running CentOS 5.5), the perl-DBD-MySQL package is not installed on the remote appliance, so using DBD::Gofer(*) or other similar approaches to proxy the connection over SSH isn't an option without bootstrapping the appliance to some degree, which, as I mentioned, I would like to avoid.

   (*) At least, I don't think it's an option; my experience with DBD::Gofer is limited.

3. The client application needs to be as portable as possible, and I would like to forego any dependencies on external applications. Put another way, I'm OK with compiling libssh2 into a module, but would like to avoid using an ssh client binary, socat, or the like. At least, what I don't want to be doing is creating tunnels using exec() calls to these binaries.

4. Just so we're clear as to what kind of SSH environment is configured on the remote appliance, the ssh environment is the same as one might find in a default CentOS 5.5 install.

Now, I have tunneled MySQL connections over SSH in the past plenty of times and am fairly familiar with SSH tunneling in general, but I cannot seem to figure out how to do this in a "pure Perl" manner.

I've read a couple different threads here on PerlMonks (most notably: here, here and here) that broach the subject, but don't seem to even point in the general direction of an answer. I've also looked for similar questions about tunneling in general, and have looked at say, the Tunnels section of the Net::OpenSSH perldoc, but can't wrap my head around how I would create a driver handle that connects over the pipes created by Net::OpenSSH's open_ex method...

I've also looked at modules like IO::Select and IO::Socket::INET, but can't really seem to wrap my head around how I might use them in furtherance of my afore-stated goals.

Any suggestions?

Update I was looking for a Perl module that would allow for SSH port forwards without the use of external apps such as /usr/bin/ssh or plink.

There are two modules that implement SSH client functionality completely "in" the module (that is to say, without acting as a wrapper for external binaries): Net::SSH::Perl/Net::SSH::W32Perl and Net::OpenSSH.

Net::SSH::Perl, as Krambambuli points out does not support SSH's "LocalForward" option. Net::OpenSSH supports SSH tunneling, but this is not the same as port forwarding, which is what I really wanted, and since the module I'm writing will need to support Windows, Net::OpenSSH is not an option here anyway.

In summary, as far as I can tell, there is no way to do SSH port forwarding using only Perl modules and without external binaries, not just in Windows, but on any platform. There are plenty of good options for accomplishing SSH port forwarding using external binaries, options that will likely suffice for most users, however, for my purposes, using external binaries wasn't an option.

As far as the solution to my own problem went, I got permission from the higher-ups to do a moderate amount of boostrapping on the target appliance so that the appliance's MySQL server will support SSL-encrypted connections, thereby eliminating my need to do any SSH port forwarding.

[Corion]

This is not a Perl solution, but if the remote end has an ssh daemon running, you can use SSH tunneling to connect to the remote database:

ssh -fNg -L 6603:127.0.0.1:3306 [e-mail]

Then you connecto to localhost:6603, and the communication should get forwarded to the remote machine.

To do this transparently from Perl, I would launch ssh in the background like this:

my $ssh_pid= open("ssh -fNg -L 6603:127.0.0.1:3306 remote_user@mysql.e
+xample.com |")
    or die;

my $dbh= DBI->connect("dbd:mysql...");

...

# tear down the connection
kill -9 => $ssh_pid;

[wwinfrey]

Right, I am very familiar with that technique. However, if you'll note from my post, my design goals dictate that I establish the tunneled connection without the use of exec() or open() calls to system binaries like the SSH client binary (/usr/bin/ssh) or socat.

I am not looking for how to do this from the shell; I am looking to do this completely from within the confines of my Perl application, mostly for portability issues, but also because this "application" I'm working on will be implemented as a custom module in an API I am designing.

Dependence on external programs in this situation seems sloppy and hard to maintain. I am looking for my fellow developers to simply "use" my module and not have to worry about installing anything else besides other Perl modules.

[salva]

Then, there is only a realistic way to do that: write your own ssh implementation using Net::SSH2 able to redirect connections on a TCP port to the remote MySQL port trough SSH.

Then in your module run this proxy in a new thread or process.

[Krambambuli]

Whereas Net::SSH::Perl seems the first natural choice, it looks like that module isn't ready for tunneling. Although there is a way to specify a number of options with the constructor,

"Net::SSH::Perl::Config understands a subset of the configuration directives that can live in these files; this subset matches up with the functionality that Net::SSH::Perl can support. Unknown keywords will simply be skipped."

LocalForward is _not_among the known options, so...

Other possible choices could be Net::SSH, Net::SSH2 or Net::SSH::Tunnel. However, these are just wrappers over the sytem-installed ssh and not pure Perl solutions, so if they 'hide the underlying system' enough or not for you remains your decision.

Maybe Net-OpenSSH-0.60 would work - but I haven't tried.


Krambambuli
---

[wwinfrey]

Net::OpenSSH looks like a prime candidate. In my original post, I wrote:

I've also looked for similar questions about tunneling in general, and have looked at say, the Tunnels section of the Net::OpenSSH perldoc, but can't wrap my head around how I would create a driver handle that connects over the pipes created by Net::OpenSSH's open_ex method...

So really, I suppose my entire post could have been distilled down to "How do you make DBI connect with DBD::mysql over Net::OpenSSH tunnels?" but I did not want to necessarily dis-avail myself of other possible solutions. I shouldn't have been so quick to dismiss the VPN solution out-of-hand, especially seeing that is, as Salva alluded to above, what Net::OpenSSH's perldoc "Tunnels" section is referring to.

However, these are just wrappers over the sytem-installed ssh and not pure Perl solutions, so if they 'hide the underlying system' enough or not for you remains your decision.

In the absence of any other viable solution, I suppose I may have to settle for something in this approach. The reason I'm hesitant to use wrappers for system binaries is because I'd like to wrap this up in an easily-distributed module to my local team of co-devs, and did I mention the target OS will eventually be Windows?

[wwinfrey]

Looks like Net::OpenSSH isn't supported on Windows, so that's out. Probably should have checked that requirement beforehand. Tunneling and VPNs likely would not have worked here either because, Windows.

I suppose I'll have to find another path.

[sundialsvc4]

May I cordially suggest that this is a perfect application for VPN ... whether you do it in local software or even in the built-in VPN capability of a modern router device.

Simply establish a secure tunnel to the target system, preferably using digital certificates, and the entire problem goes away.   The two systems simply talk to one another, over what appear to each of them to be a “local” connection, and .. mirabile dictu! .. the connection is secure!   Courtesy of a third-party agent that is quite unknown to (and therefore, no longer a concern to) either of them.   Priceless!™

[wwinfrey]

I've updated my original post with my conclusions.

Thank you everyone for sharing your insights. Even though I was not able to find exactly what I was looking for I did come away much more informed about the capabilities and limitations of the SSH modules on CPAN.

[sundialsvc4]

May I cordially suggest that this is a perfect application for VPN ... whether you do it in local software or even in the built-in VPN capability of a modern router device.

Simply establish a secure tunnel to the target system, preferably using digital certificates, and the entire problem goes away.   The two systems simply talk to one another, over what appear to each of them to be a “local” connection, and .. mirabile dictu! .. the connection is secure!   Courtesy of a third-party agent that is quite unknown to (and therefore, no longer a concern to) either of them.   They no longer have to take any special steps at all to secure the connection ... they may simply take for granted that it is. “Priceless!”™

[wwinfrey]

I agree that a VPN would help certainly fix the problem, but again - I have a design requirement that states that there can be no external dependencies. The solution must live completely within the Perl code.

Another restriction is that I am trying to avoid any bootstrapping of the remote system, and using a VPN would defeat this requirement as well.

[salva]

OpenSSH has native support for creating VPNs (no external programs required). Though it must be enabled on the configuration and requires root access.