Connection to PostgreSQL Database fails

[EGarouachi]

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

• With SSL mode disabled 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

• Without specifying SSL mode ( 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

• 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.

[Anonymous]

Hello,

Here is a KB article about: Three ways to set a truststore for an SSL/TLS connection in a Job

Let us know if it is what you are looking for?

Best regards

Sabrina

[EGarouachi]

Hello ,

Thank you for your answer.

I tried this way too but I still have the same problem​ of mismatching common name and hostname.

[Anonymous]

Hello,

Here is an article about: https://stackoverflow.com/questions/55545924/how-to-solve-the-error-connection-could-not-be-allocate...

Let us know if it helps.

Best regards

Sabrina

[EGarouachi · Accepted solution]

thank you but the solution was 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)

[Anonymous]

Hello,

Thanks for sharing your solution with us.

Best regards

Sabrina