Support Questions

Kunal.Gaikwad · ‎08-16-2017

DBCPConnectionPool is configured as the image (dbconnec image) attached

Database Connection URL is as: jdbc:postgresql://<IP>:<port>/<database name>?ssl=true

I am getting error as follows when i add "org.postgresql.ds.PGPoolingDataSource" Database Driver Class Name in the DBCPCOnnectionPool

 ERROR [Timer-Driven Process Thread-3] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=015d1028-9c7e-1e9b-821a-7e21dd312b82] Unable to execute SQL select query SELECT * FROM account_transaction due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target): {}
org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target)
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:275)
        at sun.reflect.GeneratedMethodAccessor711.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:89)
        at com.sun.proxy.$Proxy128.getConnection(Unknown Source)
        at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:266)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1120)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:272)
        ... 17 common frames omitted
Caused by: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
        at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:67)
        at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:359)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:148)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
        at org.postgresql.Driver.makeConnection(Driver.java:450)
        at org.postgresql.Driver.connect(Driver.java:252)
        at org.apache.nifi.dbcp.DriverShim.connect(DriverShim.java:46)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 20 common frames omitted
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
        at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
        at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1949)
        at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)
        at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)
        at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1514)
        at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)
        at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1026)
        at sun.security.ssl.Handshaker.process_record(Handshaker.java:961)
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1062)
        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)
        at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)
        at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)
        at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:62)
        ... 31 common frames omitted
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
        at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:387)
        at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:292)
        at sun.security.validator.Validator.validate(Validator.java:260)
        at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:324)
        at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:229)
        at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:124)
        at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1496)
        ... 39 common frames omitted
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
        at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
        at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
        at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)
        at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:382)
        ... 45 common frames omitted

And, when I put "org.postgresql.ds.PGPoolingDataSource" in the Database Driver Class Name in the DBCPCOnnectionPool and change the db connection URL to "jdbc:postgresql://X.X.X.X:5432/abc", I get the following error:

,906 WARN [Timer-Driven Process Thread-3] o.a.n.c.t.ContinuallyRunProcessorT                                                                         ask
java.lang.IllegalStateException: Cannot invoke method public abstract java.sql.Connection org.a                                                                         pache.nifi.dbcp.DBCPService.getConnection() throws org.apache.nifi.processor.exception.ProcessE                                                                         xception on Controller Service with identifier 015d1030-9c7e-1e9b-0b6a-4d8d0c01e807 because the                                                                          Controller Service is disabled
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke                                                                         (StandardControllerServiceInvocationHandler.java:84)
        at com.sun.proxy.$Proxy128.getConnection(Unknown Source)
        at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.                                                                         java:266)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.jav                                                                         a:1120)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProc                                                                         essorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProc                                                                         essorTask.java:47)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSc                                                                         hedulingAgent.java:132)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Sche                                                                         duledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledTh                                                                         readPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:748)
2017-08-16 09:38:57,906 ERROR [Timer-Driven Process Thread-4] o.a.n.p.standard.QueryDatabaseTab                                                                         le QueryDatabaseTable[id=015d1028-9c7e-1e9b-821a-7e21dd312b82] QueryDatabaseTable[id=015d1028-9                                                                         c7e-1e9b-821a-7e21dd312b82] failed to process session due to java.lang.IllegalStateException: C                                                                         annot invoke method public abstract java.sql.Connection org.apache.nifi.dbcp.DBCPService.getCon                                                                         nection() throws org.apache.nifi.processor.exception.ProcessException on Controller Service wit                                                                         h identifier 015d1030-9c7e-1e9b-0b6a-4d8d0c01e807 because the Controller Service is disabled: {                                                                         }
java.lang.IllegalStateException: Cannot invoke method public abstract java.sql.Connection org.a                                                                         pache.nifi.dbcp.DBCPService.getConnection() throws org.apache.nifi.processor.exception.ProcessE                                                                         xception on Controller Service with identifier 015d1030-9c7e-1e9b-0b6a-4d8d0c01e807 because the                                                                          Controller Service is disabled
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke                                                                         (StandardControllerServiceInvocationHandler.java:84)
        at com.sun.proxy.$Proxy128.getConnection(Unknown Source)
        at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.                                                                         java:266)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.jav                                                                         a:1120)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProc                                                                         essorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProc                                                                         essorTask.java:47)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSc                                                                         hedulingAgent.java:132)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Sche                                                                         duledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledTh                                                                         readPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:748)

Also, if i add "org.postgresql.Driver" Database Driver Class Name in the DBCPCOnnectionPool, and keep the db connection URL to "jdbc:postgresql://X.X.X.X:5432/abc", I get the following error:

229 ERROR [Timer-Driven Process Thread-3] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=015d1028-9c7e-1e9b-821a-7e21dd312b82] Unable to execute SQL select query SELECT * FROM XXXX due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (FATAL: no pg_hba.conf entry for host "X.X.X.X", user "abc", database "abc", SSL off): {}
org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (FATAL: no pg_hba.conf entry for host "X.X.X.X", user "abc", database "abc", SSL off)
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:275)
        at sun.reflect.GeneratedMethodAccessor711.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:89)
        at com.sun.proxy.$Proxy128.getConnection(Unknown Source)
        at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:266)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1120)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (FATAL: no pg_hba.conf entry for host "X.X.X.X", user "abc", database "abc", SSL off)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:272)
        ... 17 common frames omitted
Caused by: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "X.X.X.X", user "abc", database "abc", SSL off
        at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:438)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
        at org.postgresql.Driver.makeConnection(Driver.java:450)
        at org.postgresql.Driver.connect(Driver.java:252)
        at org.apache.nifi.dbcp.DriverShim.connect(DriverShim.java:46)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
        ... 20 common frames omitted

jsensharma · ‎08-16-2017

@Kunal Gaikwad

Looks like the issue is because you are using Postgres with SSL option and hence the Nifi truststore should have the certificates of the postgres certificates imported to it. That seems to be causing the following error.

(SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target): {}

- I think you should import Portgres DB certificate to Nifi Truststore. https://community.hortonworks.com/articles/886/securing-nifi-step-by-step.html (This is little OLD article though)

nifi.security.truststore=<path_to_truststore_file>

.

Or else you will need to import the portgresql certificates to the "$JAVA_HOME/jre/lib/security/cacerts", Here the JAVA_HOME is the java which Nifi is using.

jsensharma · ‎08-16-2017

@Kunal Gaikwad

Are you sure that your DB is running on SSL port and you really want your connection to the DB to be secure? If not then you can remove the "?ssl=true" option from the connection url and also from the DB side also we need to verufy if it allows insecure (non SSL) connection or not?

.

Kunal.Gaikwad · ‎08-17-2017

@Jay SenSharma

I had removed "?ssl=true" after the database name from the DB connection URL but I still get an error that controller service is disabled. I have pasted the error too above before.

Cloudera Community

Support Questions

QueryDatabaseTable giving error while pulling data from postgre