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

A little bit of clarification before continuing: SSL (Secure Sockets Layer) and TLS (Transport Layer Security) are names that nowadays are used interchangeably. The original SSL protocol is deprecated, and TLS is its successor, but since what ended up being TLS was originally planned as version 3.1 of SSL, the old name is still used.

TLS is a security protocol that adds an encryption layer to internet communications. Its most common application is securing the data transfer between web clients and servers operating under the HTTP protocol, which results in the HTTPS implementation. But in reality, it can be used in combination with other protocols, such as in the case of its use with the PostgreSQL protocol.

Erlang ships with its own implementation of functions for TLS protocol support under the :ssl module.

Postgrex SSL Configuration Settings

The Ecto.Adapters.Postgres adapter module for Ecto SQL uses Postgrex for communicating with the database. Postgrex supports SSL connections to the underlying PostgreSQL server via the :ssl configuration option. If :ssl is set to true, :ssl_opts are expected, e.g.:

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

By default, even when SSL is used to establish connections to the DB, PostgreSQL doesn’t perform any verification of the server certificate. However, additional verification of the server’s identity via a chain of trust is needed to prevent spoofing.

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.

DER Extraction from PEM

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

And that’s it! The final solution for feeding a root certificate from a string when establishing database connections to PostgreSQL with verification is really simple after all, but it was tricky to obtain the exact steps since we didn’t find them all in a single place.

I hope this post can help people encountering similar situations when establishing secure connections to their databases. Thanks for reading!