Support of Postgres Jdbc driver version >= 42.2.5

[agattung]

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]

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]

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

[henricook]

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]

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]

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

[outkine]

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]

😱

[outkine]

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]

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]

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]

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]

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]

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

[henricook]

@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]

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]

@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.