Talend Connection to PostgreSQL Database fails

Question

I'm trying to make a connection to a PostgreSQL database which is located in a remote server using Talend Open Studio for Big Data v7.3.1 the problem is that the server requires SSL certificates. I tried many ways but It still fails. Here are the connection strings that I tried and the log errors in TOS BD

1)With ssl mode disable jdbc:postgresql://xx.xx.xx.xx:5432/dbName?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&sslmode=disable

Exception in component tDBConnection_1 (DB_Connection)
org.postgresql.util.PSQLException: FATAL: pg_hba.conf rejects connection for host "my.ip.address.here", user "user", database "databasename", SSL off

2)Without specifying ssl mode (it means it takes the default mode) jdbc:postgresql://xx.xx.xx.xx:5432/dbName?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

org.postgresql.ssl.PGjdbcHostnameVerifier verify
GRAVE: Server name validation failed: hostname xx.xx.xx.xx does not match common name xxxxx
Exception in component tDBConnection_1 (DB_Connection)
org.postgresql.util.PSQLException: The hostname xx.xx.xx.xx could not be verified by 
hostnameverifier PgjdbcHostnameVerifier

3)If I omit SSL parameters part in the connection like this jdbc:postgresql://xx.xx.xx.xx:5432/dbName? it asks for a valid client certificate.

NB:I can connect to the database using the pgadmin the problem is with Talend open Studio

Emél Emél · Accepted Answer · 2020-11-27T12:44:46

The solution is to put the certificates locations in the connection string with specifying the ssl mode :

ssl=true&sslmode=prefer&sslcert=DirectoryToSslCertificates/client.crt&sslkey=DirectoryToSslCertificates/clientkey.pk8&sslrootcert=DirectoryToSslCertificates/root.crt

(dont forget to change the certificates formats with openssl commands,I had .pem files)

Talend Connection to PostgreSQL Database fails

1 Answers