Solved

SSH Tunnel connection fails when using JDBC driver

jstanger
Badge +1

I am utilizing FME Cloud (Server 2020) to run a regular ETL pipeline from a Firebird database using their Jaybird JDBC driver. Working with a local instance and FME Desktop seems to have no issues with connecting and running SQL statements.

The database cannot have external ports opened due to sensitive data being stored within it. However, we are able to create an SSH tunnel to a jump server that is otherwise locked down which FME Cloud could then use to create a connection. Needing to keep a secure encrypted connection throughout the entire path I have managed to load some external Python libraries and establish a temporary SSH tunnel from the FME Cloud instance to the jump server for the duration of the workbench execution. I have tested this connection by using the Firebird Python library in a PythonCaller to execute an SQL statement successfully.

However, when I try to use the SQLCreator or SQLExecutor with the already established to work JDBC driver with this local SSH tunnel it fails to complete the SQL statement. Using a non-sensitive version of Firebird hosted on my own machine I was able to use the SQLCreator to execute an SQL statement on the FME Cloud instance where it was directly addressing the remote port that had been temporarily opened on the jump server.

TL/DR this JDBC driver only fails if it tries to use the SSH tunnel created by Python between the Cloud instance and the jump server but works under all other conditions. And the SSH tunnel does work if a non-JDBC method is used.

Is there anything about how FME Cloud EC2 container is configured or how FME Server uses JDBC connections that might be causing this frustrating situation. I really don't want to create a custom transformer in Python just for Firebird.

icon

Best answer by jstanger 7 July 2020, 13:16

View original

12 replies

ravenkopelman
Rank
Badge

Can you share the Python libraries used to create the tunnel? I wonder if the tunnel is only available to Python libraries... you could test that theory by checking if an HTTPCaller (pure C++) will use the tunnel.

jstanger
Badge +1

Can you share the Python libraries used to create the tunnel? I wonder if the tunnel is only available to Python libraries... you could test that theory by checking if an HTTPCaller (pure C++) will use the tunnel.

Yes no problem, I'm using sshtunnel which is based on paramiko which in turn also uses cryptography (newer version than FME uses by default, so needed to temporarily modify the sys.path to load a newer version of cryptography first) fairly extensively.

I have actually used this Python tunnel with the JDBC driver on FME Desktop in my initial testing on my local machine. So the local testing process goes, start first python script to set the remote port tunnel from the DB, start a second python script to set the local port tunnel to the jump servers remote port, start FME workbench and connection + SQL execution and response goes fine. So I'm pretty sure the Python tunnel is equivalent to opening one using PuTTY/bash ssh.

I've also done this locally with asyncssh but had more trouble with getting that to work on FME Cloud, I think due to package conflicts (I haven't gone back and tried with the temporary sys.path trick yet).

ravenkopelman
Rank
Badge

Yes no problem, I'm using sshtunnel which is based on paramiko which in turn also uses cryptography (newer version than FME uses by default, so needed to temporarily modify the sys.path to load a newer version of cryptography first) fairly extensively.

I have actually used this Python tunnel with the JDBC driver on FME Desktop in my initial testing on my local machine. So the local testing process goes, start first python script to set the remote port tunnel from the DB, start a second python script to set the local port tunnel to the jump servers remote port, start FME workbench and connection + SQL execution and response goes fine. So I'm pretty sure the Python tunnel is equivalent to opening one using PuTTY/bash ssh.

I've also done this locally with asyncssh but had more trouble with getting that to work on FME Cloud, I think due to package conflicts (I haven't gone back and tried with the temporary sys.path trick yet).

Very interesting! Now I understand why you asked about this from an FME Cloud perspective.

Too far outside my area of expertise for me to effective I'm afraid.

jstanger
Badge +1

Very interesting! Now I understand why you asked about this from an FME Cloud perspective.

Too far outside my area of expertise for me to effective I'm afraid.

