# 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`](https://gist.github.com/chgeuer/387537c47e48c4c084ac9c6dfba41bba)

A few weeks ago, I read an [article](https://techcommunity.microsoft.com/blog/fasttrackforazureblog/connecting-to-azure-sql-database-using-sqlalchemy-and-microsoft-entra-authentica/4259772) 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](https://github.com/elixir-ecto/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](https://github.com/elixir-ecto/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](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) 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](https://portal.azure.com/#view/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/~/RegisteredApps), create yourself a new application, and in the app's "Certificates & secrets" tab create a "Client secret".

![Setting up  the Entra application](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-80f21aff24d4a644230ffcfa52754d5e88c0a216%2Fazure-postgrex-01-entra-app.png?alt=media)

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:

![Azure PostgreSQL configuration](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-e069a2ac8474b74257c4b918db26aabf11cbd1d0%2Fazure-postgrex-02-portal-postgres.png?alt=media)

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.

![Azure PostgreSQL networking configuration](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-88ae9b4586d1c1132b0b94db835915588b9954b2%2Fazure-postgrex-03-postgres-networking.png?alt=media)

## 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](https://livebook.dev/) and get the installer for your platform.

### Installing dependencies

In Livebook, I install the current dependencies for [`postgrex`](https://hexdocs.pm/postgrex/readme.html), [`req`](https://hexdocs.pm/req/readme.html) and [`livebook_env`](https://hexdocs.pm/livebook_env/readme.html).

* `:x509` to simplify certificate handling. (in a previous version of this article, I did it all by hand 🙄)
* `: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:

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

And everything compiles nicely:

![Elixir libraries](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-e116a4c0b526fb9f607a547f1c252d779d2c44ec%2Fazure-postgrex-04-libraries.png?alt=media)

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:

```elixir
LivebookEnv.import_dotenv("/home/joe/Desktop/postgrex_cheat_sheet.txt")
```

![LivebookEnv.import\_dotenv("C:/Users/chgeuer/Desktop/postgrex\_cheat\_sheet.txt")](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-8ee7ef5b6d809d22b2b795e54417f3030f96c026%2Fazure-postgrex-05-sourcing-env.png?alt=media)

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:

```elixir
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()
```

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

![Downloading the certificates](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-6b6fd53718c900be653ec471fc3edf2f2fd667db%2Fazure-postgrex-06-pinning-certs.png?alt=media)

This module essentially downloads root CA certificates from Microsoft and DigiCert (I took the URLs from our [documentation](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). 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...

```elixir
{: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](https://jwt.ms/) is a static web site by Microsoft, which parses JWT tokens using JavaScript exclusively in the user's browser.

![Fetching the access token](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-a63952347405294a91c8d0a43847987a241d75fe%2Fazure-postgrex-07-fetching-access_token.png?alt=media)

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

```elixir
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:

![Connecting to the database](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-5987f23852df936edf565ac00e0f71074b8639e5%2Fazure-postgrex-08-connection-with-entra.png?alt=media)

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

```elixir
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, [])
```

![Running a SQL statement](https://439978545-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDiEVTiIb6z0zL45wfNrM%2Fuploads%2Fgit-blob-1a129465b2b5623553988400bbbddcda61500f62%2Fazure-postgrex-09-running-a-sql-statement.png?alt=media)

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

```elixir
{: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")
)
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://cookbook.geuer-pollmann.de/azure/postgrex-on-azure.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.