Skip to main content
YerFel
YerFel
Gold ⭐️
March 5, 2020
Solved

Nifi and Postgresql with certificate: how to configure client certificate and key in DBCPConnectionPoll service?

I have a NIFI image running in openshift and a postgres in the cloud "owned" by another department.

 

They sent to us a certificate(".crt") and a key(".key"), since the log-in is made trough client certificate, instead of username and password. I have succeeded to log-in in pgAdmin 4, but not to connect the NIFI to the Postgres with certificate and key.

 

I have uploaded the certificate and the key to the image (using a secret and mounting it) so if I go to the pod terminal I can access it.

 

But, when I pass to a DBCPConnectionPoll service the connection string bellow and activates an ExecuteSQL processor, I receive an exception that the certificate is not valid, as follows:

 

ERRORExecuteSQL[id=...] Unable to execute SQL query <...>;due to java.sql.SQLException: Cannot create a PoolableConnectionFactory (FATAL: connection requires a valid client certificate).
No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create a PoolableConnectionFactory (FATAL: connection requires a valid client certificate)

 

I have tried to pass the certificate in two ways to the DBCPConnectionPoll service:

1) as parameters in the connection string ("database connection url" property:

jdbc:postgresql://<ip>:<port>/<username>?user=<username>&sslTrue&sslcert=/etc/.../mycerts/mycert.der&sslkey=/etc/.../mycerts/mykey.key.pk8

2) adding properties in the DBCPConnectionPoll service (+ button and the just adding parameter name and the path as the value) and just passing this as url:

jdbc:postgresql://<ip>:<port>/<username>

 

Both seems to work generally speaking, since I can connect to another postgres I have which not requires ssl certification.

 

Some considerations:

1) My assumption here is that the connection string in the NIFI does not know to read properly the file path for the certificate and key.

2) I have converted the certificates a bunch of times to different types that java can receive in order to see if that was the problem, but I still receive the same exception. So it seems that the connection pool just does not "achieve" the files at all. Nevertheless, if some one has a say in this topic, it can be handy, after the main problem is solved. So appreciate some tips here as well.

3) I have also read the NIFI source code and it seems that NIFI uses normally JDBC classes to create the connection pool, so a connection string as I passed would have worked in java code, but somehow doesn't work in NIFI (which is written in java).

4) The jdbc driver and everything else is configured properly, since I can work with a non-secure postgres in NIFI.

 

Thank you very much.

 

YerFel
YerFelAnswer
Gold ⭐️
March 19, 2020

A co-worker found the 'simple solution' to the problem I asked and I would like to share so it can help others.

 

What was missing was the property sslmode = require. After including that, the service worked perfectly. Actually, I am not sure why it didn't work with sslmode = prefer, since it is what my pgadmin is using for the same database and there it works perfectly. It seems like we must 'force' nifi to use ssl in this case - see documentation here: https://jdbc.postgresql.org/documentation/head/ssl-client.html.

 

Moreover, some insights:

1) It worked with certificate in '.der' and key in '.pk8' formats (didn't have to use trust-store and key-store as needed in other services).

2) One can add in the 'plus' button the properties and give them the right name as we would do in java code, instead of concatenating every property in the connection string (see second option in the question above).

 

Make it helps others as well.

View in thread

6 replies

stevenmatison
stevenmatison
Platinum ⭐️⭐️
March 5, 2020

You need to use the cert to create truststore files and deliver these files to all of the nifi nodes.   These files needed to be owned nifi:nifi as well.   Then provide the path to these files to the controller service: SSLContextService.  Then configure your Processor to use this SSLContextService.

 

An example truststore command looks like:

 

/usr/jdk64/jdk1.8.0_112/bin/keytool -import -file /etc/nifi/ssl/file.crt -alias nifi -keystore /etc/nifi/ssl/truststore.jks

Translate
YerFel
YerFelAuthor
Gold ⭐️
March 9, 2020

Thank you, for your answer.

Unfortunately, not the DBCPConnectionPool service, nor the ExecuteSQL processor provide option for SSLContextService.

 

Therefore, I need another solution and it suppose it must be in the url connection string, but not sure. 

Translate
stevenmatison
stevenmatison
Platinum ⭐️⭐️
March 9, 2020

@YerFel I think you still need to use the truststore or keystore method.   Try to test jdbc connection string non ssl vs ssl with another tool until you get the right connection string, then move to NIFI Execution.   At Nifi level make sure the cert file(s) are owned to nifi user.     Also watch the nifi log during testing if not already. That log may provide additional hints to the client error.

Translate
YerFel
YerFelAuthorAnswer
Gold ⭐️
March 19, 2020

A co-worker found the 'simple solution' to the problem I asked and I would like to share so it can help others.

 

What was missing was the property sslmode = require. After including that, the service worked perfectly. Actually, I am not sure why it didn't work with sslmode = prefer, since it is what my pgadmin is using for the same database and there it works perfectly. It seems like we must 'force' nifi to use ssl in this case - see documentation here: https://jdbc.postgresql.org/documentation/head/ssl-client.html.

 

Moreover, some insights:

1) It worked with certificate in '.der' and key in '.pk8' formats (didn't have to use trust-store and key-store as needed in other services).

2) One can add in the 'plus' button the properties and give them the right name as we would do in java code, instead of concatenating every property in the connection string (see second option in the question above).

 

Make it helps others as well.

Translate
Terms & ConditionsAccessibility statement