postgrex.livemd

Postgrex on Azure: How to connect to an Azure PostgreSQL DB from Elixir

Mix.install([
  {:x509, "~> 0.8.10"},
  {:postgrex, "~> 0.19.3"},
  {:req, "~> 0.5.7"},
  {:livebook_env, "~> 1.0"},
  {:kino, "~> 0.14.2"}
])

Introduction

Blog article: https://cookbook.geuer-pollmann.de/azure/postgrex-on-azure

A few weeks ago, I read an article on how to connect to an Azure SQL DB using Python's SQLAlchemy library, featuring Microsoft Entra ID authentication. My personal interest is more on the Elixir/Erlang side of the house, so Elixir (and Postgrex) it is. Right on time were questions in the Elixir Forum where a customer had challenges to get the Elixir-side TLS config right to establish a connection, so that it's the right time for the topic.

tl;dr: There are 2 technically interesting things to learn from this article:

1. To get postgrex library to talk to the TLS-protected Azure PostgreSQL endpoint, the client's SSL options must ensure everything for validating the X.509 certificate chain is available, and you communicate the proper database name via SNI. The Microsoft documentation points to the proper root CA certificates, which allows us to do certificate pinning.
2. The other question is how to get the Microsoft Entra token properly supplied, when establishing the connection to the database. Here it matters to have a token with the right scope/audience, and to have the right username. While I expected the username to be the client_id/application_id or something, it's actually the friendly name of the application in Entra.

For the sake of this article, I include screenshots and IDs of stuff, so we can fully follow along, but I'll delete the actual resources and identities, so no reason to try whether the creds still work 😐.

Azure setup

Create the Entra application registration

Assuming you want to follow along with this article, first lets setup the Azure side. In Microsoft Entra's "App registrations" blade, create yourself a new application, and in the app's "Certificates & secrets" tab create a "Client secret".

In the screenshot above, you can see I created an application called postgrex, which will be our username when we attempt to authenticate with a JWT access token issued by Entra. Fire off vim or Notepad.exe and make yourself a little cheat sheet, like this (obviously whatever values apply to your environment):

TENANT_ID=...
CLIENT_NAME=postgrex
CLIENT_ID=...
CLIENT_SECRET=...

Create your Azure PostgreSQL DB

Next, get yourself an "Azure Database for PostgreSQL". I created a tiny B1ms version for testing purposes.

I picked support for both PostgreSQL authentication, and Microsoft Entra authentication, so I could try both together. During the setup, you specify the admin username and password (for vanilla PostgreSQL authentication), but you also add your previously created app registration to the list of authorized admins:

Enrich your cheat sheet with the rest of the data (name of your instance, and admin user / pass):

AZURE_POSTGRES_HOST=...
AZURE_POSTGRES_ADMIN_USER=...
AZURE_POSTGRES_ADMIN_PASS=...

Now please store your key/value file with the config values on your computer, we'll be using it in the next step.

Firewall rules... For demo purposes, I also opened up the PostgreSQL endpoint to the whole Internet (Public access), and opened my instance to the IP range 0.0.0.0 -- 255.255.255.255. This is a really BAD idea for any production system, so don't shoot yourselves in the foot here.

Running that code should report that our 7 values (TENANT_ID, CLIENT_ID, CLIENT_SECRET, CLIENT_NAME, AZURE_POSTGRES_HOST, AZURE_POSTGRES_ADMIN_USER, AZURE_POSTGRES_ADMIN_PASS) are read into the environment:

LivebookEnv.import_dotenv("C:/Users/chgeuer/Desktop/postgrex_cheat_sheet.txt")

The file previously sourced contains a couple of settings, and looks like this:

TENANT_ID=...
CLIENT_NAME=...
CLIENT_ID=...
CLIENT_SECRET=...
AZURE_POSTGRES_HOST=...
AZURE_POSTGRES_ADMIN_USER=...
AZURE_POSTGRES_ADMIN_PASS=...

A little module for sourcing in

Next, we create an Elixir module that helps us with the X.509 certificate pinning and TLS configuration.

