Production-Ready Managed Identity Implementation for PostgreSQL with Node.js

Overview

This guide provides complete, production-ready patterns for implementing Azure Managed Identity authentication with PostgreSQL in Node.js applications. It addresses the critical challenge that Microsoft documentation doesn't fully cover: token refresh with connection pooling in long-running services.

---

The Challenge

What Makes This Hard

1. Token Lifetime: Azure AD tokens expire after 60 minutes
2. Pool Limitation: The pg library doesn't support updating passwords on existing connection pools
3. Long-Running Services: App Service/Container Apps run continuously and need connections beyond 1 hour
4. Documentation Gap: Microsoft examples show token acquisition but not production-ready pool management

What Microsoft Docs Show (Incomplete)

// ❌ This works for <60 minutes only
const credential = new DefaultAzureCredential();
const token = await credential.getToken('https://ossrdbms-aad.database.windows.net/.default');

const pool = new Pool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: token.token,  // This token expires in 60 minutes!
  // ...
});

Problem: After 60 minutes, all connections fail. The pool is created once and never updated.

---

Solution Architecture

Option 1: Connection-Per-Request with Token Caching (Simplest)

Use when: Low-traffic applications, serverless functions, APIs with infrequent database access

Pros:

• ✅ Simple to implement
• ✅ Always uses fresh tokens
• ✅ No pool management complexity

Cons:

• ⚠️ Higher latency (connection overhead per request)
• ⚠️ Not suitable for high-traffic applications

Option 2: Lazy Pool Recreation (Recommended)

Use when: Production web services, long-running applications, moderate to high traffic

Pros:

• ✅ Connection pooling for performance
• ✅ Automatic token refresh
• ✅ Graceful pool rotation
• ✅ Production-ready

Cons:

• ⚠️ More complex implementation
• ⚠️ Brief connection churn during rotation

Option 3: Azure Service Connector (Easiest Setup)

Use when: Using Azure CLI/Portal to configure connections

Pros:

• ✅ Automatic infrastructure setup
• ✅ Environment variables configured automatically
• ✅ Database user created automatically

Cons:

• ⚠️ Still requires code-level token refresh
• ⚠️ Less control over configuration

---

Implementation Patterns

Pattern 1: Connection-Per-Request (Simplest)

const { DefaultAzureCredential } = require('@azure/identity');
const { Client } = require('pg');

// Cache the credential to reuse across requests
const credential = new DefaultAzureCredential();

// Get a fresh token and create a connection
async function getConnection() {
  const tokenResponse = await credential.getToken(
    'https://ossrdbms-aad.database.windows.net/.default'
  );

  const client = new Client({
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT || '5432'),
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: tokenResponse.token, // Fresh token every time
    ssl: { rejectUnauthorized: false },
  });

  await client.connect();
  return client;
}

// Usage in your API routes
async function queryDatabase(sql, params) {
  const client = await getConnection();
  try {
    const result = await client.query(sql, params);
    return result;
  } finally {
    await client.end(); // Always close the connection
  }
}

module.exports = { queryDatabase };

When to use:

• Azure Functions (serverless)
• Low-traffic APIs (<100 requests/min)
• Background jobs that run periodically

---

Pattern 2: Lazy Pool Recreation (Production-Ready)

const { Pool } = require('pg');
const { DefaultAzureCredential } = require('@azure/identity');

