Tungsten Won't Connect w/ SSL to Redshift

184 views
Skip to first unread message

Paul Cavallaro

unread,
Jan 4, 2016, 12:51:29 PM1/4/16
to Tungsten Replicator Discuss
I've tried testing out Tungsten Replicator to replicate from MySQL -> Redshift, but it looks like there is no way for Tungsten to connect to Redshift using SSL.

Looking at the code here: https://github.com/vmware/tungsten-replicator/blob/master/replicator/src/java/com/continuent/tungsten/replicator/datasource/SqlConnectionSpecRedshift.java#L58-L66 , it looks like there is no way to specify to use SSL when connecting to Redshift, which is required for my use case.

Wondering if there is a work around, or if this is a known issue.

When I attempted, I found the following stack trace from trepsvc.log when trying to start the Redshift slave tungsten service:


INFO   | jvm 1    | 2015/12/30 19:34:14 | com.continuent.tungsten.replicator.ReplicatorException: Unable to connect to DBMS: url=jdbc:postgresql://redacted.redshift.amazonaws.com:5439/redacted?tcpKeepAlive=true user=tungsten
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.datasource.SqlConnectionManager.getWrappedConnection(SqlConnectionManager.java:137)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.datasource.SqlConnectionManager.getCatalogConnection(SqlConnectionManager.java:177)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.datasource.RedshiftDataSource.reduce(RedshiftDataSource.java:182)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.datasource.DataSourceManager.removeAndReleaseAll(DataSourceManager.java:233)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.datasource.DataSourceService.release(DataSourceService.java:222)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.conf.ReplicatorRuntime.releasePlugin(ReplicatorRuntime.java:1332)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.pipeline.Pipeline.release(Pipeline.java:307)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.conf.ReplicatorRuntime.release(ReplicatorRuntime.java:659)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.management.tungsten.TungstenPlugin.doShutdown(TungstenPlugin.java:1343)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.management.tungsten.TungstenPlugin.offline(TungstenPlugin.java:606)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.management.OpenReplicatorManager$ErrorShutdownAction.doAction(OpenReplicatorManager.java:1143)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.fsm.core.StateMachine.applyEvent(StateMachine.java:362)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.fsm.event.EventProcessor.run(EventProcessor.java:78)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at java.util.concurrent.FutureTask.run(FutureTask.java:262)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at java.lang.Thread.run(Thread.java:745)
INFO   | jvm 1    | 2015/12/30 19:34:14 | Caused by: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "10.10.5.152", user "tungsten", database "redacted", SSL off
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:408)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:181)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:144)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.Driver.makeConnection(Driver.java:410)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at org.postgresql.Driver.connect(Driver.java:280)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at java.sql.DriverManager.getConnection(DriverManager.java:571)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at java.sql.DriverManager.getConnection(DriverManager.java:215)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.database.AbstractDatabase.connect(AbstractDatabase.java:193)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.database.PostgreSQLDatabase.connect(PostgreSQLDatabase.java:139)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       at com.continuent.tungsten.replicator.datasource.SqlConnectionManager.getWrappedConnection(SqlConnectionManager.java:132)
INFO   | jvm 1    | 2015/12/30 19:34:14 |       ... 17 more

Thanks for any insight,

-Paul

Dmitri Dmitri

unread,
Jun 27, 2017, 12:44:15 PM6/27/17
to Tungsten Replicator Discuss
I have exactly the same problem!

Configuring Tungsten to replicate to Redshift...
got this in the log (/opt/continuent/service_logs/trepsvc.log):