This module essentially downloads root CA certificates from Microsoft and DigiCert (I took the URLs from our documentation. It then transforms the .crt files to base64-encoded PEM syntax, uses Erlang's :public_key.pem.decode/1 function to parse everything, extracts the certificates, and at compile time puts it into the @certs module attribute. This simply means these X.509 certs are only downloaded once when the application is compiled and embedded in the Erlang byte code.

The only thing we will need later is the MicrosoftCerts.ssl_opts/1 function, that creates the :ssl config we need for postgrex.

defmodule MicrosoftCerts do
  defmodule CompileHelpers do
    defp http_get(url) do
      %Req.Response{status: 200, body: body} = Req.get!(url: url)
      body
    end
  
    def download_certs_for_pinning() do
      # https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-networking-ssl-tls#download-root-ca-certificates-and-update-application-clients-in-certificate-pinning-scenarios
      """
      https://www.microsoft.com/pkiops/certs/microsoft%20azure%20rsa%20tls%20issuing%20ca%2004%20-%20xsign.crt
      https://www.microsoft.com/pkiops/certs/Microsoft%20RSA%20Root%20Certificate%20Authority%202017.crt
      https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem
      https://dl.cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem
      https://cacerts.digicert.com/DigiCertGlobalRootCA.crt
      """
      |> String.split(["\n"], trim: true)
      |> Enum.map(fn url ->
        {url, http_get(url)}
      end)
      |> Enum.map(fn {url, data} -> 
        cond do
          url |> String.ends_with?(".crt") -> 
            data 
          url |> String.ends_with?(".pem") -> 
            data
            |> X509.Certificate.from_pem!()
            |> X509.Certificate.to_der()
        end
      end)      
      |> Enum.uniq()
    end
  end

  @certs MicrosoftCerts.CompileHelpers.download_certs_for_pinning()

  def ssl_opts(hostname) do
    [
      protocol: :tls,
      protocol_version: :"tlsv1.3",
      verify: :verify_peer,
      cacerts: @certs,
      server_name_indication: String.to_charlist(hostname),
      depth: 3
    ]
  end
end

MicrosoftCerts.CompileHelpers.download_certs_for_pinning()

Rather than bringing in other dependencies, let's just grab an access token from Microsoft Entra ID, by using the Req HTTP client and POSTing a token issuance request to Entra. As you see, we're using the environment variables for Entra tenant ID, our app's client_id and client_secrets.

The scope "https://ossrdbms-aad.database.windows.net/.default" is the one you need to talk to Azure PostgreSQL and MySQL...

{:ok, %Req.Response{status: 200, body: %{ "access_token" => access_token}}} = 
  Req.request(
    method: :post,
    url: "https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token",
    path_params_style: :curly,
    path_params: [tenant_id: System.get_env("TENANT_ID")],
    form: [
      grant_type: "client_credentials",
      client_id: System.get_env("CLIENT_ID"),
      client_secret: System.get_env("CLIENT_SECRET"),
      scope: "https://ossrdbms-aad.database.windows.net/.default"
    ]
  )

"""
Click [here](https://jwt.ms/#access_token=\##{access_token}) to inspect the access token.
"""
|> Kino.Markdown.new()

With a valid access token in our hands, we're finally able to connect to the database:

db_host = System.get_env("AZURE_POSTGRES_HOST") <> ".postgres.database.azure.com"

{:ok, conn} = Postgrex.start_link(
  hostname: db_host,
  port: 5432, 
  database: "postgres",
  ssl: MicrosoftCerts.ssl_opts(db_host),  
  username: System.get_env("CLIENT_NAME"), 
  password: access_token
)

Assuming the Postgrex.start_link/1 call doesn't fail, the conn variable now contains a process ID, representing the connection to the database:

create_table_query = 
  """
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  );
  """

# Execute the create table query
Postgrex.query!(conn, create_table_query, [])

Using PostgreSQL authentication

Last but not least, of course you can also just use plain vanilla PostgreSQL authN with the username/password approach. But the strong recommendation is to use Entra authN (instead of username/passwords), as this is much better manageable.

{:ok, conn2} = Postgrex.start_link(
  hostname: db_host,
  port: 5432, 
  database: "postgres",
  ssl: MicrosoftCerts.ssl_opts(db_host),  
  username: System.get_env("AZURE_POSTGRES_ADMIN_USER"), 
  password: System.get_env("AZURE_POSTGRES_ADMIN_PASS")
)