MSDN Tutorials

Integrating PostgreSQL with Azure App Services

This tutorial will guide you through the process of connecting your Azure App Service application to a PostgreSQL database. We'll cover setting up the database, configuring your application, and best practices for managing the connection.

Prerequisites

• An active Azure subscription.
• An Azure App Service application deployed (e.g., a Web App, API App).
• Basic knowledge of PostgreSQL and your application's programming language/framework.

Step 1: Set up Azure Database for PostgreSQL

Azure Database for PostgreSQL offers a fully managed relational database service. You can choose between Single Server, Flexible Server, or Hyperscale (Citus) based on your needs.

1. Navigate to the Azure portal.
2. Search for "Azure Database for PostgreSQL" and select it.
3. Click "Create".
4. Choose your desired deployment option (e.g., Single Server for simplicity).
5. Fill in the required details: resource group, server name, admin credentials, region, and pricing tier.
6. Configure server parameters like storage and networking. Ensure "Allow access to Azure services" is enabled if your App Service is in a VNet.
7. Click "Review + create" and then "Create".

Once deployed, note down your server name, database name, username, and password. You'll need these for connecting your application.

Step 2: Configure Application Connection Settings

The most secure and flexible way to manage database credentials is by using application settings in Azure App Service. This avoids hardcoding sensitive information directly into your code.

1. In your App Service's menu, navigate to "Configuration".
2. Under "Application settings", click "New application setting".
3. Add settings for your database connection. Common names include:
   • POSTGRESQL_SERVER_NAME: Your PostgreSQL server FQDN.
   • POSTGRESQL_DATABASE_NAME: The name of your database.
   • POSTGRESQL_USERNAME: The admin username for your database.
   • POSTGRESQL_PASSWORD: The admin password for your database.
   • POSTGRESQL_CONNECTION_STRING: A combined connection string (format depends on your language/framework).
4. Click "Save" at the top. Your application will automatically pick up these new settings upon restart.

Example Connection String (C#/.NET)

For .NET applications using Npgsql, a typical connection string might look like this:

Host={POSTGRESQL_SERVER_NAME};Database={POSTGRESQL_DATABASE_NAME};Username={POSTGRESQL_USERNAME};Password={POSTGRESQL_PASSWORD};Pooling=true;Connection Lifetime=180;Connection Timeout=30;

You would retrieve the values from environment variables or application settings in your code.

Example Connection String (Node.js/Express)

const connectionString = `postgres://${process.env.POSTGRESQL_USERNAME}:${process.env.POSTGRESQL_PASSWORD}@${process.env.POSTGRESQL_SERVER_NAME}/${process.env.POSTGRESQL_DATABASE_NAME}`;

Step 3: Implement Database Connection in Your Application

Modify your application code to read the connection details from the environment variables (which are populated by your App Service application settings) and establish a connection to PostgreSQL.

Important: Always use a robust PostgreSQL client library for your chosen language and handle connection pooling and error management effectively.

Step 4: Secure Your Connection

SSL/TLS Encryption

Ensure your PostgreSQL server is configured to enforce SSL/TLS connections. For Azure Database for PostgreSQL, this is enabled by default. When constructing your connection string, include parameters to enforce SSL.

Example for Npgsql: Add SSL Mode=Require;Trust Server Certificate=false; to your connection string.

Firewall Rules

If you haven't enabled "Allow access to Azure services", you may need to configure firewall rules on your PostgreSQL server to allow traffic from your App Service's IP addresses. For production, consider using VNet integration and private endpoints for enhanced security.

Best Practices and Considerations

• Connection Pooling: Always use connection pooling to improve performance by reusing database connections.
• Environment Variables: Never hardcode credentials. Use App Service application settings/environment variables.
• Secrets Management: For more advanced scenarios, consider Azure Key Vault to store and manage your database credentials securely.
• Read Replicas: For read-heavy workloads, consider setting up read replicas for PostgreSQL to distribute load.
• Monitoring: Utilize Azure Monitor and PostgreSQL's built-in tools to track performance and identify potential issues.
• Automated Backups: Azure Database for PostgreSQL provides automated backups, but understand your RPO (Recovery Point Objective) and retention policies.