Using SSL for PostgreSQL Connections on Elixir

In this post, I’ll describe our approach to correctly forcing SSL verification when establishing PostgreSQL DB connections from an Elixir application, which we did as a way of adding an additional security layer to our DB to prevent threats such as spoofing. What we thought was a trivial task actually ended up needing a lot of trial and error, but we were finally able to come up with the correct approach.

First, I’ll describe the different parts involved in our problem, and then I’ll jump right to the implementation adopted.

TCP Connections on PostgreSQL

The PostgreSQL RDBMS uses its own message-based protocol for communication between clients and database servers. This protocol can run over TCP/IP and IPC sockets. The PostgreSQL team even registered 5432 with IANA as a port number for its protocol.

This protocol supports SSL-encrypted connections, and in turn, Postgrex — the official PostgreSQL driver for Elixir — has support for them.

SSL

Postgrex SSL Configuration Settings

config :my_app, MyApp.Repo,
  ssl: true,
  ssl_opts: [
    # Options go here.
  ]

For the client — in this case, our Elixir application — to verify the DB server certificate, a root certificate must be specified. The server needs to be configured with a leaf certificate signed by the very same root certificate. Ideally, the root certificate should come from a trusted certificate authority.

ℹ️ Note: It’s also possible for the PostgreSQL server to verify the identity of the client, but that scenario is beyond the scope of this blog post.

One way of specifying the root certificate as part of the Ecto configuration is via the :cacertfile option, which expects the path to a single PEM-encoded file, which in turn might also bundle intermediate certificates if needed, e.g.:

config :my_app, MyApp.Repo,
  ssl: true,
  ssl_opts: [
    cacertfile: "root_cert.pem"
  ]

To verify the host, the :verify option should be set to :verify_peer. Additionally, the Erlang ssl docs state:

If the option verify is set to verify_peer the option server_name_indication shall also be specified […]

As such, we need to also add :server_name_indication into the mix. :server_name_indication is an Erlang string() that should match whatever is set as the certificate’s Subject Alternative Name attribute(s) — or the Common Name attribute if no Subject Alternative Name is present.

💡 Tip: One way to check the attributes in a certificate is via OpenSSL. SAN refers to the Subject Alternative Name field (or fields, since multiple fields are allowed), whereas CN refers to the Common Name field. Run the following OpenSSL command with a PEM-encoded certificate file as an input to check its attributes: openssl x509 -in cert.crt -text -noout.

Notice the important distinction that :server_name_indication is an Erlang string. In Elixir, strings are UTF-8 encoded binaries, whereas in Erlang, strings refer to what are known as char lists in Elixir. Hence, to convert an Elixir string to a char list, we need to use Elixir’s Kernel.to_charlist/1 function:

db_host = "example.com"

config :my_app, MyApp.Repo,
  ssl: true,
  ssl_opts: [
    cacertfile: "root_cert.pem",

    verify: :verify_peer,
    server_name_indication: to_charlist(db_host)
  ]

This setup is all that’s needed when you specify a path to the certificate file.

The next section will describe an alternative for cases where you can’t provide a path to the certificate file — which was the scenario we faced.

For our specific use case, going with the :cacertfile route was more cumbersome than fetching the contents of the root certificate from an environment variable as a string.

Luckily for us, in addition to the :cacertfile option, there’s also :cacerts, which expects a list of DER-encoded trusted certificates.

This can be confusing, since :cacertfile expects a PEM-encoded file, whereas :cacerts expects DER-encoded data.

For the sake of this post, we can very broadly simplify the difference as PEM being a text encoding containing Base64- and ASCII-encoded items, while DER is a binary encoding format. A recommended deeper explanation is available in this article: PEM, DER, CRT, and CER: X.509 Encodings and Conversions.

In our case, our string is PEM encoded, so before being able to feed the certificate into the :cacerts option, we need to obtain a DER-encoded representation of the certificate(s).

For this, we can rely on the public_key module from Erlang — specifically, the pem_decode/1 function, which returns a list of {{pki_asn1_type(), der_or_encrypted_der(), not_encrypted | cipher_info()}} tuples.

We need to take the second argument of each tuple, which is the DER-encoded representation of the certificate:

der_certs =
  db_cacerts
  |> :public_key.pem_decode()
  |> Enum.map(fn {_, der, _} -> der end)

With this, our SSL configuration could look like the following:

db_host = "example.com"

config :my_app, MyApp.Repo,
  ssl: true,
  ssl_opts: [
    cacerts: decoded_certs,
    verify: :verify_peer,
    server_name_indication: to_charlist(db_host)
  ]

ℹ️ Note: Refer to the tls_client_option docs from the Erlang ssl module for a full list of possible options for :ssl_opts.

Conclusion