INFO   | jvm 1    | 2017/06/27 16:16:13 | com.continuent.tungsten.replicator.ReplicatorException: Unable to connect to DBMS: url=jdbc:postgresql://<redshift_host>:5439/<redshift_database>?tcpKeepAlive=true user=<redshift_user>
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.datasource.SqlConnectionManager.getWrappedConnection(SqlConnectionManager.java:137)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.datasource.SqlConnectionManager.getCatalogConnection(SqlConnectionManager.java:177)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.datasource.RedshiftDataSource.reduce(RedshiftDataSource.java:182)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.datasource.DataSourceManager.removeAndReleaseAll(DataSourceManager.java:233)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.datasource.DataSourceService.release(DataSourceService.java:222)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.conf.ReplicatorRuntime.releasePlugin(ReplicatorRuntime.java:1332)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.pipeline.Pipeline.release(Pipeline.java:307)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.conf.ReplicatorRuntime.release(ReplicatorRuntime.java:659)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.management.tungsten.TungstenPlugin.doShutdown(TungstenPlugin.java:1343)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.management.tungsten.TungstenPlugin.offline(TungstenPlugin.java:606)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.management.OpenReplicatorManager$ErrorShutdownAction.doAction(OpenReplicatorManager.java:1143)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.fsm.core.StateMachine.applyEvent(StateMachine.java:362)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.fsm.event.EventProcessor.run(EventProcessor.java:78)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at java.util.concurrent.FutureTask.run(FutureTask.java:266)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at java.lang.Thread.run(Thread.java:748)
INFO   | jvm 1    | 2017/06/27 16:16:13 | Caused by: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "172.30.138.245", user "<redshift_user>", database "<redshift_database>", SSL off
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:408)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:181)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:144)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.Driver.makeConnection(Driver.java:410)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at org.postgresql.Driver.connect(Driver.java:280)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at java.sql.DriverManager.getConnection(DriverManager.java:664)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at java.sql.DriverManager.getConnection(DriverManager.java:247)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.database.AbstractDatabase.connect(AbstractDatabase.java:193)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.database.PostgreSQLDatabase.connect(PostgreSQLDatabase.java:139)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       at com.continuent.tungsten.replicator.datasource.SqlConnectionManager.getWrappedConnection(SqlConnectionManager.java:132)
INFO   | jvm 1    | 2017/06/27 16:16:13 |       ... 17 more

It seems, JDBC URL needs these parameters:
  ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory

Where can I specify it in Tungsten?

Dmitri Dmitri

unread,
Jun 27, 2017, 1:15:54 PM6/27/17
to Tungsten Replicator Discuss
I use tungsten-replicator-oss-4.0.1-123

Does it helps when I switch to version 5.1 ?

MC Brown

unread,
Jun 29, 2017, 11:43:52 AM6/29/17
to Dmitri Dmitri, tungsten-repl...@googlegroups.com
Hi, 

So the simple answer is that there is no simple solution to this, but you could edit the JDBC URL directly. 

If you edit the file in /opt/continuent/tungsten/tungsten-replicator/static-alpha.properties

You could then add the SSL for RedShift JDBC connections after that. 

You can try doing this through tpm by editing the properties, although this might currently be overwritten. 

Meanwhile, I’ve added a note to ensure we add this functionality to a future release of the product.

MC

—
MC Brown
VP, Products, Continuent Ltd
--
You received this message because you are subscribed to the Google Groups "Tungsten Replicator Discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tungsten-replicator...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Dmitri Dmitri

unread,
Jun 29, 2017, 1:34:44 PM6/29/17
to Tungsten Replicator Discuss, dmitri...@gmail.com
Thanks for you support, MC!

I put the following into static-redshift_stage.properties (full path: /opt/continuent/releases/tungsten-replicator-oss-4.0.1-123_pid32654/tungsten-replicator/conf/static-redshift_stage.properties)
(my "service" named "redshift_stage" ):

replicator.global.db.sslEnabled=true
replicator.global.db.sslOptions=ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

# Used by manager to create datasources dynamically
replicator.resourceJdbcUrl=jdbc:postgresql://${replicator.global.db.host}:${replicator.global.db.port}/<database_name>?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

and still got the same error.

and, I tried this case too:
replicator.resourceJdbcUrl=jdbc:postgresql://${replicator.global.db.host}:${replicator.global.db.port}/sliceds?tcpKeepAlive=true

and again, the same error.

In all cases the error message was like this:

  Unable to connect to DBMS: url=jdbc:postgresql://<redshift_host>:5439/<redshift_database>?tcpKeepAlive=true user=<redshift_user>
I did not saw the additional SSL arguments in the JDBC URL mentioned in the error message


By the way, I was able to login into this Redshift cluster using these methods:

1. PostgreSQL JDBC driver postgresql-8.4-703.jdbc4.jar, JDBC URL:
jdbc:postgresql://<redshift_host>:5439/<redshift_database>?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

