Re: JDBC failing due to networking issues

We have the same keys in two different formats, .key for the replication connection, .der for the JDBC connection, we checked (and re-built the keys just in case) and the keys are fine.

The stack trace shows:

WARN {2016-05-19 20:39:36,452} [xx-thread-x] (xx.java:145) - SQL Error: 0, SQLState: null
ERROR {2016-05-19 20:39:36,453} [xx-thread-x] (xx.java:147) - Unable to open a test connection to the given database. JDBC url = jdbc:postgresql://xx.xx.xx.xx/xx?ssl=true&sslmode=verify-full&sslcert=/xx/xx.crt&sslkey=/xx/xx.der&sslrootcert=/xx/xx.crt, username = xx. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
org.postgresql.util.PSQLException: SSL error: Fatal Alert received: {48}
at org.postgresql.ssl.jdbc4.AbstractJdbc4MakeSSL.convert(AbstractJdbc4MakeSSL.java:126)
at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:339)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:133)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:65)
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:156)
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:35)
at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:47)
at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:30)
at org.postgresql.Driver.makeConnection(Driver.java:414)
at org.postgresql.Driver.connect(Driver.java:282)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:363)
at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:416)
at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:120)
at com.xx.getConnection(xx.java:218)
at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141)
at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:550)
at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:429)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:372)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:417)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:255)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.xx.write(Unknown Source)
at com.xx.run(WriterServiceImpl.java:176)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
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:745)
Caused by: javax.net.ssl.SSLException: Fatal Alert received: {48}
at com.rsa.sslj.x.aH.a(Unknown Source)
at com.rsa.sslj.x.aH.a(Unknown Source)
at com.rsa.sslj.x.aH.a(Unknown Source)
at com.rsa.sslj.x.ap.c(Unknown Source)
at com.rsa.sslj.x.ap.a(Unknown Source)
at com.rsa.sslj.x.ap.j(Unknown Source)
at com.rsa.sslj.x.ap.i(Unknown Source)
at com.rsa.sslj.x.ap.h(Unknown Source)
at com.rsa.sslj.x.aS.startHandshake(Unknown Source)
at org.postgresql.ssl.jdbc4.AbstractJdbc4MakeSSL.convert(AbstractJdbc4MakeSSL.java:119)
... 35 more

We run tcpdump on both ends but we cannot be sure where is the failure, we can see the handshake process initiating and then failing, the sequence goes like:
Client Hello,
Server Hello, Certificate,
Server Key Exchange,
…
Certificate, Client Key Exchange, Certificate Verify, Change Cipher Spec, Client Hello[Malformed Packet]
Alert (Level: Fatal, Description: Unknown CA)

We thought the Malformed Packet could be an issue, but on a successful connection (from other geo) we also see a Malformed Packet (according to wireshark):
Client Hello,
Server Hello,
Certificate,
Server Key Exchange,
…
Certificate, Client Key Exchange, Certificate Verify, Change Cipher Spec,
Client Hello[Malformed Packet]
…
Change Cipher Spec, Encrypted Handshake Message
…
Application Data

My first guess was that a device is performing man-in-the-middle and changing one of the certificates, but I’m not really sure where to look.

Thanks

From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf Of Dave Cramer
Sent: Tuesday, May 24, 2016 9:49 AM
To: Bazan, Hernan <hernan(dot)bazan(at)intel(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] JDBC failing due to networking issues

My guess is the keys are not correct for the validating ssl connection. Do you have the stack trace by chance ?

Dave Cramer

davec(at)postgresintl(dot)com<mailto:davec(at)postgresintl(dot)com>
www.postgresintl.com<http://www.postgresintl.com>

On 23 May 2016 at 20:48, Bazan, Hernan <hernan(dot)bazan(at)intel(dot)com<mailto:hernan(dot)bazan(at)intel(dot)com>> wrote:
We are facing a problem on a customer where (apparently) there are networking issues.
Basically, we have a master DB with several hot_standby slaves, some on the same geo than the master, some on different geo. The application we run uses two JDBC connection pools, one read-only to the local DB replication, one write-only to the master DB.
The odd thing on this case is that the replication process is working, the slave is up to date with the master, but the JDBC connection to the master fails during the handshake process, with a fatal (48) error.
Enabling trust connections on the master for the given host, and disabling SSL validation (&sslfactory=org.postgresql.ssl.NonValidatingFactory) we can connect successfully.
We need a way to debug this issue and understand how the replication connection works ok and the JDBC doesn’t. What next steps do you recommend?

Thanks