Cloudera Community

Support Questions

Find answers, ask questions, and share your expertise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search

QueryDatabaseTable giving error while pulling data from postgre

Labels:
avatar
author-rank Kunal.Gaikwad
Rising Star

Created on ‎08-16-2017 09:27 AM - edited ‎08-17-2019 07:19 PM

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

28397-dbconnec.png

4,031 Views
0 Kudos
3 REPLIES 3

avatar
author-rank jsensharma author-rank
Master Mentor

Created ‎08-16-2017 09:37 AM

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

3,522 Views
0 Kudos

avatar
author-rank jsensharma author-rank
Master Mentor

Created ‎08-16-2017 09:52 AM

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

.

3,522 Views
0 Kudos

avatar
author-rank Kunal.Gaikwad
Rising Star

Created ‎08-17-2017 09:01 AM

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

3,522 Views
0 Kudos
Announcements
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
What's New @ Cloudera
New Action menu item in the Cloudera Operational Database UI
What's New @ Cloudera
Get the list of supported instance types in the Cloudera Ope...
View More Announcements