O Sertão será Cloud

Facebook Youtube Instagram Linkedin

Connecting to Azure PostgreSQL with Azure Functions Using Native Drivers

Por Cláudio RaposoEm BlogPostou

Welcome to our practical guide on connecting Azure Functions to Azure PostgreSQL using various authentication techniques and applying factory concepts! 🎉 In this article, you’ll learn how to configure your development environment and implement a robust, scalable Azure Function. We’ll explore authentication using managed identity and service principal. 🌐

What is Azure Database for PostgreSQL?

Azure Database for PostgreSQL is a managed relational cloud database service offered by Microsoft Azure. It is based on the popular open-source PostgreSQL database management system, known for its robustness, SQL standard compliance, and extensibility. Here are some key features and benefits:

Key Features and Benefits of Azure PostgreSQL:

  • Managed Service: Azure PostgreSQL handles server maintenance, software updates, automatic backups, and disaster recovery.
  • Scalability: Adjust compute and storage capacity as needed without significant downtime.
  • High Availability: Features automatic backups, replication, and failover.

Security:

  • Encryption: Data is encrypted in transit and at rest using SSL/TLS and Transparent Data Encryption (TDE).
  • Access Control: Integration with Azure Active Directory (AAD) for authentication and Role-Based Access Control (RBAC).

Development and Integration:

  • Development Tools: Supports tools like pgAdmin, DBeaver, and RESTful APIs.
  • Integration with Azure Services: Easy integration with services like Azure Functions, Azure Kubernetes Service (AKS), and Azure Logic Apps.

Step 1: Environment Setup

Ensure the following dependencies are installed:

npm install @azure/identity pg

Note: If you need to see Steps 2 to 3, refer to the previous article on the topic:

Como Configurar o Azure Blob Service Client Aplicando os Princípios SOLID em uma Azure Function com…

Step 4: Architecture with Credential Providers

We will create a factory to instantiate the PostgreSQL client using our CredentialProvider.

import { Client } from 'pg';
import { TokenCredential } from '@azure/identity';

/**
 * Factory for creating PostgreSQL Client instances.
 */
class PostgresClientFactory {
  private host: string;
  private user: string;
  private database: string;
  private port: number;
  private ssl: boolean;
  private credentialProvider: CredentialProvider;

  /**
   * Creates a new instance of PostgresClientFactory.
   * @param {string} host - The PostgreSQL host.
   * @param {string} user - The PostgreSQL user.
   * @param {string} database - The database name.
   * @param {number} port - The PostgreSQL port.
   * @param {boolean} ssl - Indicator for using SSL.
   * @param {CredentialProvider} credentialProvider - The credential provider to use.
   */
  constructor(host: string, user: string, database: string, port: number, ssl: boolean, credentialProvider: CredentialProvider) {
    this.host = host;
    this.user = user;
    this.database = database;
    this.port = port;
    this.ssl = ssl;
    this.credentialProvider = credentialProvider;
  }

  /**
   * Creates a PostgreSQL Client instance.
   * @returns {Promise<Client>} A promise that resolves with the configured Client instance.
   */
  async createClient(): Promise<Client> {
    const credential = this.credentialProvider.getCredential();
    const accessToken = await credential.getToken('https://ossrdbms-aad.database.windows.net/.default');
    const client = new Client({
      host: this.host,
      user: this.user,
      password: accessToken.token,
      database: this.database,
      port: this.port,
      ssl: this.ssl
    });
    return client;
  }
}

Step 5: Using the Factory in an Azure Function

Let’s integrate this into an Azure Function.

Example 1: Azure Function with System-Assigned Managed Identity

import { AzureFunction, Context, HttpRequest } from "@azure/functions";
import { PostgresClientFactory } from "./PostgresClientFactory";
import { SystemAssignedManagedIdentityCredentialProvider } from "./SystemAssignedManagedIdentityCredentialProvider";

const host = process.env.AZURE_POSTGRESQL_HOST;
const user = process.env.AZURE_POSTGRESQL_USER;
const database = process.env.AZURE_POSTGRESQL_DATABASE;
const port = Number(process.env.AZURE_POSTGRESQL_PORT);
const ssl = Boolean(process.env.AZURE_POSTGRESQL_SSL);

