ScriptRunner Encrypted Database Connection

[Irtiza Rizvi]

Hello,

I am trying to create a DB Resource that connects to an external Postgres DB.  The DB connection needs to be encrypted.  Here is my connection string:

jdbc:postgresql://<host>:<port>/<db>?sslmode=verify-ca&sslrootcert=server-ca.pem&sslcert=client-cert.pem&sslkey=client-key.pem

I get the following error:

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not open SSL root certificate file server-ca.pem.
at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:576)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
at com.onresolve.scriptrunner.db.configurations.ExternalDbConfig$_createDataSource_closure1.doCall(ExternalDbConfig.groovy:45)
at com.onresolve.scriptrunner.db.configurations.ExternalDbConfig$_createDataSource_closure1.doCall(ExternalDbConfig.groovy)
at com.atlassian.plugin.util.ContextClassLoaderSwitchingUtil.runInContext(ContextClassLoaderSwitchingUtil.java:48)
at com.atlassian.plugin.util.ContextClassLoaderSwitchingUtil.runInContext(ContextClassLoaderSwitchingUtil.java:25)
at com.atlassian.plugin.util.ContextClassLoaderSwitchingUtil$runInContext.call(Unknown Source)
at com.onresolve.scriptrunner.db.configurations.ExternalDbConfig.createDataSource(ExternalDbConfig.groovy:24)
at com.onresolve.scriptrunner.db.configurations.ExternalDbConfig$createDataSource$1.call(Unknown Source)
at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.createDataSource(AbstractDbConnectionManager.groovy:43)
at com.onresolve.scriptrunner.db.DbConnectionManager$createDataSource$3.call(Unknown Source)
at com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection.createOrUseExistingDataSource(ExternalDatabaseConnection.groovy:134)
at com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection$createOrUseExistingDataSource.callCurrent(Unknown Source)
at com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection.validate(ExternalDatabaseConnection.groovy:167)
at com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection.validate(ExternalDatabaseConnection.groovy)
Caused by: org.postgresql.util.PSQLException: Could not open SSL root certificate file server-ca.pem.
at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:120)
at org.postgresql.core.SocketFactoryFactory.getSslSocketFactory(SocketFactoryFactory.java:61)
at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:33)
at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:441)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:94)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:458)
at org.postgresql.Driver.connect(Driver.java:260)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:123)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:367)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:196)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541)
... 15 more
Caused by: java.io.FileNotFoundException: server-ca.pem (No such file or directory)
at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:117)
... 30 more

I have tried putting the .pem files in the JIRA_HOME and script root directory to no avail.  Where should the files be located to ensure my DB connection is encrypted?

[Artur Moura · Accepted solution]

Hi @Irtiza Rizvi

I believe you should import the SSL certificate chain into Java truststore, then configure the JDBC to force the use of SSL.

Unable to connect to database with SSL enabled during Jira setup - you may refer to this document, which is very similar to what you are looking for.

Hope it helps.

[Irtiza Rizvi]

We imported the certificates to the JVM (following this guide: https://confluence.atlassian.com/kb/how-to-import-a-public-ssl-certificate-into-a-jvm-867025849.html) and used `sslmode=require` in the DB connection string and it works!  Thank you!

Side note, if you are using sslmode=verify-ca or verify-full, ScriptRunner looks for a root.crt file in the /home/jira/.postgresql directory.  See https://www.postgresql.org/docs/9.1/libpq-ssl.html for more info.

[Artur Moura]

Awesome @Irtiza Rizvi !

Glad to hear the issue is solved! Thanks for sharing the resolution steps.