Spring Data JPA with ssh tunnel to a remote MySQL server

Question

I'm using Spring Data JPA with Hibernate as persistence provider in conjunction with a remote MySQL5 Server for a job that periodically replicates a subset of internal data. The job (i.e. a quartz-scheduled java application) runs once per dai and needs approx. 30seconds to complete the synchronization). For safety reasons we don't want to open the remote server for direct connections from outside (i.e. other than localhost).

I've seen examples with Jsch to programmatically set up an ssh tunnel, but could not finde any resources on how to integrate Jsch with spring data. One problem I'm seeing is that certain of my spring beans (i.e. org.apache.commons.configuration.DatabaseConfiguration) are created at application startup and already needs access to the datasource.

I could open the ssh tunnel outside of the application, but then it would be opened all the time, but I wanted to avoid that as I only need it opened 30seconds per day.

EDIT:

After some research I found several ways to get a ssh tunnel

A) Implementing my own DataSource (I extended org.springframework.jdbc.datasource.DriverManagerDataSource) and then used PostContruct and Predestroy to setup / close the ssh tunnel with Jsch

--> Problem: The ssh tunnel remains open for the lifetime of the application, what is not what I want

B) Implementing my own Driver (I extended com.mysql.jdbc.Driver) and overwrite "connect" to create the ssh tunnel before the connection

--> Problem: I'm not able to close the ssh tunnel connection

Any more suggestions are welcome

score 4 · Accepted Answer · answered Nov 06 '15 at 07:52

4

If you have a DataSource bean in your Spring configuration, you can create your own DataSource implementation that opens an SSH tunnel before attempting to make a connection using the provided JDBC URL. As an example, consider the following configuration that uses a HikariDataSource:

<bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource">
    <bean class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">...</bean>
  </property>
</bean>

You can extend the class HikariDataSource to provide your own implementation. An example below:

class TunneledHikariDataSource extends HikariDataSource implements InitializingBean {
  private boolean createTunnel = true;
  private int tunnelPort = 3306;

  public void afterPropertiesSet() {
    if(createTunnel) {
      // 1. Extract remote host name from the JDBC URL.
      // 2. Extract/infer remote tunnel port (e.g. 3306)
      // from the JDBC URL.
      // 3. Create a tunnel using Jsch and sample code
      // at http://www.jcraft.com/jsch/examples/PortForwardingL.java.html
      ...
    }
  }
}

Then, instantiate a bean instance for the custom class instead of HikariDataSource.

answered Nov 06 '15 at 07:52

manish

19,695
5
67
91

Thanks for the hint, did not know that I can previde my own implementation of the dataSource. As I'm using a jndi datasoruce ( org.springframework.jnd.JndiObjectFactoryBean) at the moment, I still have to find out how to get the connection url/port (which I define in Tomcat's context) – Raphael Roth Nov 07 '15 at 08:23
1

Expose the host name and port as properties on the bean as well. In addition to `createTunnel` and `tunnelPort`, you can also have `remoteHost` and `remotePort`. – manish Nov 07 '15 at 08:46
and how do I get a handle for closing the connection (to close the ssh tunnel?). As far as I understand I this is implemented in the Connection object itself (In my case a class implementing java.sql.Connectio? – Raphael Roth Nov 25 '15 at 10:50
1

Why do you want to open and close the tunnel on every connection request? Establishing the tunnel is an expensive operation so it will slow your operations down. If you really want to open and close tunnel on every connection, you will have to implement your own `Connection` class since you are playing with the connection lifecycle. Also, please ask different questions separately. – manish Nov 25 '15 at 11:17

Markus Rohlof · Answer 2 · 2022-04-06T13:14:05.443

Expanding on @manish's answer here is my solution that works with Spring Boot in 2022:

@Configuration
class DataSourceInitializer {

    @Bean
    fun dataSource(properties: DataSourceProperties): DataSource {
        return properties.initializeDataSourceBuilder().type(SshTunnelingHikariDataSource::class.java).build()
    }

}

class SshTunnelingHikariDataSource : HikariDataSource(), InitializingBean {

    override fun afterPropertiesSet() {
        val jsch = JSch()

        val filePath = javaClass.classLoader.getResource("id_rsa")?.toURI()?.path
        jsch.addIdentity(filePath, "optional_key_file_passphrase")

        val session = jsch.getSession("root", "remote-host.com")

        val config = Properties()
        config["StrictHostKeyChecking"] = "no";

        session.setConfig(config)
        session.connect()


        session.setPortForwardingL(3307, "db-host.com", 3306) 
    }
}

Depending on your connection you may not need the identity file (i. e. a private key) but instead you may need username + password which you can provide at getSession.

remote-host.com is the host you want to have your SSH session to terminate in, having access to the database

db-host.com is the host that your remote-host can resolve. It may as well be localhost if the database is running locally on your remote-host

Spring Data JPA with ssh tunnel to a remote MySQL server

2 Answers2

Linked