Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi

BerniHacker — Mon, 21 Mar 2022 16:58:26 GMT

I have created a data pipeline with Apache Ni-Fi (version 1.15.2) to transfer data from a MySQL database in a AWS RDS instance into Timescale (postgreSQL). Ni-Fi runs in Linux Ubuntu 18.04 in Virtual Box on top of Windows 10. The pipeline has been tested successfully by using as a target the Timescale database located in my local Linux instance, without using SSL.

Then I have modified the pipeline to use Timescale Cloud as a target (https://portal.timescale.cloud/login). The database name is: periodic-measurements

In Timescale Cloud service page I can see these credentials:

Host: <host-string>
Port: 10250
User: tsdbadmin
Password: <timescale-cloud-service-password>
Service URI: postgres://tsdbadmin:<timescale-cloud-service-password>@<host-string>:10250/defaultdb?sslmode=require
SSL mode: require
Allowed IP addresses: 0.0.0.0/0

I use PutDatabaseRecord processor to write data in the postgreSQL database which uses the DBCPConnectionPool controller service.

I have copied the CA certificate got from Timescale Cloud service page into the following directory in my Linux instance with the following file name:

/etc/ca-certificates/ts-cloud-ca.pem

These are the configuration parameters in the the DBCPConnectionPool controller service:

• Database Connection URL: jdbc:postgres://<host-string>:10250/periodic-measurements

• Database Driver Class Name: org.postgresql.Driver

• Database Driver Location: /usr/share/java/postgresql/postgresql-42.3.1.jar

• Database User: tsdbadmin

• Password: <timescale-cloud-service-password>

• sslmode: require

• sslrootcert: /etc/ca-certificates/ts-cloud-ca.pem

When enabling the controller service I get the following error:

StandardControllerServiceNode[service=DBCPConnectionPool[id=017e1005-818e-1423-7951-125faf9dff4d], name=Connector to Timescale Cloud, active=true] Failed to invoke @OnEnabled method due to java.sql.SQLException: No suitable driver

causes: org.apache.nifi.processor.exception.ProcessException: No suitable driver for the given Database Connection URL: No suitable driver for the given Database Connection URL

I get the same result if I change the DBCPConnectionPool controller service settings with sslmode: verify-ca

Note that I have managed to connect successfully to the Timescale Cloud database with Grafana from my Linux instance by using the same credentials.

Any hint what could be wrong?

Thanks,

Bernardo

Re: Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi

araujo — Mon, 21 Mar 2022 22:05:28 GMT

@BerniHacker ,

I noticed that your JDBC URL starts with "jdbc:postgres:". The correct is "jdbc:postgresql:".

Could you please make this change and try again?

Cheers,

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Re: Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi

BerniHacker — Tue, 22 Mar 2022 08:36:50 GMT

Hi André!

Thanks for your answer! That was the problem!

question Re: Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi in Support Questions

Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi

Re: Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi

Re: Error when trying to connect to Timescale Cloud with SSL by using Apache Ni-Fi