Documentation

adrs/044-postgresql-public-access.md

ADR-044: PostgreSQL Public Access (Non-VNet Integration)

Status

Accepted

Context

When deploying Azure PostgreSQL Flexible Server to Azure, there are two networking options:

  1. Public Access - Server has a public endpoint, access controlled via firewall rules
  2. Private Access (VNet Integration) - Server is deployed into a VNet subnet with no public endpoint

During initial Azure deployment attempts, we encountered persistent failures when deploying PostgreSQL Flexible Server with VNet integration. Investigation revealed the root cause:

VNet Integration Requirements

PostgreSQL Flexible Server with VNet integration requires:

  1. Dedicated Subnet: A subnet delegated exclusively to Microsoft.DBforPostgreSQL/flexibleServers
  2. NSG Rules: Network Security Group rules allowing outbound traffic to:
    • AzureActiveDirectory service tag (for Entra ID authentication)
    • Storage service tag (for WAL file archival)
  3. Route Table Configuration: If using custom routes, rules for AzureActiveDirectory with Internet next hop

The Problem

Our deployment was failing because:

  1. PostgreSQL 18 Preview: We use PG 18 for UUID v7 native support, but PG 18 Preview does not support Entra ID authentication
  2. Password Authentication: We're using password auth, which doesn't benefit from VNet's Entra ID integration
  3. NSG Complexity: The Container Apps subnet didn't have the required outbound rules for AzureActiveDirectory
  4. Subnet Delegation Conflict: PostgreSQL requires its own dedicated subnet (can't share with Container Apps)

The error manifested as the PostgreSQL server getting stuck in a failed provisioning state, unable to complete deployment due to blocked outbound connectivity to Azure Active Directory services.

Decision

Deploy PostgreSQL Flexible Server with public access instead of VNet integration, using firewall rules to restrict access to Azure services only.

Implementation

// In DynaplexInfrastructureExtensions.cs
var allowAzureServices = new PostgreSqlFlexibleServerFirewallRule("allowAzureServices") {
    Parent = server,
    Name = "AllowAllAzureServices",
    StartIPAddress = System.Net.IPAddress.Parse("0.0.0.0"),
    EndIPAddress = System.Net.IPAddress.Parse("0.0.0.0")
};
infra.Add(allowAzureServices);

The 0.0.0.0 to 0.0.0.0 range is Azure's special notation for "allow all Azure services" - it permits connections from any IP address allocated to Azure services, including our Container Apps.

Consequences

Positive

  1. Simplified Deployment: No need to manage dedicated PostgreSQL subnet, NSG rules, or route tables
  2. Faster Iteration: Removes a complex failure point during infrastructure provisioning
  3. Container Apps Connectivity: Container Apps connect via public endpoint without VNet peering
  4. Consistent with Auth Model: Password authentication doesn't benefit from VNet's Entra ID features anyway

Negative

  1. Public Endpoint: Server has a public DNS name (though firewalled to Azure services only)
  2. Broader Access: The AllowAllAzureServices rule permits any Azure service (including other customers' services) - mitigated by password authentication requirement
  3. Not Zero-Trust: Traffic traverses public internet rather than private network backbone

Security Mitigations

  1. Password Authentication: Connections still require valid PostgreSQL credentials
  2. TLS Required: All connections use TLS encryption
  3. Azure-Only Access: Firewall blocks all non-Azure IP addresses
  4. Connection String Secrets: Credentials managed via Aspire secrets, not exposed in configuration

Future Migration Path

When PostgreSQL 18 reaches GA with Entra ID support:

  1. Create dedicated PostgreSQL subnet with proper delegation
  2. Configure NSG with outbound rules for AzureActiveDirectory and Storage service tags
  3. Enable Entra ID authentication on the server
  4. Update Container Apps to use managed identity for database access
  5. Switch server to private access mode
  6. Remove public endpoint and firewall rules

This migration can be done with minimal downtime using Azure's networking change capabilities.

References