topic Re: Talend v7.3 Error when connect to postgresDB using SSL in Talend Studio

Talend v7.3 Error when connect to postgresDB using SSL

huynhdung — Fri, 27 Sep 2024 05:42:10 GMT

Hi Team,

I need to configure the Postgres DB SSL certificate in Talend for establishing a secure connection with DB from Talend. But the error was occured.

Can you check what wrong with my setting?
Basic Settings:

Advanced Settings:
"ssl=true&sslmode=verify-ca&sslcert=C:/xxx/postgresql.crt&sslkey=C:/xxx/postgresql.key&sslrootcert=C:/xxx/production_root.crt"

My key worked well with Dbeaver!

If you need more information, please comment!

Thanks in advance.

Regards,

Re: Talend v7.3 Error when connect to postgresDB using SSL

Anonymous — Fri, 27 Sep 2024 07:43:16 GMT

Hello,

> I need to configure the Postgres DB SSL certificate in Talend for establishing a secure connection with DB from Talend.

I believe you mean "secure asymmetric authentication".

Your error looks like an application error to me: do you have some log from your server side? Whats the content of pg_hba.conf? What's the client configuration related to the ceritificate required from the server?

An asymmetric authentication doesn't requiere a password.

BTW, for PGSQL asymmetric auth requieres SSL as transport layer (https://www.postgresql.org/docs/current/auth-cert.html)

This might also help: https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CLIENT-CERTIFICATES

Re: Talend v7.3 Error when connect to postgresDB using SSL

huynhdung — Fri, 27 Sep 2024 09:04:39 GMT

Hi Nuser,

thank for your reply, let me check it!

Re: Talend v7.3 Error when connect to postgresDB using SSL

Anonymous — Sat, 28 Sep 2024 12:47:20 GMT

Hello @huynhdung ,I double checked and I was not aware that the client certificate authentication is part of TLS layer negotiation, so my previous comment might be not completely correct.

At this point, I believe your error is definitively connected to the certificates used in the mTLS authentication.

I did some tests (under Linux and with PEM format) and the environment below worked fine for me - it's not a production environment.

1 - Creating the CA certificate on server machine:

openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=192.168.1.71" chmod og-rwx root.key openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt

2 - Creating the server's CSR and the certificate on server machine:

openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=192.168.1.70" chmod og-rwx server.key openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

3 - Creating client's CSR on client machine:

openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=user" chmod og-rwx client.key

4 - Move the client's CSR (client.csr) on the server and create the client certificate on server machine:

openssl x509 -req -in client.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out client.crt

5 - Move the client certificate (client.crt) and the CA root certificate (root.crt) on the client machine.

6 - Configuring postgresql.conf on server machine:

# - SSL - ssl = on ssl_ca_file = 'root.crt' ssl_cert_file = 'server.crt' ssl_key_file = 'server.key'

7 - Configuring pg_hba.conf on server machine:

hostssl all user 127.0.0.1/32 cert clientcert=verify-full

Now, you should have your environment configured and from the client machine you should be able to authenticate throught certificate (mTLS).

In my environment it works with the following command:

psql 'host=127.0.0.1 port=5432 dbname=postgres user=user sslcert=client.crt sslkey=client.key sslrootcert=root.crt'

Back to the error you posted "connection require a valid client certificate", it is thrown when the server is expenting a certificate authentication but the client DOESN'T PROVIDE A CERTIFICATE.

With the previous configured environment is easy to reproduce the error:

psql 'host=127.0.0.1 port=5432 dbname=postgres user=user' psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: connection requires a valid client certificate

At this point I belive there might be two cases here:

1 - From a client perspective, "sslkey=C:/xxx/postgresql.key" is not a valid PKCS-8 DER format; follow the JDBC documentation to also convert the key: https://jdbc.postgresql.org/documentation/use/; (openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -v1 PBE-MD5-DES)

2 - The client is not able to traslate your advanced commands into the driver so it is not sending a certificate.

I'm more prone to the #1, but I believe you can troubleshoot it with the following steps:

1 - Try to authenticate to your PGSQL server without the JDBC driver and with the current certificate files.

2 - If it works, try to convert the PEM into a PKCS-8 DER format and change the advance configuration of your JDBC driver as following: "sslkey=C:/xxx/postgresql.pk8"

Thnanks for helping me dig deeper and hoping it helps!

I followed this documentation:

https://jdbc.postgresql.org/documentation/use/

https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CERTIFICATE-CREATION

Re: Talend v7.3 Error when connect to postgresDB using SSL

huynhdung — Wed, 02 Oct 2024 03:19:00 GMT

@Anonymous

Thank for your kind reply!.

For some reason I'm not able to check it now.

I'm check and come back in several days.

Re: Talend v7.3 Error when connect to postgresDB using SSL

huynhdung — Thu, 10 Oct 2024 02:29:54 GMT

@Anonymous

Thank for your support, I converted the key then it worked !

$ openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -nocrypt
$ chmod 0600 postgresql.pk8

Once more time, thanks you!