Yeah, pretty excited to write this up once I'm done. But I'm fairly confident I've narrowed it down to something about the combination of JDBC and FME Cloud.

jstanger
Badge +1

Ok, further experimentation with the alternative asyncssh which forced me to learn quite a bit about threads and asyncio in Python has revealed that if an FME Engine starts an SSH tunnel then pauses until the task is manually canceled then another Engine running an SQLCreator with the JDBC driver can execute successfully.

However, even if I managed to get the asyncssh tunnel running on a parallel thread, which a Python library can then use to execute and return SQL to the FME Environment to pass to a Logger, and running a subprocess that successfully tests the TCP tunnel via bash, still the SQLCreator hangs if it tries to use the tunnel. Now requiring a reboot of the FME Server as the secondary thread doesn't get correctly killed.

I'm now fairly certain that something deeper in how FME Engines are implemented is getting in the way. At this stage I'd really like any insight an FME dev might have.

jstanger
Badge +1

Alright, I've done some more testing and found that using asyncssh or sshtunnel to use Python to create a tunnel for a PostgreSQL connection and using either an SQLCreator or a Reader that is still hangs. So it doesn't seem to be a case of the JDBC driver but rather how the transformer interacts with the Python tunnel.

So Python to Python tunnel seems to work fine. Python tunnel created by a different Engine seems to work fine. But anytime the tunnel is opened by the same Engine, even as a separate thread in Python, seems to cause the transformers to hang. Checking the PostgreSQL logs, it appears the connection is never actually made. But, if Python uses subprocess.Popen to execute an "nc" bash command to test the Python tunnel it appears to work as well. So definitely something strange in the way threads/processes/code environment interacts within the workbench is what is going wrong.

However, not being one to give up, I did find a way to make this work. Using subprocess.Popen to call the native ssh command using roughly the format:

exec ssh -tt -o "UserKnownHostsFile /data/fmeserver/resources/data/host_key_file" -i /data/fmeserver/resources/data/host_private_key.pem -L <local_port>:localhost:<remote_port> <host_username>@<host>

While I'll do a proper write up later, the exec part is important to kill the process in the shutdown Python script (otherwise the tunnel lingers until you restart and if you try to kill the process you hang the engine). You will need to obtain the host keys of your target and store them somewhere if you are using FME Cloud. Same with the private_key.pem, though with this one you will have to do a one time SystemCaller to set the chmod to 600 otherwise ssh will fail.

This process allows you to create a temporary tunnel for use by the specific Engine running the workbench. Alternatively, you can create a workbench that uses a SystemCaller to establish a permanent tunnel in the background using the above command, then let other workbenches access it as needed. Keeping an eye on the processes you spawn, particularly on FME Cloud as the more limited machine control should be respected, otherwise you'll need to get comfortable with frequent restarts to clean up things.

oliver.morris
Rank
Badge +10

we are coming against the same requirements, has an idea been created for this or have you made any further progress? many thanks

jstanger
Badge +1

So there is good news and bad news.

 

My requirements were to manage a temporary SSH tunnel just long enough to execute the workbench then close the tunnel, potentially making it available for reuse. Particularly with the Python libraries there seems to be some mysterious behaviour of async/sub-processes either closing before the Transformers start working or never closing causing the next execution to fail.

 

What I did realize, is that in my case FME Cloud is running on a Linux box and therefore I could execute a fairly straight forward system call using subprocess.Popen(), stash the process PID and the process object, then ensure I kill it after the workbench finishes. Requires you to have available to the FME engine a known hosts file and probably use a public key for auth, but it facilitates compression over the tunnel as well so the performance seems to be fairly good.

 

Now if you have a pure FME Server instance and the SSH connection never needs to be broken, you have some simpler options. If you are in my boat then you also have to worry about port management/locking potentially requiring a semaphore type code pattern with a lock file or database and I found the easiest way was simply to have a second "manager" workbench which sadly consumes two engines but is simpler coding wise.

 

