[resolved] Secure connection to heroku (aws, ssl)

[mertez]

I am struggling to establish a connection to postgresql server that requires ssl authenticaiton. In postgresql driver 9.2.1004 there exist an option without validating the server's certificate (NonValidatingFactory) however the current talend driver 9.2.1003 does not support it. How can I connect to the database via ssl?
I am using esb 5.5.0.

[mertez]

and the pictures..

[Anonymous]

Hi Mertez,
Following is connection string
Driver={PostgreSQL ANSI};Server=IP address;Port=5432;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;sslmode=require;
https://www.connectionstrings.com/postgresql/
which says sslmode - require which is case sensitive...
Can you connect using existing string from a client?
vaibhav

[mertez]

For establishing a secure connection via ssl you need a public certificate of the target server that you should put in your trusted computer java keystore (correct me if am wrong). If you do not have the certificate in the javastore you can bypass the server validation with the option "NonValidatingFactory" but still make an ecrypted connection. For now we are in a development stage so we try to use the above mentioned option which is not available in the v9.x driver and because of that the error from the attached picture appears.
The problem is that driver does not contain any NonValidatingFactory class.

[_AnonymousUser]

hello mertez,
did you solve your problem somehow?

[Anonymous]

I'm having the same problem in connecting Talend Open Studio for Data Integration (5.6.1) to my PostgreSQL Database on Heroku:

 
Connection failure. You must change the Database Settings.
  java.lang.RuntimeException: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "XXX.X.XXX.XX", user "xxxxxxxxxx", database "xxxxxxxxxx", SSL off
      at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:164)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1054)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:311)
      at org.talend.repository.ui.utils.ManagerConnection.check(ManagerConnection.java:272)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$26.runWithCancel(DatabaseForm.java:2335)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$26.runWithCancel(DatabaseForm.java:1)
      at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)
      at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)
      at java.util.concurrent.FutureTask.run(FutureTask.java:262)
      at java.lang.Thread.run(Thread.java:745)
  Caused by: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "XXX.X.XXX.XX", user "xxxxxxxxxx", database "xxxxxxxxxx", SSL off
      at org.postgresql.Driver$ConnectThread.getResult(Driver.java:348)
      at org.postgresql.Driver.connect(Driver.java:271)
      at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)
      at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:155)
      ... 9 more

Any help would be most appreciated.
Thanks,
Chandra

[mertez · Accepted solution]

I was quite struggling with this issue. If I remember correctly I have searched the appropriate driver that contained a class nonValidatingFactory and included it into the job. After that I think I had to do some modifications in the url too. Check the pics bellow. Hope it helps.

[mertez]

String from the database field is probably redundant so I think you can remove it.

[Anonymous]

Worked like a charm. Thanks a lot for the quick and prompt reply. You are a life savior.

[mertez]

Hehe, I am happy to hear that we solved the issue