Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
New Contributor

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.

 

1 ACCEPTED SOLUTION

avatar
New Contributor

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 solution in original post

6 REPLIES 6

avatar
Super Guru

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

avatar
New Contributor

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. 

avatar
Super Guru

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

avatar
New Contributor

@stevenmatison @YerFel 

Am able to add the ssl parameters ssl= true, sslmode=verify-full, sslrootcert= <path to certificate>, sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory . Am trying to connect postgredb getting cerificate chaining error. I can see in Nifi logs 

Caused by: java.security.cert.CertPathValidatorException: The certificate issued by CN=XXX Cloud Databases is not trusted. 

I have placed the root certificate in my project folder only. I don't know how do "At Nifi level make sure the cert file(s) are owned to nifi user". can u suggest me??

certificate name is 'server.cert.der'

avatar
Super Guru

@RajeshLuckky  If you follow the original post,  you need the ssl key and cert in the jdbc string.

 

"At Nifi level make sure the cert file(s) are owned to nifi user".

 

For example, if you create the cert and key files in the folder /etc/nifi/ssl/ then you would execute:

chown -R nifi:nifi /etc/nifi/ssl/

 

This will own the files to nifi so the nifi user can read them. 

avatar
New Contributor

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.