2. Redshift JDBC driver RedshiftJDBC42-1.2.1.1001.jar, JDBC URL:
jdbc:redshift://<redshift_host>:5439/<redshift_database>?ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory

And, Amazon recommends to use their JDBC driver, not the PostgreSQL driver:
http://docs.aws.amazon.com/redshift/latest/mgmt/configuring-connections.html#connecting-drivers

Would you please point me to a place in Java code where I can modify the JDBC URL?
(Tweaking configuration is preferrable. But in worst case I can just hack it into Java code)
To unsubscribe from this group and stop receiving emails from it, send an email to tungsten-replicator-discuss+unsub...@googlegroups.com.

Dmitri Dmitri

unread,
Jun 30, 2017, 10:54:09 AM6/30/17
to Tungsten Replicator Discuss, dmitri...@gmail.com
What is especially strange - in all cases I see this error message in the log

  com.continuent.tungsten.replicator.ReplicatorException: Unable to connect to DBMS: url=jdbc:postgresql://<redshift_host>:5439/<redshift_database>?tcpKeepAlive=true user=<redshift_user>

Even when I tweak the JDBC URL in the "static-redshift_stage.properties" adding "sslfactory" or removing the "tcpKeepAlive" - this error message does not change!

MC Brown

unread,
Jun 30, 2017, 10:58:47 AM6/30/17
to tungsten-repl...@googlegroups.com, Dmitri Dmitri
HAve you restarted the replicator each time? 


To unsubscribe from this group and stop receiving emails from it, send an email to tungsten-replicator-discuss+unsubs...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Dmitri Dmitri

unread,
Jul 3, 2017, 1:12:45 PM7/3/17
to Tungsten Replicator Discuss, dmitri...@gmail.com
Yes, I restarted replicator each time

David Hagan

unread,
Jan 29, 2018, 2:25:19 PM1/29/18
to Tungsten Replicator Discuss
Just wondering if there is any step forward on this, as I am running into this issue as well for my project.

Most of what has been posted is the exact same issue I've seen on my installation.  I am currently running version 5.2.1.  

I've tried working in the variety of configuration settings, with restarting replicator each time, and there does not appear to be to change the URL to use SSL at all.  I've begun digging into the open source code to work with, but as Dmitri pointed out, would be better to work with configurations instead.

Please advise,

David 

MC Brown

unread,
Jan 30, 2018, 4:29:29 AM1/30/18
to tungsten-repl...@googlegroups.com
Hi David, all, 

We should be able to handle this with the new Redshift drivers and a suitable config option in the replicator. 

I admit I've been tied up trying to get another new release out the door, but let me see what I can sort this week. 

MC

--

David Hagan

unread,
Jan 30, 2018, 5:37:40 PM1/30/18
to Tungsten Replicator Discuss
- MC Brown

I did end creating an interim solution by cracking open the source code and using a workaround that makes the URL specify what I need; I was able to make my connection and build the source code for my own release.  It is a hack solution.

However, I would like to have a better solution that does use the configurations instead.  I am a bit rusty on my Java, but wouldn't mind helping out where I might be able to, to get this feature/bug fixed.

I will try getting my machine set up to work, but I might need a bit of help knowing how the various configs interact with the various data sources classes.

Regards,

David M. Hagan

JohnCr@VT

unread,
Nov 6, 2019, 3:59:34 PM11/6/19
to Tungsten Replicator Discuss
I've contacted support for this same problem.
Can't connect to Redshift using SSL.
Both the MySQL extractor and Redshift applier on the same AIM EC2 instance.
Relevant ini, log, and properties files are attached.

Regards,
John
static-demo.properties
trepsvc.log
tungsten-app.ini
tungsten-ext.ini

Chris Parker

unread,
Nov 6, 2019, 4:33:56 PM11/6/19
to tungsten-repl...@googlegroups.com
Thanks John, we’re looking into this for you and will get back to you shortly
--

You received this message because you are subscribed to the Google Groups "Tungsten Replicator Discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tungsten-replicator...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tungsten-replicator-discuss/ec4a2588-e262-4ddd-b33b-55932509bc15%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages