Re: JDBC failing due to networking issues - Mailing list pgsql-jdbc

I'd try connecting with a simple program that just creates a validated socket. That would be my first attempt at debugging.

On 24 May 2016 at 09:47, Bazan, Hernan <hernan.bazan@intel.com> wrote:

Yes, the key is on the keystore, there are other servers with the same exact configuration working, this is particular to this geo/network.

 

From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Tuesday, May 24, 2016 10:44 AM

To: Bazan, Hernan <hernan.bazan@intel.com>
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] JDBC failing due to networking issues

 

 

On 24 May 2016 at 09:31, Bazan, Hernan <hernan.bazan@intel.com> wrote:

I don’t have access right now, I will test with the latest jdbc.

This is not intermittent, JDBC fails every time (and replication is up and running).

 

In which case it is a key problem. Did you add the key to the java keystore ? 

 

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

 

 

Thanks

 

From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Tuesday, May 24, 2016 10:26 AM

To: Bazan, Hernan <hernan.bazan@intel.com>
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] JDBC failing due to networking issues

 

So based on the stack trace this is an older version of the driver. 

 

Is it possible to upgrade the driver (even just to test)?

 

Is this an intermittent problem or you just can't connect at all ?

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

 

On 24 May 2016 at 09:16, Bazan, Hernan <hernan.bazan@intel.com> wrote:

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@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Tuesday, May 24, 2016 9:49 AM
To: Bazan, Hernan <hernan.bazan@intel.com>
Cc: pgsql-jdbc@postgresql.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@postgresintl.com

www.postgresintl.com

 

On 23 May 2016 at 20:48, Bazan, Hernan <hernan.bazan@intel.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