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.

It seems like your client can't verify the certificate of your ubuntu server either because it does not have the required trust anchor to verify the chain of trust or because you are using a self-signed certificate in your ubuntu server. Can you verify which is the case? — jms, Nov 25 '19 at 01:10
I am Having the same issue too. I am using a managed DB from Digital Ocean — Cartisim, Jan 19 '20 at 12:51

score 4 · Accepted Answer · edited Dec 04 '21 at 00:13

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.

Download the CA certificate from the managed database dashboard on Digital Ocean (the connection details section).
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.

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

1 Answers1