class ManagedIdentityPool {
  constructor() {
    this.pool = null;
    this.tokenExpiry = null;
    this.credential = new DefaultAzureCredential();
    this.isRefreshing = false;
    
    // Configuration
    this.config = {
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT || '5432'),
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      ssl: { rejectUnauthorized: false },
      max: 20,
      min: 5,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 10000,
    };
  }

  async getToken() {
    const tokenResponse = await this.credential.getToken(
      'https://ossrdbms-aad.database.windows.net/.default'
    );
    return tokenResponse;
  }

  async createPool() {
    const tokenResponse = await this.getToken();
    
    // Set expiry to 50 minutes (refresh before actual expiration)
    this.tokenExpiry = Date.now() + (50 * 60 * 1000);
    
    const pool = new Pool({
      ...this.config,
      password: tokenResponse.token,
    });

    // Test the connection
    try {
      const client = await pool.connect();
      console.log('✅ Database pool connected with managed identity');
      client.release();
    } catch (error) {
      console.error('❌ Database pool connection failed:', error.message);
      throw error;
    }

    return pool;
  }

  async ensureValidPool() {
    const needsRefresh = !this.pool || 
                        !this.tokenExpiry || 
                        Date.now() >= this.tokenExpiry;

    if (!needsRefresh) {
      return this.pool;
    }

    // Prevent concurrent refreshes
    if (this.isRefreshing) {
      // Wait for ongoing refresh
      while (this.isRefreshing) {
        await new Promise(resolve => setTimeout(resolve, 100));
      }
      return this.pool;
    }

    this.isRefreshing = true;

    try {
      console.log('🔄 Refreshing database connection pool...');
      
      const oldPool = this.pool;
      const newPool = await this.createPool();
      
      // Swap to new pool
      this.pool = newPool;
      
      // Gracefully close old pool (let existing connections finish)
      if (oldPool) {
        setTimeout(async () => {
          try {
            await oldPool.end();
            console.log('✅ Old pool closed gracefully');
          } catch (error) {
            console.error('⚠️ Error closing old pool:', error.message);
          }
        }, 5000); // Wait 5 seconds for in-flight queries
      }

      console.log('✅ Database pool refreshed successfully');
      return this.pool;
    } finally {
      this.isRefreshing = false;
    }
  }

  async query(text, params) {
    const pool = await this.ensureValidPool();
    
    try {
      return await pool.query(text, params);
    } catch (error) {
      // If query fails due to auth, force refresh and retry once
      if (error.message.includes('password authentication failed') ||
          error.message.includes('token')) {
        console.warn('⚠️ Auth error detected, forcing pool refresh...');
        this.tokenExpiry = 0; // Force refresh
        const freshPool = await this.ensureValidPool();
        return await freshPool.query(text, params);
      }
      throw error;
    }
  }

  async getClient() {
    const pool = await this.ensureValidPool();
    return await pool.connect();
  }

  async end() {
    if (this.pool) {
      await this.pool.end();
      this.pool = null;
      this.tokenExpiry = null;
    }
  }
}

// Singleton instance
let poolInstance = null;

function getPool() {
  if (!poolInstance) {
    poolInstance = new ManagedIdentityPool();
  }
  return poolInstance;
}

// Export interface
module.exports = {
  query: async (text, params) => {
    const pool = getPool();
    return pool.query(text, params);
  },
  
  getClient: async () => {
    const pool = getPool();
    return pool.getClient();
  },
  
  end: async () => {
    if (poolInstance) {
      await poolInstance.end();
      poolInstance = null;
    }
  }
};

When to use:

• Production web APIs
• High-traffic applications
• Long-running services
• App Service, Container Apps, AKS

Key features:

• ✅ Automatic token refresh before expiration
• ✅ Graceful pool rotation (no dropped connections)
• ✅ Retry logic for auth failures
• ✅ Thread-safe refresh (prevents concurrent refreshes)
• ✅ Connection pooling for performance

---

Pattern 3: Hybrid Approach (Dev + Prod)

const { Pool } = require('pg');
const { DefaultAzureCredential } = require('@azure/identity');

const isAzure = process.env.WEBSITE_INSTANCE_ID !== undefined;
const useManagedIdentity = isAzure && !process.env.DB_PASSWORD;

async function createDatabasePool() {
  const baseConfig = {
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT || '5432'),
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
    max: 20,
    min: 5,
    idleTimeoutMillis: 30000,
  };

  if (useManagedIdentity) {
    // Use the ManagedIdentityPool class from Pattern 2
    const { ManagedIdentityPool } = require('./managed-identity-pool');
    return new ManagedIdentityPool(baseConfig);
  } else {
    // Local development: use password
    console.log('🔧 Using password authentication (local development)');
    return new Pool({
      ...baseConfig,
      password: process.env.DB_PASSWORD,
    });
  }
}

module.exports = { createDatabasePool };

When to use:

• Apps that run both locally and in Azure
• Teams with multiple developers
• CI/CD pipelines

---

Azure Setup Requirements

1. Enable Managed Identity on App Service

# Enable system-assigned managed identity
az webapp identity assign \
  --resource-group <resource-group> \
  --name <app-service-name>

# Get the principal ID (save this)
az webapp identity show \
  --resource-group <resource-group> \
  --name <app-service-name> \
  --query principalId -o tsv

