topic Re: Connection to PostgreSQL Database fails in Talend Studio

Connection to PostgreSQL Database fails

EGarouachi — Sat, 16 Nov 2024 01:03:15 GMT

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.

Re: Connection to PostgreSQL Database fails

Anonymous — Thu, 26 Nov 2020 03:39:39 GMT

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

Re: Connection to PostgreSQL Database fails

EGarouachi — Thu, 26 Nov 2020 08:20:13 GMT

Hello ,

Thank you for your answer.

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

Re: Connection to PostgreSQL Database fails

Anonymous — Fri, 27 Nov 2020 11:03:50 GMT

Hello,

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

Let us know if it helps.

Best regards

Sabrina

Re: Connection to PostgreSQL Database fails

EGarouachi — Fri, 27 Nov 2020 12:37:28 GMT

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)

Re: Connection to PostgreSQL Database fails

Anonymous — Mon, 30 Nov 2020 05:31:38 GMT

Hello,

Thanks for sharing your solution with us.

Best regards

Sabrina