Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

ScriptRunner Encrypted Database Connection

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?

1 answer

1 accepted

0 votes
Answer accepted
Artur Moura Atlassian Team Dec 23, 2021

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.

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.

Like Artur Moura likes this
Artur Moura Atlassian Team Dec 30, 2021

Awesome @Irtiza Rizvi !

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Software

👋 Looking for 15-20 volunteers to test Atlassian training content

Hi everyone! Are you interested in beta testing Atlassian University’s newest (unreleased!) training course? We’re looking for 15-20 volunteers to test our newest training course, Basic reporting...

518 views 19 25
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you