2. Configure PostgreSQL for Entra Authentication

# Set Entra admin
az postgres flexible-server ad-admin create \
  --resource-group <resource-group> \
  --server-name <postgres-server> \
  --display-name <your-email> \
  --object-id <your-object-id>

3. Create Database User for Managed Identity

-- Connect to PostgreSQL as admin
-- Then create a user for the managed identity

-- Get the managed identity name (usually the app service name)
SET aad_validate_oids_in_tenant = off;

-- Create the user
CREATE ROLE "<app-service-name>" WITH LOGIN IN ROLE azure_ad_user;

-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE <database-name> TO "<app-service-name>";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "<app-service-name>";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "<app-service-name>";

4. Set Environment Variables

# In App Service Configuration
DB_HOST=<server-name>.postgres.database.azure.com
DB_PORT=5432
DB_NAME=<database-name>
DB_USER=<app-service-name>  # Same as managed identity name
DB_SSL=true
# DO NOT SET DB_PASSWORD - let managed identity handle it

5. Allow Azure Services in Firewall

az postgres flexible-server firewall-rule create \
  --resource-group <resource-group> \
  --name <postgres-server> \
  --rule-name AllowAzureServices \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

---

Using Azure Service Connector (Alternative)

Service Connector automates steps 2-4 above:

az webapp connection create postgres-flexible \
  --resource-group <resource-group> \
  --name <app-service-name> \
  --target-resource-group <resource-group> \
  --server <postgres-server> \
  --database <database-name> \
  --system-identity \
  --client-type node

This command automatically:

• Enables managed identity
• Sets PostgreSQL Entra admin
• Creates database user
• Sets environment variables
• Configures firewall

---

Error Handling Best Practices

1. Handle Token Acquisition Failures

async function getTokenWithRetry(maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      const credential = new DefaultAzureCredential();
      const token = await credential.getToken(
        'https://ossrdbms-aad.database.windows.net/.default'
      );
      return token;
    } catch (error) {
      console.error(`Token acquisition attempt ${i + 1} failed:`, error.message);
      if (i === maxRetries - 1) throw error;
      await new Promise(resolve => setTimeout(resolve, 1000 * (i + 1))); // Exponential backoff
    }
  }
}

2. Handle Connection Failures

async function queryWithRetry(sql, params, maxRetries = 2) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await pool.query(sql, params);
    } catch (error) {
      const isAuthError = error.message.includes('password authentication') ||
                         error.message.includes('token') ||
                         error.message.includes('authentication failed');
      
      if (isAuthError && i < maxRetries - 1) {
        console.warn('Auth error, refreshing pool and retrying...');
        await pool.ensureValidPool(); // Force refresh
        continue;
      }
      throw error;
    }
  }
}

3. Graceful Shutdown

// In your server.js
process.on('SIGTERM', async () => {
  console.log('SIGTERM received, closing database pool...');
  await pool.end();
  process.exit(0);
});

process.on('SIGINT', async () => {
  console.log('SIGINT received, closing database pool...');
  await pool.end();
  process.exit(0);
});

---

Testing

Test Locally with Service Principal

# Create a service principal for local testing
az ad sp create-for-rbac --name <sp-name> --role Contributor \
  --scopes /subscriptions/<subscription-id>

# Set environment variables
export AZURE_TENANT_ID=<tenant-id>
export AZURE_CLIENT_ID=<client-id>
export AZURE_CLIENT_SECRET=<client-secret>
export DB_HOST=<postgres-server>.postgres.database.azure.com
export DB_USER=<sp-name>
export DB_NAME=<database-name>
export DB_SSL=true

Test Token Refresh

// Test script to verify token refresh works
async function testTokenRefresh() {
  const pool = getPool();
  
  console.log('Initial query...');
  await pool.query('SELECT 1');
  
  console.log('Forcing token expiry...');
  pool.tokenExpiry = 0; // Force immediate refresh
  
  console.log('Query after forced refresh...');
  await pool.query('SELECT 1');
  
  console.log('✅ Token refresh test passed');
}

---

Common Pitfalls

❌ Pitfall 1: Creating Pool Only Once

// BAD: Pool created once, token expires after 60 minutes
const pool = new Pool({ password: token.token });

Solution: Use Pattern 2 with automatic refresh

