Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution
Highlighted

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

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

Accepted Solutions
Highlighted

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

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

4 REPLIES 4

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

Master Collaborator

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

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven @ DFHZ

Highlighted

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

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. 

Highlighted

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

Master Collaborator

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

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven @ DFHZ

Highlighted

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

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

Don't have an account?
Coming from Hortonworks? Activate your account here