/**
 * Azure Function using system-assigned managed identity.
 * @param {Context} context - The function context.
 * @param {HttpRequest} req - The HTTP request.
 * @returns {Promise<void>} A promise that resolves when the function is complete.
 */
const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
  const credentialProvider = new SystemAssignedManagedIdentityCredentialProvider();
  const clientFactory = new PostgresClientFactory(host, user, database, port, ssl, credentialProvider);

  const client = await clientFactory.createClient();
  await client.connect();
  // Execute database operations
  await client.end();
  context.res = {
    body: "Azure Function using system-assigned managed identity to access PostgreSQL"
  };
};

export default httpTrigger;

Example 2: Azure Function with User-Assigned Managed Identity

import { AzureFunction, Context, HttpRequest } from "@azure/functions";
import { PostgresClientFactory } from "./PostgresClientFactory";
import { UserAssignedManagedIdentityCredentialProvider } from "./UserAssignedManagedIdentityCredentialProvider";

const host = process.env.AZURE_POSTGRESQL_HOST;
const user = process.env.AZURE_POSTGRESQL_USER;
const database = process.env.AZURE_POSTGRESQL_DATABASE;
const port = Number(process.env.AZURE_POSTGRESQL_PORT);
const ssl = Boolean(process.env.AZURE_POSTGRESQL_SSL);
const clientId = process.env.AZURE_POSTGRESQL_CLIENTID;

/**
 * Azure Function using user-assigned managed identity.
 * @param {Context} context - The function context.
 * @param {HttpRequest} req - The HTTP request.
 * @returns {Promise<void>} A promise that resolves when the function is complete.
 */
const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
  const credentialProvider = new UserAssignedManagedIdentityCredentialProvider(clientId);
  const clientFactory = new PostgresClientFactory(host, user, database, port, ssl, credentialProvider);  

  const client = await clientFactory.createClient();
  await client.connect();
  // Execute database operations
  await client.end();
  context.res = {
    body: "Azure Function using user-assigned managed identity to access PostgreSQL"
  };
};

export default httpTrigger;

Example 3: Azure Function with Service Principal

import { AzureFunction, Context, HttpRequest } from "@azure/functions";
import { PostgresClientFactory } from "./PostgresClientFactory";
import { ServicePrincipalCredentialProvider } from "./ServicePrincipalCredentialProvider";

const host = process.env.AZURE_POSTGRESQL_HOST;
const user = process.env.AZURE_POSTGRESQL_USER;
const database = process.env.AZURE_POSTGRESQL_DATABASE;
const port = Number(process.env.AZURE_POSTGRESQL_PORT);
const ssl = Boolean(process.env.AZURE_POSTGRESQL_SSL);
const tenantId = process.env.AZURE_POSTGRESQL_TENANTID;
const clientId = process.env.AZURE_POSTGRESQL_CLIENTID;
const clientSecret = process.env.AZURE_POSTGRESQL_CLIENTSECRET;

/**
 * Azure Function using Service Principal.
 * @param {Context} context - The function context.
 * @param {HttpRequest} req - The HTTP request.
 * @returns {Promise<void>} A promise that resolves when the function is complete.
 */
const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
  const credentialProvider = new ServicePrincipalCredentialProvider(tenantId, clientId, clientSecret);
  const clientFactory = new PostgresClientFactory(host, user, database, port, ssl, credentialProvider);
  
  const client = await clientFactory.createClient();
  await client.connect();
  // Execute database operations
  await client.end();
  context.res = {
    body: "Azure Function using Service Principal to access PostgreSQL"
  };
};

export default httpTrigger;

Conclusion

Congratulations! You’ve successfully configured Azure PostgreSQL using various authentication techniques in Azure Functions, applying the Factory Pattern to orchestrate connection creation. This modular approach makes the code easier to maintain and extend. 🎉

Stackademic 🎓

Thank you for reading until the end. Before you go:

Connecting to Azure PostgreSQL with Azure Functions Using Native Drivers was originally published in Stackademic on Medium, where people are continuing the conversation by highlighting and responding to this story.

Cláudio Raposo

Cláudio Raposo