connect to Redshift via an intermediate server

Question

I'm developing a Java application that connects to a Redshift database to run massive queries that won't run on our hardware. The application also consumes various internal, non-AWS resources in our datacenter (e.g. files on our NAS, Oracle, MySQL, etc...).

Unfortunately, due to some network routing restrictions, it's not possible for the application to connect directly to Redshift. I can manually connect to our production Redshift cluster via SSH to an intermediate EC2 instance that belongs to our VPC - and I'm looking to do this programatically.

In my test environment, which doesn't have the same routing restrictions, I'm able to connect using a datasource like this:

@Bean(name="dataSourceRedshift")
public DataSource dataSourceRedshift() throws SQLException {
    SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
    dataSource.setDriver(new com.amazon.redshift.jdbc41.Driver());
    dataSource.setUrl("jdbc:postgresql://" + redshiftHost + ":" + redshiftPort + "/" + redshiftDatabase);
    dataSource.setUsername(redshiftUser);
    dataSource.setPassword(redshiftPass);
    return dataSource;
}

In our production environment, where I can't connect directly to Redshift, is there a way to tweak the datasource bean (above) to setup an SSH tunnel through the EC2 instance? If not, what's the best way to 'hop' through?

Alex Woolford Alex Woolford · Accepted Answer · 2016-05-20T16:29:02

I stumbled across a really simple way to create a datasource that tunnels through SSH (courtesy of Lucas Theisen: https://github.com/lucastheisen/jsch-extension):

@Bean(name="dataSourceRedshift")
public DataSource dataSourceRedshift() throws SQLException, JSchException {
    SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
    dataSource.setDriver(new com.amazon.redshift.jdbc41.Driver());
    dataSource.setUrl("jdbc:postgresql://" + redshiftHost + ":" + redshiftPort + "/" + redshiftDatabase);
    dataSource.setUsername(redshiftUser);
    dataSource.setPassword(redshiftPass);

    DefaultSessionFactory defaultSessionFactory = new DefaultSessionFactory();

    TunneledDataSourceWrapper tunneledDataSource = new TunneledDataSourceWrapper(
            new TunnelConnectionManager(
                    defaultSessionFactory,
                    redshiftTunnel ),
            dataSource );

    return tunneledDataSource;
}

Where the redshiftTunnel string is:

awoolford@localhost->awoolford@{{ ec2 instance in our VPC }}|127.0.0.1:5439:{{ redshift endpoint }}:5439

connect to Redshift via an intermediate server

1 Answers