CODEWITHSUNDEEP
javahibernatesshspring-datajsch
Raphael Roth
Raphael Roth

Reputation: 27383

Spring Data JPA with ssh tunnel to a remote MySQL server

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

Upvotes: 2

Views: 8753

Answers (2)

Markus Rohlof
Markus Rohlof

Reputation: 430

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

Upvotes: 1

manish
manish

Reputation: 20135

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.

Upvotes: 4

Related Questions