Connecting Jira to Postgres with forced SSL is failing

[Klaus Foerschl]

Hello,

i'm trying to setup a Jira 7.3.7 server and connecting it to an existing Postgres 9.5.4 DB. The DB instance is setup to force SSL connections using TLS1.2.

Unfortunately i'm unable to establish the DB connection as it can be found from the catalina.out logfiles.

My dbconfig.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
...

    <url>jdbc:postgresql://postgres.server.name:8888/jiradb?ssl=true</url>
...

---

This is the shortened exception thread from catalina.out:

...
2017-06-23 08:51:07,645 JIRA-Bootstrap INFO      [c.a.j.config.database.SystemDatabaseConfigurationLoader] Reading database configuration from /var/atlassian/application-data/jira/dbconfig.xml
2017-06-23 08:51:07,780 JIRA-Bootstrap INFO      [c.a.jira.startup.JiraStartupLogger] Running JIRA startup checks.
2017-06-23 08:51:07,780 JIRA-Bootstrap INFO      [c.a.jira.startup.JiraStartupLogger] JIRA pre-database startup checks completed successfully.
2017-06-23 08:51:08,189 JIRA-Bootstrap ERROR      [NoModule] Error getting datasource via DBCP: JdbcDatasourceInfo{uri='jdbc:postgresql://postgres.server.name:8888/jiradbp?ssl=true', driverClassName='org.postgresql.Driver', username='conflup', password='********', isolationLevel='null', connectionProperties=null, connectionPoolInfo=ConnectionPoolInfo{maxSize=20, minSize=20, initialSize=null, maxIdle=20, maxWait=30000, sleepTime=300000, lifeTime=600000, deadLockMaxWait=600000, deadLockRetryWait=10000, validationQuery=null, minEvictableTimeMillis=60000, timeBetweenEvictionRunsMillis=300000, poolPreparedStatements=null, testOnBorrow=false, testOnReturn=null, testWhileIdle=true, maxOpenPreparedStatements=null, numTestsPerEvictionRun=null, removeAbandonedOnBorrow=true, removeAbandonedOnMaintanance=null, removeAbandonedTimeout=300, validationQueryTimeout=null, defaultCatalog=null}}
java.sql.SQLException: Cannot create PoolableConnectionFactory (The connection attempt failed.)
...
Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
...
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure
...
2017-06-23 08:51:08,200 JIRA-Bootstrap ERROR      [o.o.c.entity.jdbc.DatabaseUtil] Unable to establish a connection with the database... Error was:org.postgresql.util.PSQLException: The connection attempt failed.
2017-06-23 08:51:08,200 JIRA-Bootstrap ERROR      [o.o.c.entity.jdbc.DatabaseUtil] Could not get table name information from the database, aborting.
2017-06-23 08:51:08,201 JIRA-Bootstrap WARN      [o.a.commons.dbcp2.BasicDataSource] Failed to complete JMX registration
javax.management.InstanceAlreadyExistsException: com.atlassian.jira:name=BasicDataSource
...
2017-06-23 08:51:08,208 JIRA-Bootstrap ERROR      [NoModule] Error getting datasource via DBCP: JdbcDatasourceInfo{uri='jdbc:postgresql://postgres.server.name:8888/jiradbp?ssl=true', driverClassName='org.postgresql.Driver', username='conflup', password='********', isolationLevel='null', connectionProperties=null, connectionPoolInfo=ConnectionPoolInfo{maxSize=20, minSize=20, initialSize=null, maxIdle=20, maxWait=30000, sleepTime=300000, lifeTime=600000, deadLockMaxWait=600000, deadLockRetryWait=10000, validationQuery=null, minEvictableTimeMillis=60000, timeBetweenEvictionRunsMillis=300000, poolPreparedStatements=null, testOnBorrow=false, testOnReturn=null, testWhileIdle=true, maxOpenPreparedStatements=null, numTestsPerEvictionRun=null, removeAbandonedOnBorrow=true, removeAbandonedOnMaintanance=null, removeAbandonedTimeout=300, validationQueryTimeout=null, defaultCatalog=null}}
java.sql.SQLException: Cannot create PoolableConnectionFactory (The connection attempt failed.)
...
Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
...
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure
...
2017-06-23 08:51:08,218 JIRA-Bootstrap ERROR      [o.o.c.entity.jdbc.DatabaseUtil] Unable to establish a connection with the database... Error was:org.postgresql.util.PSQLException: The connection attempt failed.
2017-06-23 08:51:08,218 JIRA-Bootstrap ERROR      [o.o.c.entity.jdbc.DatabaseUtil] Could not get table name information from the database, aborting.
2017-06-23 08:51:08,219 JIRA-Bootstrap INFO      [c.a.j.config.database.DatabaseConfigurationManagerImpl] The database is configured. Now running Database Checklist Launcher
2017-06-23 08:51:08,232 JIRA-Bootstrap WARN      [o.a.commons.dbcp2.BasicDataSource] Failed to complete JMX registration
javax.management.InstanceAlreadyExistsException: com.atlassian.jira:name=BasicDataSource
...
2017-06-23 08:51:08,243 JIRA-Bootstrap ERROR      [NoModule] Error getting datasource via DBCP: JdbcDatasourceInfo{uri='jdbc:postgresql://postgres.server.name:8888/jiradbp?ssl=true', driverClassName='org.postgresql.Driver', username='conflup', password='********', isolationLevel='null', connectionProperties=null, connectionPoolInfo=ConnectionPoolInfo{maxSize=20, minSize=20, initialSize=null, maxIdle=20, maxWait=30000, sleepTime=300000, lifeTime=600000, deadLockMaxWait=600000, deadLockRetryWait=10000, validationQuery=null, minEvictableTimeMillis=60000, timeBetweenEvictionRunsMillis=300000, poolPreparedStatements=null, testOnBorrow=false, testOnReturn=null, testWhileIdle=true, maxOpenPreparedStatements=null, numTestsPerEvictionRun=null, removeAbandonedOnBorrow=true, removeAbandonedOnMaintanance=null, removeAbandonedTimeout=300, validationQueryTimeout=null, defaultCatalog=null}}
java.sql.SQLException: Cannot create PoolableConnectionFactory (The connection attempt failed.)
...
Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
...
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure
...
2017-06-23 08:51:08,253 JIRA-Bootstrap WARN      [c.a.j.appconsistency.db.CollationCheck]
   
    ****************************************************************************************************
    The database collation could not be read. An unsupported collation could cause some functionality to not work
    ****************************************************************************************************
