How to connect to an Azure-hosted managed identities postgres server from a node app using the Prisma ORM

Introduction

If your organisation requires enhanced security, then they may require databases to be authenticated using Azure managed identities.

If that is the case then connecting to databases through a node app using the Prisma ORM can be problematic - it is not something supported by prisma out of the box.

This article will explain how you can adapt your node/prisma app to allow it to connect to an Azure managed identities postgres database.

The Problem

Azure Managed Identities

If you enable Azure managed identities on a postgres server (as described in this documentation), then instead of connecting to postgres in the traditional way using a standard password, you will instead need to request a token from Azure and use this as a password.

Unlike standard passwords, these tokens only have a limited lifespan (usually in the order of hours), so they will need to be refreshed periodically when making new connections to the database.

Once connections are made, they are valid until closed even if the token expires before the connection is closed, but any new connections will need a new token.

The limited lifespan of these tokens can cause issues for Prisma. It is not something it supports out of the box.

Prisma ORM

The standard way to connect to a postgres database through Prisma is via the DATABASE_URL environment variable, which typically looks like this:

DATABASE_URL="postgresql://username:password@localhost:5432/my_db?schema=public"

The PrismaClient will then use this to generate connections to the specified database and store them in a pool to be used for queries.

This works very well when connecting to a postgres server that uses standard authentication, but it is not ideal for servers that use Azure managed identities authentication.

In the latter case, the ‘passwords’ (tokens) that we use to connect expire every hour, so even if we were to generate one, set it as an environment variable and then immediately start our app, we would only be able to reliably communicate with the database for the lifespan of the token.

So how do we get around this?

Potential Solutions

There are a number of potential solutions listed in the discussions of this issue on prisma’s github page.

Most are far from perfect, with ideas like destroying/recreating the Prisma Client every time a token’s lifespan expires or simply re-requesting a token and retrying the request as soon as a connection fails.

There is one solution hinted at deep within the comments which is much cleaner, and is the basis for this entire article (so big thanks to that person!)

The Solution

The cleanest solution I have found is to give the Prisma Client a ‘password generation’ function, which will request a token from Azure whenever a new connection is made to the database.

Because we are requesting a new token with each new connection, we don’t need to worry about the token’s lifespan, and we don’t need to worry about destroying/recreating Prisma Clients (or any other similar heavy solutions).

Because connections remain valid until closed, we also don’t need to worry about tokens expiring on long-lived connections. Once they are open, they are authenticated.

The main complexity in this solution is in it’s initial implementation - The standard Prisma Client will not allow you to supply a ‘password generation’ function, so you need to create a custom adaptor to do so.

Read on to find out how.

Environment

First, these are the packages you will need. I have also listed the versions I have used in my solution. It might work with other versions but I have not tested them.

• @prisma/client - version 5.22.0.
• @prisma/adapter-pg - version 6.0.0.
• pg - version 8.13.1.
• @azure/identity- version 4.5.0.

I am also assuming you already have:

• A postgres server set up with Azure managed identities authentication, and an empty database within it.
• The Azure CLI, and a user that is capable of generating tokens and has permissions to connect to your chosen database.
• A node app with prisma already set up, with at least an initial migration.

Optional - Connecting Manually

Before building the solution into the app, we will connect manually to the database. It is useful to verify that you can connect manually because there are a lot of moving parts if anything does go wrong, and this is a good way to rule out the connection itself being an issue.

Additionally, we will be able to set up our database with migrations by connecting manually (if required).

First, open a terminal and log in to the Azure CLI (if you haven’t already):

az login

Then we need to manually generate a token:

az account get-access-token --resource-type oss-rdbms

This should generate something like this:

{
  "accessToken": "eyJ0...7Hy1mMNRg",
  "expiresOn": "2024-12-23 12:39:53.000000",
  "expires_on": 1734957593,
  "subscription": "subscription-id-here",
  "tenant": "tenant-id-here",
  "tokenType": "Bearer"
}

We can then use this token as the password when connecting to your database from your database IDE of choice (or simply from psql).

Verify that the token is working by connecting to your database.

Next we will use this token to connect from node/Prisma and run migrations on the Azure managed identities server database.

Optional - Running initial migrations from the node/prisma app

We can copy/paste the access token generated in the previous section into our prisma DATABASE_URL (or into the password variable wherever else you might be generating the prisma client):

DATABASE_URL="postgresql://username:[e-mail]:5432/my_db?schema=my_schema"

If you then run the migrations using the prisma migrate command, then the app should be able to connect and the migrations should be applied successfully.

Custom Prisma Client Generator

We have now verified the connection works and run our migrations. Next we need to give the prisma client the ability to generate tokens when it creates new connections.

First, let’s create a function that can connect to Azure and request the access token:

import { DefaultAzureCredential } from "@azure/identity";

export default async function getAzurePostgresToken(
  azureIdentityId: string,
): Promise<string> {
  const credential = new DefaultAzureCredential({
    managedIdentityClientId: azureIdentityId,
  });

  const tokenResponse = await credential.getToken(
    "https://ossrdbms-aad.database.windows.net/.default",
  );

  if (!tokenResponse || !tokenResponse.token) {
    throw new Error(
      "Could not acquire access token for postgres",
      500,
      false,
    );
  }

  return tokenResponse.token;
}

We can then use this function to request a new token each time a connection is made. There is no way to pass in this function using the default prisma client, so we will need to create a custom adaptor:

import { PrismaClient } from "@prisma/client";
import { Pool } from "pg";
import { PrismaPg } from "@prisma/adapter-pg";

function generateNewPrismaClient(): PrismaClient {
  const pool = new Pool({
    host: config.PG_HOST,
    port: config.PG_PORT,
    database: config.PG_DATABASE,
    user: config.PG_USER,
    ssl: { rejectUnauthorized: true },
    password: async () => {
      return await getAzurePostgresToken(config.AZURE_MANAGED_IDENTITY_ID);
    },
  });

  const adapter = new PrismaPg(pool);
  return new PrismaClient({ adapter });
}

We can then call this function to generate a new Prisma Client and distribute it throughout the app.

Conclusion

Big thanks again to this comment, without which I never would have found this solution.

It took some digging to find that comment. I hope that in making an article around it, it makes the solution more visible for future developers.