Support of Postgres Jdbc driver version >= 42.2.5 #234

agattung · 2019-11-15T09:02:14Z

Problem
After change to Postgres Jdbc Driver 42.2.8 an exception occured during application startup:
Failed to obtain JDBC Connection; nested exception is org.postgresql.util.PSQLException: Could not open SSL root certificate file //.postgresql/root.crt.

Reason is that in version 42.2.5 ssl=true implies sslmode=require as stated in https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.5.

The Jdbc Url would work with version >=42.2.5 when either "ssl=true" is removed or "sslmode=prefer" is appended.

Workaround?
Is there a workaround to set the Jdbc Url manually for applications where Boxfuse manages the DB? I could not find any way of overwriting the jdbc Url from outside. Using boxfuse.envparam I ran into "ERROR: Environment variables starting with BOXFUSE are reserved for Boxfuse's own use: BOXFUSE_DATABASE_URL".

outkine · 2020-07-01T16:18:00Z

I'm also having this issue with the Play framework. I spent a long time trying to figure out how to append sslmode=prefer to the database url, until I realized that Boxfuse passes the url automatically as a command line argument:

-Ddb.default.url=jdbc:postgresql://10.0.2.2:5433/boxfuse-dev-db?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactor

Because these arguments take precedent over the Play typesafe configuration, there is no way to override this.

henricook · 2020-11-14T23:06:40Z

This has also forced me to downgrade my postgres version in a play deployment as a new (paying) customer

henricook · 2020-11-15T08:58:47Z

In fact, it hasn't solved it for me - i'm seeing this error with a new play app (only ping endpoints) and "org.postgresql" % "postgresql" % "42.2.5" - can you help?

Edit: this went away when I downgraded to version 42.2.4

outkine · 2020-11-15T16:43:52Z

This combination has worked well for me:

scalaVersion := "2.12.11"
libraryDependencies ++= Seq(
  "org.postgresql" % "postgresql" % "42.1.4",
  "io.getquill" %% "quill-jasync-postgres" % "3.5.3-SNAPSHOT",
)
libraryDependencies ++= Seq(
  "org.flywaydb" %% "flyway-play" % "6.0.0"
)

That being said, it seems like this project is unmaintained, so I'd recommend migrating away if possible.

henricook · 2020-11-15T17:31:06Z

Boxfuse is unmaintained?! It's still recommended by play framework and I've just signed up with them and started paying

outkine · 2020-11-15T18:06:56Z

The infrastructure is all online, but the repos/website/social media haven't been updated in over a year (check the contrib history for this repo), and the head maintainer is unresponsive.

henricook · 2020-11-15T18:08:00Z

😱

outkine · 2020-11-15T18:18:33Z

It's really quite unfortunate because there don't seem to be any good alternatives to Boxfuse for deploying Play apps. I'm considering managing AWS manually with a bunch of terraform files, but that's much more difficult to maintain.

henricook · 2020-11-15T18:22:58Z

I'd love to know what you end up doing. I've used Scalingo who are great for play apps but I don't think they can do this Singapore zone for me

outkine · 2020-11-15T18:28:02Z

Wow, thanks for the recommendation, not sure how I missed Scalingo. Once I have a bit more time I'll probably create a new issue in this repo for migrating options, so we'll be able to continue the discussion there.

axelfontaine · 2020-11-16T15:09:34Z

Boxfuse is unmaintained?! It's still recommended by play framework and I've just signed up with them and started paying

No, it is not unmaintained. We've had significant resources being tied up this year with the transition following the sale of the Flyway side of our business. However that is coming to an end and we expect the pace of development to increase significantly again in 2021.

outkine · 2020-11-16T16:23:57Z

That's really great to hear! @axelfontaine will Boxfuse still be offering student/open source licenses? I reached out about half a year ago and haven't heard back.

axelfontaine · 2020-11-16T17:18:39Z

@outkine Not at this time. It is something we may look into again in the future, no promises though.

henricook · 2020-11-22T14:57:13Z

@axelfontaine This has come back to bite me with a vengeance, i've been forced to use a dependency that references the postgres driver itself (i.e. outside of my control). As a result it uses > 42.2.4 which means any query I try to run on my boxfuse instance ends in:

Caused by: org.postgresql.util.PSQLException: Could not open SSL root certificate file //.postgresql/root.crt