Drop me an email if you'd like more details as it is still a bit hacky where I'm at so I'm a touch embarrassed to publish publicly. But I can confirm, it is possible to either tunnel direct or via a jump server to give you port mappings to any other machine without opening those ports to the world wide hackers.

oliver.morris
Rank
Badge +10

@jstanger​ thank you for such a comprehensive reply, that is really helpful and saved me a lot of investigation time. The process logging is the main concern for me as we have many database calls. @stewartatsafe​ what are your thoughts on implementing SSH tunnelling to sql rds databases? It would really help avoid us having convoluted methods to reach these resources in secure environments. Shall I create an idea for this? Thank you

stewartatsafe
Rank
Badge +5

@jstanger​ thank you for such a comprehensive reply, that is really helpful and saved me a lot of investigation time. The process logging is the main concern for me as we have many database calls. @stewartatsafe​ what are your thoughts on implementing SSH tunnelling to sql rds databases? It would really help avoid us having convoluted methods to reach these resources in secure environments. Shall I create an idea for this? Thank you

Hi @oliver.morris​ . Supporting creating an SSH tunnel to a FME Cloud instance isn't out of the question. Clearly there is demand based upon this thread. Obviously security is a key concern as we would be opening up another entry point into the the infrastructure. Performance also needs to be considered. Have you ever sent larger volumes of data in production over an SSH tunnel (TCP)? I am a little bit nervous about stability and how lost packets would be handled.

 

Please create an Idea for it so we can see if anyone else is interested. I will file a task for us to do some further research and testing internally.

jstanger
Badge +1

Hi @oliver.morris​ . Supporting creating an SSH tunnel to a FME Cloud instance isn't out of the question. Clearly there is demand based upon this thread. Obviously security is a key concern as we would be opening up another entry point into the the infrastructure. Performance also needs to be considered. Have you ever sent larger volumes of data in production over an SSH tunnel (TCP)? I am a little bit nervous about stability and how lost packets would be handled.

 

Please create an Idea for it so we can see if anyone else is interested. I will file a task for us to do some further research and testing internally.

Hi @stewartatsafe​ . I can understand quite well the concern about stability. This is actually why I open the tunnel just long enough to execute the workbench then close it again. In terms of data volume, I've been pretty successful pushing what probably amounts to around 100mb worth of results from SQL queries (approx say 35 unique SQL transactions) without issue. Typically the issue arises if the SSH tunnel is expected to remain open over long periods of idle time. Also keeping the tunnel only as long as needed would be a way to mitigate security and if the tunnel required public key security rather than username/password it shouldn't be more vulnerable than existing attack points I feel.

 

Moving rasters and the like would obviously be a bit more of a risk but since the tunnel itself it just forwarding packets, the TCP or UDP protocol over top should handle dropped packets if possible.

 

I'll have a think about the best way to propose the idea. Some part of me would be quite happy with just having an SSH tunnel be similar to a Web Connection Resource on FME Server that could be referenced as required for various transformers somehow. But another part of me would quite like the idea of being able to drop in a SSHTunnel transformer that would open a tunnel that would be closed once the FME engine process finished. I have a feeling the way FME engine handles threads might make that tricky though.

oliver.morris
Rank
Badge +10

Hi @oliver.morris​ . Supporting creating an SSH tunnel to a FME Cloud instance isn't out of the question. Clearly there is demand based upon this thread. Obviously security is a key concern as we would be opening up another entry point into the the infrastructure. Performance also needs to be considered. Have you ever sent larger volumes of data in production over an SSH tunnel (TCP)? I am a little bit nervous about stability and how lost packets would be handled.

 

Please create an Idea for it so we can see if anyone else is interested. I will file a task for us to do some further research and testing internally.

thanks @stewartatsafe​ idea created - https://community.safe.com/s/idea/0874Q000000j0MJQAY/detail

we are more than happy to test if performance is a concern.

Reply


Community Stats

28,728
Posts
109,523
Replies
36,566
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.