Postgrex on Azure - Connecting to Azure PostgreSQL from Elixir

Written 2024-NOV-14 by Christian Geuer-Pollmann

NOTE: If you want to open this whole article as a Livebook, head over to https://gist.github.com/chgeuer/387537c47e48c4c084ac9c6dfba41bba

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 😐.

How to authenticate to PostgreSQL?

In this article, I demonstrate how you can authenticate to Azure PostgreSQL using two different mechanisms, via Microsoft Entra authentication, and via traditional PostgreSQL authentication.

  • Entra ID: Signing in via Entra means you setup a new application in Entra to represent your web application and create a password for that application. When you want to establish a database connection, you first must fetch an access token from Entra to talk to the database.

  • PostgreSQL authentication: In this case, you access the database using a username/password combination you chose during database creation.

Microsoft strongly recommends to go with the Entra ID option. Entra ID allows you to use a single credential to access many different services, like Azure PostgreSQL, storage, KeyVault, Event Hub, and many more. Otherwise you would have to manage (store/protect/rotate) service-specific credentials for each of these services within your application.

Having said that, if for whatever reason you say "Thanks mate, no Entra ID for me right now", then feel free to skip all the Entra-related bits in this article (but don't complain later 🙄).

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=81c45207-40a0-4d7d-a8f3-feeca7c918fd
CLIENT_NAME=postgrex
CLIENT_ID=dc604136-1d05-4be5-b6c1-5e251c8fd25d
CLIENT_SECRET=...

Create your Azure PostgreSQL DB

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

In authentication options, I chose to use 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=elixirdemo1
AZURE_POSTGRES_ADMIN_USER=adminpostgrex
AZURE_POSTGRES_ADMIN_PASS=SuperSecret123.-

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/testing purposes only, 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. Doing this would be a really BAD idea for any production system, so please don't shoot yourselves in the foot here.

Client environment

For our little demo, you might use the awesome Livebook application, an interactive notebook environment for Elixir and Erlang, that allows you to run code step-by-step. Head over to livebook.dev and get the installer for your platform.

Installing dependencies

In Livebook, I install the current dependencies for postgrex, req and livebook_env.

  • :postgrex is the Elixir-side library to connect to the database.

  • :req is a nice HTTP client. I could've used Erlang's native :httpc client, but Req is cool.

  • :livebook_env is a small helper that reads our 'cheat sheet' with config values, and injects them as environment variables.

So your "Notebook dependencies and setup" section should look like this:

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

And everything compiles nicely:

In the next cell, we load our configuration from the file into the environment. 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("/home/joe/Desktop/postgrex_cheat_sheet.txt")

Next, we create an Elixir module that helps us with the X.509 certificate pinning and TLS configuration. Copy/paste this into a new cell:

defmodule MicrosoftCerts do
  defmodule CompileHelpers do
    defp http_get(url) do
      %Req.Response{status: 200, body: body} = Req.get!(url: url)
      body
    end
  
    defp to_pem(cert) when is_binary(cert) do
      cert
      |> Base.encode64()
      |> String.replace(~r/.{64}/, "\\0\n")
      |> then(&"-----BEGIN CERTIFICATE-----\n#{&1}\n-----END CERTIFICATE-----\n\n")
    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
          String.ends_with?(url, ".pem") -> data
          String.ends_with?(url, ".crt") -> to_pem(data)
        end
      end)
      |> Enum.join("\n\n")
      |> :public_key.pem_decode() 
      |> Enum.map(fn {:Certificate, der, :not_encrypted} -> der 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

When you now run a new cell with the command MicrosoftCerts.CompileHelpers.download_certs_for_pinning(), you should see a list with 4 binaries (which are 4 root CA certificates).

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.

Fetch a token the 'hard way'

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"
    ]
  )

IO.puts("https://jwt.ms/#access_token=#{access_token}")

This last IO.puts(...) statement prints out a link which you can open in your web browser, to inspect the access_token...

The access token is just a fragment path, i.e. doesn't get transferred to an external server. jwt.ms is a static web site by Microsoft, which parses JWT tokens using JavaScript exclusively in the user's browser.

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),  
  # The username here is the friendly name of our app...
  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:

So the last step here is to run a query against the database, like this one:

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")
)
PreviousEvent-sourcing into working memory to improve data access latencyNextExcel

Last updated