Unable to connect to Posdtgres db

gal

• January 07, 2015 00:00

• Answered

Follow

Hi,

I'm new to CloverETL, and I'm trying to connect to the db for the first time.
I need ssl to be on. I tried using the following connection string in the URL secrion of the DB connection:
jdbc:postgresql://localhost:port/db?&ssl=true

But I received the following error message
"Can't connect to DB: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target"

I also tried to set the parameter ssl to true on the advanced section., but then it didn't recognized the ssl parameter:
"Can't connect to DB: FATAL: no pg_hba.conf entry for host "10.30.0.85", user "dbuser", database "db", SSL off"

Please advice.

Thanks!
Gal

• 

  slechtaj

  • January 09, 2015 17:18

  0

  Comment actions Permalink

  Hi Gal,
  
  You need to provide Clover with certificate of the DB Server. You can import such certificate into your own truststore and tell Clover to use that one. In order to do so you need to put the following arguments into graph run configuration (Run -> Run Configuration -> Arguments tab -> VM arguments text-area):
  

  -Djavax.net.ssl.trustStore=<PATH_TO_YOUR_TRUSTSTORE>
  -Djavax.net.ssl.trustStorePassword=<TRUSTSTORE_PASSWORD>

  
  Hope this helps.
• 

  gal

  • January 10, 2015 00:24

  0

  Comment actions Permalink

  Hi,
  
  Is this mandatory? I'm using a cloud based db and having problems getting it's certificate.
  After setting the following connection variables (jdbc.ssl=true, jdbc.sslfactory=org.postgresql.ssl.NonValidatingFactory) - I recieve a "Connection valid" info message when I validate the connection.
  However when I try to use the connection in order to extract METADATA or in order to connect to a DBOutputTable, I get the following error: "Can't connect to DB:FATAL: no pg_hba.cong entry for host <host_ip>" , user "<user_name>, database <db_name>, SSL off".
  
  Why is SSL off when I try to use the connection? In the advanced variables for the connection it is set to ssl=true and the validation works.
  
  Thanks
  Gal
• 

  gal

  • January 12, 2015 17:07

  0

  Comment actions Permalink

  I've added the path to the certificate, but I'm still getting the same error message - the tool doesn't try to use ssl connection.
• 

  slechtaj

  • January 12, 2015 17:35

  0

  Comment actions Permalink

  Hi Gal,
  
  you might use NonValidatingFactory in this case (Using SSL). The thing is that you wrote the properties with "jdbc." prefix as there is supposed to be used just "ssl" and "sslfactory". That is also why you see "SSL off" in the message. You can also paste these parameters into the URL.
  
  Hope this helps.
• 

  gal

  • January 12, 2015 18:35

  0

  Comment actions Permalink

  I've attached a screenshot that shows the current configuration. I'm still getting "SSL off" message

  • Screenshot 2015-01-12 10.27.40.jpg
• 

  imriskal

  • January 13, 2015 14:16

  0

  Comment actions Permalink

  Hi Gal,
  
  I have a few notes to your screenshot.
  
  1) JDBC properties is not the right place for setting path to your trust store. As my colleague Jan mentioned, trust store should be set via run configuration and it is valid for the whole JVM. (Run -> Run Configuration -> Arguments tab -> VM arguments) Or in case of CloverETL Server, it should be set in setenv script.
  2) You are setting path to a certificate instead of path to trust store. And you also need to set a password to it. See the following page to see the difference between certificate and key store.
  3) If ssl and sslfactory in "Custom JDBC properties" properties did not work, move them into your JDBC URL as parameters.
  
  I hope this helps.
  
  Regards,
• 

  gal

  • January 13, 2015 19:30

  0

  Comment actions Permalink

  Thanks! Setting the URL with the parameters worked:
  connection URL: jdbc:postgresql://localhost:port/db?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Please sign in to leave a comment.