Please, us there anything you can do to help? Is that directory where it's looking for the cert just incorrect or something?

Whole stack trace:

020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb ! @7hp05l8cl - Internal server error, for (GET) [/prices/latest] ->
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb play.api.UnexpectedException: Unexpected exception[SQLTransientConnectionException: db - Connection is not available, request timed out after 30000ms.]
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:355)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:261)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at play.filters.cors.AbstractCORSPolicy$$anonfun$1.applyOrElse(AbstractCORSPolicy.scala:128)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at play.filters.cors.AbstractCORSPolicy$$anonfun$1.applyOrElse(AbstractCORSPolicy.scala:126)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at scala.concurrent.impl.Promise$Transformation.run(Promise.scala:454)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at play.api.libs.streams.Execution$trampoline$.execute(Execution.scala:70)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at scala.concurrent.impl.Promise$Transformation.submitWithValue(Promise.scala:393)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at scala.concurrent.impl.Promise$DefaultPromise.submitWithValue(Promise.scala:302)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at scala.concurrent.impl.Promise$DefaultPromise.tryComplete0(Promise.scala:249)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at scala.concurrent.impl.Promise$Transformation.run(Promise.scala:468)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1402)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb Caused by: java.sql.SQLTransientConnectionException: db - Connection is not available, request timed out after 30000ms.
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.jdbc.hikaricp.HikariCPJdbcDataSource.createConnection(HikariCPJdbcDataSource.scala:14)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:494)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:46)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:37)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.basic.BasicBackend$DatabaseDef.acquireSession(BasicBackend.scala:250)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.basic.BasicBackend$DatabaseDef.acquireSession$(BasicBackend.scala:249)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.jdbc.JdbcBackend$DatabaseDef.acquireSession(JdbcBackend.scala:37)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at slick.basic.BasicBackend$DatabaseDef$$anon$3.run(BasicBackend.scala:275)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.lang.Thread.run(Thread.java:748)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb Caused by: org.postgresql.util.PSQLException: Could not open SSL root certificate file //.postgresql/root.crt.
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:140)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.core.SocketFactoryFactory.getSslSocketFactory(SocketFactoryFactory.java:61)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.ssl.MakeSSL.convert(MakeSSL.java:34)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:446)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:140)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:197)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:217)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.Driver.makeConnection(Driver.java:458)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.Driver.connect(Driver.java:260)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:706)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:692)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	... 3 common frames omitted
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb Caused by: java.io.FileNotFoundException: /.postgresql/root.crt (No such file or directory)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.io.FileInputStream.open0(Native Method)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.io.FileInputStream.open(FileInputStream.java:195)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.io.FileInputStream.<init>(FileInputStream.java:138)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at java.io.FileInputStream.<init>(FileInputStream.java:93)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	at org.postgresql.ssl.LibPQFactory.<init>(LibPQFactory.java:137)
2020-11-22 14:36:19.141 my-backend:0.0.0.1606055135325 i-0adf45ae7f21ec8eb 	... 20 common frames omitted

henricook · 2020-11-22T15:50:16Z

Desperate times call for desperate measures. I've got around this problem with a dirty little hack. My app is based on play framework, I've defined a new set of config keys for the database connection that aren't on the command line (i.e. not specified by boxfuse). I've based them off the boxfuse provided variables and added a couple of tweaks of my own to get around this issue.

It's ugly as hell, but it works. This explanation is based on a Play app 2.8.2 app, Scala 2.13, with Slick 3.3.x and postgres:

So instead of using slick.dbs.default.db.url - change the default to something else. Boxfuse only seems to provide slick arguments as if it's the default database. I created slick.dbs.mydb.db.url. Next, override these five values (plus any more you have with the default prefix) using your new prefix and change the boxfuse values as below to workaround this bug:

slick.dbs.mydb.profile="slick.jdbc.PostgresProfile$"
slick.dbs.mydb.db.driver="org.postgresql.Driver"
slick.dbs.mydb.db.url=${db.default.url}"&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory"
slick.dbs.mydb.db.user=${db.default.username}
slick.dbs.mydb.db.password=${db.default.password}`

And I had to add an attribute throughout my code where I was creating DatabaseConfigProviders - here's an example from one class:

class BoardRepository @Inject() (@NamedDatabase("mydb") protected val dbConfigProvider: DatabaseConfigProvider)
    extends HasDatabaseConfigProvider[JdbcProfile] with Logging {

It's the @NamedDatabase attribute you need to add

bbatarelo · 2021-03-04T23:01:28Z

@henricook there is actually a more elegant solution. You can actually have access to finished preprocessed application configuration in Play Framework and you have one more chance to modify it using your custom application loader.

Here's a custom application loader made for illustration. Basically it takes whatever is in slick db url, removes everything after ? and appends ssl=false instead then overwrites the configuration. This sort of configuration solves all sorts of ssl issues with postgres driver and Boxfuse.

You need to register this module in application.conf like so:
play.application.loader: "modules.CustomApplicationLoader",

package modules

import play.api.{ApplicationLoader, Configuration}
import play.api.inject.guice.{GuiceApplicationBuilder, GuiceApplicationLoader}

class CustomApplicationLoader extends GuiceApplicationLoader() {
  override protected def builder(context: ApplicationLoader.Context): GuiceApplicationBuilder = {

    val DB_URL_PATH = "slick.dbs.default.db.url"

    val dbUrl = context.initialConfiguration.get[String](DB_URL_PATH)

    val newUrl = dbUrl.replaceAll("\\?.*", "?ssl=false")

    val newConfig = context.initialConfiguration ++ Configuration(DB_URL_PATH -> newUrl)

    initialBuilder.in(context.environment).loadConfig(newConfig).overrides(overrides(context): _*)
  }
}

This approach is relatively clean since you don't need to modify your app anywhere and reverting it means commenting out one line in app config or just removing one class and config entry.

axelfontaine added c: image-builder d: postgresql t: bug labels Jan 30, 2021

axelfontaine mentioned this issue Feb 9, 2021

Failure to parse application-prod.yml in JHipster 6.10 fat jar #244

Open

This was referenced Apr 13, 2022

Required changes after upgrading postgres jdbc driver to 42.3.3 Azure-Samples/open-liberty-on-aks#19

Merged

Required changes after upgrading postgres jdbc driver to 42.3.3 Azure-Samples/open-liberty-on-aro#17

Merged

axelfontaine mentioned this issue Mar 7, 2024

PSQLException: The connection attempt failed. #300

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Support of Postgres Jdbc driver version >= 42.2.5 #234

Support of Postgres Jdbc driver version >= 42.2.5 #234

agattung commented Nov 15, 2019

outkine commented Jul 1, 2020

henricook commented Nov 14, 2020 •

edited

Loading

henricook commented Nov 15, 2020 •

edited

Loading

outkine commented Nov 15, 2020

henricook commented Nov 15, 2020

outkine commented Nov 15, 2020

henricook commented Nov 15, 2020

outkine commented Nov 15, 2020

henricook commented Nov 15, 2020

outkine commented Nov 15, 2020

axelfontaine commented Nov 16, 2020 •

edited

Loading

outkine commented Nov 16, 2020

axelfontaine commented Nov 16, 2020

henricook commented Nov 22, 2020 •

edited

Loading

henricook commented Nov 22, 2020 •

edited

Loading

bbatarelo commented Mar 4, 2021

Support of Postgres Jdbc driver version >= 42.2.5 #234

Support of Postgres Jdbc driver version >= 42.2.5 #234

Comments

agattung commented Nov 15, 2019

outkine commented Jul 1, 2020

henricook commented Nov 14, 2020 • edited Loading

henricook commented Nov 15, 2020 • edited Loading

outkine commented Nov 15, 2020

henricook commented Nov 15, 2020

outkine commented Nov 15, 2020

henricook commented Nov 15, 2020

outkine commented Nov 15, 2020

henricook commented Nov 15, 2020

outkine commented Nov 15, 2020

axelfontaine commented Nov 16, 2020 • edited Loading

outkine commented Nov 16, 2020

axelfontaine commented Nov 16, 2020

henricook commented Nov 22, 2020 • edited Loading

henricook commented Nov 22, 2020 • edited Loading

bbatarelo commented Mar 4, 2021

henricook commented Nov 14, 2020 •

edited

Loading

henricook commented Nov 15, 2020 •

edited

Loading

axelfontaine commented Nov 16, 2020 •

edited

Loading

henricook commented Nov 22, 2020 •

edited

Loading

henricook commented Nov 22, 2020 •

edited

Loading