Configuring HikariCP for the Play Framework to connect to Postgres over SSL?

Question

I am trying to get the Play Framework to connect to Postgres database over SSL. SSL is configured with LetsEncrypt on Postgres.

Without SSL, Play has no problem connecting to the database. But with it, I get the error:

java.io.FileNotFoundException: /home/mainuser/.postgresql/root.crt (No such file or directory)

I need to mention that certificate files are placed under the directory /etc/postgresql/13/main/. The ssl connection to the database works just fine both locally and remotely outside Play.

The current application.conf includes:

db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://localhost/my_db?ssl=on"
db.default.username=${?DATABASE_USER}
db.default.password=${?DATABASE_PASSWORD}
db.default.hikaricp.connectionTestQuery = "SELECT 1"


fixedConnectionPool = 5

database.dispatcher {
  executor = "thread-pool-executor"
  throughput = 1
  thread-pool-executor {
    fixed-pool-size = ${fixedConnectionPool}
  }
}

I would like to be able to connect to this database, both locally and remotely over SSL. The official documentation

Any suggestions would be appreciated.

Answer 1

You probably want to set the sslrootcert variable of the PostgreSQL JDBC driver in order to change the location of the certificate. I quote from the driver's manual:

sslrootcert = String

File name of the SSL root certificate. Defaults to defaultdir/root.crt where defaultdir is ${user.home}/.postgresql/ in *nix systems and %appdata%/postgresql/ on windows

It can be a PEM encoded X509v3 certificate

See, for example, https://jdbc.postgresql.org/documentation/head/connect.html. And while I haven't tried it, you should be able to add it as a further argument to your JDBC connection string, db.default.url, like

db.default.url="jdbc:postgresql://localhost/my_db?ssl=on&sslrootcert=/new/path/cert"

if the application.conf setting for it isn't obvious or non-existent.