How can I help Vapor successfully SSL-handshake my PostgreSQL server?

Question

I'm using Vapor on a Ubuntu server to connect to my DigitalOcean-managed PostgreSQL database.

From the command-line, running the following works fine:

psql postgresql://user:password@host:port/dbname?sslmode=require

But running the equivalent with the following code gives me:

Fatal error: Error raised at top level: NIOOpenSSL.NIOOpenSSLError.handshakeFailed(NIOOpenSSL.OpenSSLError.sslError([Error: 337047686 error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed])): file /home/buildnode/jenkins/workspace/oss-swift-5.1-package-linux-ubuntu-18_04/swift/stdlib/public/core/ErrorType.swift, line 200

Here is the code:

    let postgres = PostgreSQLDatabase(config: PostgreSQLDatabaseConfig(
        hostname: Environment.get("POSTGRESQL_HOSTNAME")!,
        port: Int(Environment.get("POSTGRESQL_PORT")!)!,
        username: Environment.get("POSTGRESQL_USERNAME")!,
        database: Environment.get("POSTGRESQL_DATABASE")!,
        password: Environment.get("POSTGRESQL_PASSWORD")!,
        transport: .standardTLS
    ))

Switching the transport argument to .unverifiedTLS works.

I need help to let Vapor work out the SSL connection fine, but I have no idea where to start.

Answer 1

I recently got this working with Vapor 4 and MySQL on Digital Ocean, I suspect the same will work for PostgreSQL. The main bit was configuring Vapor to trust Digital Ocean's certificate.

1. Download the CA certificate from the managed database dashboard on Digital Ocean (the connection details section).

2. Configure the database tlsConfigurataion to trust that certificate. Here's an example of what that could look like:

import NIOSSL

public func configure(_ app: Application) throws {
    app.databases.use(.postgres(
        hostname: Environment.get("DATABASE_HOST") ?? "localhost",
        port: Environment.get("DATABASE_PORT").flatMap(Int.init(_:)) ?? PostgresConfiguration.ianaPortNumber,
        username: Environment.get("DATABASE_USERNAME") ?? "vapor_username",
        password: Environment.get("DATABASE_PASSWORD") ?? "vapor_password",
        database: Environment.get("DATABASE_NAME") ?? "vapor_database",
        tlsConfiguration: try makeTlsConfiguration()
    ), as: .psql)
  // ...
}

private func makeTlsConfiguration() throws -> TLSConfiguration {
    var tlsConfiguration = TLSConfiguration.makeClientConfiguration()
    if let certPath = Environment.get("DATABASE_SSL_CERT_PATH") {
        tlsConfiguration.trustRoots = NIOSSLTrustRoots.certificates(
            try NIOSSLCertificate.fromPEMFile(certPath)
        )
    }
    return tlsConfiguration
}

In this example, I use the DATABASE_SSL_CERT_PATH environment variable to set the path of the downloaded ca-certificate.crt file.