❌ Pitfall 2: Not Handling Token Refresh

// BAD: Refreshes token but doesn't update pool
setInterval(async () => {
  const token = await getToken();
  // Token is refreshed but pool still uses old password!
}, 45 * 60 * 1000);

Solution: Recreate pool with new token, not just refresh token

❌ Pitfall 3: Incorrect Database Username

// BAD: Using PostgreSQL admin username
DB_USER=postgres

// GOOD: Using managed identity name
DB_USER=my-app-service-name

Solution: Username must match managed identity name

❌ Pitfall 4: Not Granting Permissions

-- BAD: User created but no permissions
CREATE ROLE "my-app" WITH LOGIN;

-- GOOD: User with proper permissions
CREATE ROLE "my-app" WITH LOGIN IN ROLE azure_ad_user;
GRANT ALL PRIVILEGES ON DATABASE mydb TO "my-app";

Solution: Grant explicit permissions to managed identity user

❌ Pitfall 5: Hardcoding Scope

// BAD: Wrong scope
credential.getToken('https://database.windows.net/.default');

// GOOD: Correct scope for PostgreSQL
credential.getToken('https://ossrdbms-aad.database.windows.net/.default');

Solution: Use correct scope for Azure Database for PostgreSQL

---

Performance Considerations

Connection Pool Sizing

{
  max: 20,        // Maximum connections (adjust based on traffic)
  min: 5,         // Minimum connections (keep warm)
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 10000,  // Fail fast if can't connect
}

Recommendations:

• Low traffic: max: 10, min: 2
• Medium traffic: max: 20, min: 5
• High traffic: max: 50, min: 10

Token Caching

DefaultAzureCredential automatically caches tokens, so reuse the same instance:

// GOOD: Reuse credential across requests
const credential = new DefaultAzureCredential();

// BAD: Creating new credential every time
async function getToken() {
  const cred = new DefaultAzureCredential(); // Inefficient!
  return await cred.getToken('...');
}

---

Monitoring

Log Token Refresh Events

console.log('🔄 Token refreshing in:', Math.floor((this.tokenExpiry - Date.now()) / 1000), 'seconds');

Application Insights

const appInsights = require('applicationinsights');

// Track token refresh
appInsights.defaultClient.trackEvent({
  name: 'DatabaseTokenRefresh',
  properties: {
    timeToExpiry: this.tokenExpiry - Date.now(),
  }
});

// Track auth failures
appInsights.defaultClient.trackException({
  exception: error,
  properties: {
    errorType: 'DatabaseAuthFailure',
  }
});

---

Decision Tree

Do you need managed identity for PostgreSQL?
│
├─ Running in Azure? ──No──> Use password authentication
│                             (stored in Key Vault or env vars)
│
└─ Yes
   │
   ├─ Low traffic (<100 req/min)? ──Yes──> Use Pattern 1 (connection-per-request)
   │
   └─ No (high traffic or long-running)
      │
      └──> Use Pattern 2 (lazy pool recreation)
           │
           ├─ Want easy setup? ──Yes──> Use Azure Service Connector + Pattern 2
           │
           └─ No (full control)
              │
              └──> Manual setup + Pattern 2

---

Complete Example

See the implementation in Pattern 2 above. For a complete working repository structure:

backend/
├── config/
│   ├── database.js              # Pattern 2 implementation
│   └── managed-identity-pool.js # ManagedIdentityPool class
├── routes/
│   └── todos.js                 # Use: const { query } = require('../config/database')
└── server.js                    # Graceful shutdown handling

---

Resources

• Tutorial: Connect to Azure databases with managed identity
• Passwordless connections guide
• Azure Identity SDK for JavaScript
• PostgreSQL Entra authentication

---

Summary

For Production Node.js + PostgreSQL + Managed Identity:

1. ✅ Use Pattern 2 (Lazy Pool Recreation) for long-running services
2. ✅ Refresh tokens before expiration (50 minutes, not 60)
3. ✅ Implement graceful pool rotation to avoid dropped connections
4. ✅ Add retry logic for authentication failures
5. ✅ Use Service Connector for easier setup (optional)
6. ✅ Test both local (password) and Azure (managed identity) modes
7. ✅ Monitor token refresh events in Application Insights

The key insight: Microsoft documentation shows token acquisition but not pool management. Production implementations must handle both.