... and so on and so on

---

Would be great if anyone has a hint, what's going wrong.
It's pretty clear, that the SSL handshake is the problem.
Maybe TLS1.2???
Is the builtin postgresql jdbc driver of Jira 7.3.7. able to deal with TLS1.2?

According to my Postgres admin, the db server log says:
"could not accept SSL connection: no shared cipher"
But we don't know what this means. Any idea?

I'm looking forward for any answer that might help us.

Please be aware, that we are in the process of ordering licenses but for the time we are trying to prepare ourselves with the trial version.

Kind regards

Klaus

[Nic Brough -Adaptavist-]

Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure is usually down to having the wrong or no certificates installed on the java system trying to reach the encrypted system.

Have you installed the certificates in Jira's JVM?

[Klaus Foerschl]

Hi.

Yes, i have imported the certificate into the jre. At least i think so.
My Db admin told me he uses self signed certificate for the DB. So he sent me a server.crt file, wich i imported into the cacerts keystore below .../jre/lib/security.

keytool -list shows it as imported.

Since the DB says "no shared cipher" we thought that maybe the JRE and/or jdbc driver, coming with jira, are unable to deal with the servers cipher.
So, i exchanged the postgres jdbc driver .../lib/postgresql-9.1-903.jdbc4-atlassian-hosted.jar with the  PostgreSQL JDBC 4.2 Driver, 42.1.1 driver from jdbc.postgresql.org.
It did not change anything.

Finally, the DB admin now decreased the postgresqls's security settings, to not force TLS1.2 anymore, but run with default postgresql mode, accepting all/more cipers.
THIS finally caused Jire to being able to connect to the DB and do the bootstrap.

So, my new questions are:

- is Jira and it's builtin java and jdbc driver able to communicate with the DB like this:
   SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

- how can we enable this?
   e.g. java runtime options, jdbc connection options, ...?

Many thanks for your help

Klaus

[Nic Brough -Adaptavist-]

I'd have expected to need a client certificate, not a server one, although I understand why that works too.

I'm not sure why TLS1.2 is failing here, I've got to the edge of my knowledge on how SSL works for a database connection.

Note that at this point, it's not JIRA doing anything with SSL, it's the PostGres driver, Tomcat, Java and your database server handling it all.

[Klaus Foerschl]

Hello.

Just to complete/close this topic.
We've found the problem source meanwhile. As supposed already, it was an SSL issue between java and PostgresQL.
The DB is forcing TLS1.2 and uses very strong cipher, which is unknown to any (1.7 or even 1.8) java version.
There's an extension (JCE - http://www.oracle.com/technetwork/java/javase/downloads/jce-7-download-432124.html) downloadable for java, providing those ciphers, with which it now works properly.

Anyhow. Thanks for your answers and suggestions