Monday, May 8, 2023

Deploying PostgreSQL servers with Bicep

I gave a talk at the recent CitusCon about deploying PostgreSQL servers to Azure using Bicep, an infrastructure-as-code language. You can watch the talk on YouTube or go through the online slides.

For those of you that prefer reading, this blog post is a "blog-post-ification" of my talk. Hope it helps you with more repeatable PostgreSQL deploys!

Managed services for PostgreSQL on Azure

Azure has multiple offerings for PostgreSQL, and it can be confusing to know which one to use. Here's a quick overview:

Option Description
Azure Database for PostgreSQL – Single Server Microsoft's original offering. No longer recommended for new apps.
Azure Database for PostgreSQL – Flexible Server Microsoft's most recent PostgreSQL offering. Fully managed service with vertical scaling.
Azure Cosmos DB for PostgreSQL Distributed database using PostgreSQL and the Citus extension. Can scale horizontally.

For more details on the differences, check out these blog posts from the Azure team:

In this blog post, I am focusing on the PostgreSQL Flexible server offering, since that's a great fit for Python web apps and it's what I have the most experience deploying. The concepts covered here should be helpful for deploying other Azure database servers as well, however.

Ways to deploy
a PostgreSQL Flexible Server

There are quite a few ways to deploy a PostgreSQL Flexible Server to Azure: Azure portal, Azure CLI, Azure PowerShell, Azure SDKs, ARM templates, Terraform, and Azure Bicep. First, let me point out why I dislike many of those options.

Using the Azure Portal

Azure portal screenshot

Creating new resources using the Azure portal is a great way to get started with Azure, but it's not good for replicable deploys of similar resources. I do recommend going through the Portal creation process once (for a throw-away server) just to get a feel for the options, but then immediately moving on to other approaches.

Using CLI commands

An improvement over the Portal is the Azure CLI or Powershell modules, which allow you to specify all the parameters in a single command.

Azure CLI example:

az postgres flexible-server create --resource-group pg-grp \
    --name pg-srv --location westus \
    --sku-name GP_Gen5_2 --version 14 \
    --admin-user myadmin --admin-password NeverShowThis

Azure PowerShell example:

New-AzPostgreSqlFlexibleServer -ResourceGroupName pg-grp \
    -ServerName pg-srv -Location westus \
    -SkuName GP_Gen5_2 -Version 14 \
    -AdministratorLogin myadmin -AdministratorLoginPassword NeverShowThis

It's easier to replicate deploys now, since you can just copy the command. However, if you realize you need to update the server configuration afterwards, you have to run a different command (update), so replication would then require multiple commands or a merging of the create and update commands. 😩

Using ARM templates

ARM (Azure Resource Manager) templates are JSON files that declaratively describe the resources you want to create. 

{
    "type": "Microsoft.DBforPostgreSQL/flexibleServers",
    "apiVersion": "2021-06-01",
    "name": "pg-srv",
    "location": "westus",
    "sku": {
        "name": "Standard_B1ms",
        "tier": "Burstable"
    },
    "properties": {
        "administratorLogin": "admin-user",
        "administratorLoginPassword": "NeverShowThis",
        "version": "14"
     }
    ...

👁️ See full example in postgres_example.json

ARM templates are a great way to replicate deploys, but using JSON as a file format leads to fairly unwieldy files that are difficult to parameterize. That's why Azure invented...

Bicep

Bicep is a DSL (domain-specific language) that compiles down to ARM templates. The example below shows a minimal configuration for a PostgreSQL Flexible server: 

resource server 'Microsoft.DBforPostgreSQL/flexibleServers@2021-06-01' = {
    name: 'pg-srv'
    location: 'eastus'
    sku: {
        name: 'Standard_B1ms'
        tier: 'Burstable'
    }
    properties: {
        administratorLogin: 'myadmin'
        administratorLoginPassword: 'NeverShowThis'
        version: '14'
        storage: {
        storageSizeGB: 128
        }
    }
}

The Bicep language

Bicep offers quite a few language features that make it easier to write and maintain, and we'll see examples of each of these in the rest of this post.

Feature Example
Parameters param location string = 'eastus'
Types param storageSizeGB int = 128
Logic skuTier == 'Burstable' ? 32 : 128
Loops for i in range(0, serverCount):
Functions param adminUsername string = newGuid()
Modules module myServer 'flexserver.bicep' { }

For a detailed introduction to the entire Bicep language, consult the Bicep language documentation .

A parameterized Bicep file

We can parameterize the Bicep file to make it easier to reuse, using parameters for values that vary across deployments. The example below parameterizes the server name, location, and admin password: 

param serverName string = 'pg-srv'
param location string = 'eastus'
@secure()
param adminPassword string

resource srv 'Microsoft.DBforPostgreSQL/flexibleServers@2021-06-01' = {
    name: serverName
    location: location
    sku: {
        name: 'Standard_B1ms'
        tier: 'Burstable'
    }
    properties: {
        administratorLogin: 'myadmin'
        administratorLoginPassword: adminPassword
        version: '14'
        storage: { storageSizeGB: 128 }
    }  
}

Deploying from Bicep

Once we have a Bicep file, we can deploy it using Azure CLI commands.

The az deployment group create command will either create or update resources in an existing resource group. If the described resources already exist, it will just update their configuration (if any change detected). If they don't exist, it will create the resources from scratch. The example below shows what it's like to deploy the file above: 

$ az deployment group create --resource-group pg-grp --template-file pg.bicep
Please provide securestring value for 'adminPassword' (? for help):
{   "name": "postgres_example1",
    "properties": {
    "outputResources": [{
        "id": "/subscriptions/32ea8a26-5b40-4838-b6cb-be5c89a57c16/resourceGroups/cituscon-examples-eastus/providers/Microsoft.DBforPostgreSQL/flexibleServers/pg-srv",
        "resourceGroup": "pg-grp"
        }], ...

The output contains a long JSON description of all the created resources (truncated here).

If needed, it's possible to specify parameters on the command line:

$ az deployment group create --resource-group pg-grp --template-file pg.bicep \
    --parameters adminPassword=ADMIN_PASSWORD

Once the command finishes, logging into the Portal shows the newly created server:

Screenshot of Portal overview page for server

Child resources

A child resource exists solely within the scope of its parent resource.

These are the child resources that can be created for PostgreSQL Flexible servers:

  • administrators
  • configurations
  • databases
  • firewallRules
  • migrations

There are also a few read-only child resources that can be referenced in Bicep (but not created through Bicep): advisors, backups, queryTexts.

Child resources: database

Each PostgreSQL Flexible server always includes a database called postgres plus system databases azure_maintenance and azure_sys.

Additional databases can be created as child resources of the server, as shown in the example below:

resource postgresServer 'Microsoft.DBforPostgreSQL/flexibleServers@2022-12-01' = {
    name: serverName
    location: location
    ...

    resource database 'databases' = {
        name: 'webapp'
    }
}

👁️ See full example in postgres_database.bicep

Child resources: databases

You may also want to create multiple databases at once. That's a great time to use an array parameter type with a for loop: 

param databaseNames array = ['webapp', 'analytics']

resource postgresServer 'Microsoft.DBforPostgreSQL/flexibleServers@2022-12-01' = {
    name: serverName
    location: location
    ...

    resource database 'databases' = [for name in databaseNames: {
        name: name
    }]
}

👁️ See full example in postgres_databases.bicep

Once deployed, the databases will show up in the Portal, along with an option to connect to them with a shell:

Screenshot of Databases page of PostgreSQL server

Child resources: firewall rule

By default, a PostgreSQL Flexible server is configured for "Public access" but it's not yet accessible from any IP ranges. You can use firewallRules to allow access from particular IPs.

The example below allows access from any Azure service within Azure:

resource firewallAzure 'firewallRules' = {
    name: 'allow-all-azure-internal-IPs'
    properties: {
        startIpAddress: '0.0.0.0'
        endIpAddress: '0.0.0.0'
    }
}

👁️ See full example in postgres_azurefirewall.bicep

⚠️ This configuration means that any Azure developer can now access the server, if they somehow know the username and password. Stay tuned to see how to deploy PostgreSQL within a Virtual network, the preferred approach.

You may want to allow access for just your work machine(s). The example below creates rules for each IP in list using array with for: 

param allowedSingleIPs array = ['103.64.88.254', '44.143.22.28']

resource postgresServer 'Microsoft.DBforPostgreSQL/flexibleServers@2022-12-01' = {
    ...

    resource firewallSingle 'firewallRules' = [for ip in allowedSingleIPs: {
        name: 'allow-single-${replace(ip, '.', '')}'
        properties: {
            startIpAddress: ip
            endIpAddress: ip
        }
    }]
}

👁️ See full example in postgres_loopfirewall.bicep

Once deployed, the firewall rules will show up in the Portal in the Networking section:

Screenshot of Networking page of PostgreSQL server

Adding a Virtual Network

As a security best practice, Azure recommends that you deploy PostgreSQL servers in a Virtual Network (VNet), along with other Azure resources that need access to it.

I'll step through the Bicep needed to deploy both a PostgreSQL server and an App Service App inside a VNet (each delegated to their own subnet), and use a Private DNS Zone for name resolution between the App and PG server.

Virtual Network

The resource definition below creates a VNet with a private address space of 10.0.0.0 - 10.0.255.255:

resource virtualNetwork 'Microsoft.Network/virtualNetworks@2019-11-01' = {
    name: '${name}-vnet'
    location: location
    properties: {
        addressSpace: {
        addressPrefixes: [
            '10.0.0.0/16'
        ]
        }
    }
}

Virtual Network: subnets

The next step is to define subnets in the VNet, by adding child resources to that VNet resource. The child resource below creates a subnet with address space of 10.0.0.1 - 10.0.0.255 and delegates it to PostgreSQL servers: 

resource databaseSubnet 'subnets' = {
    name: 'database-subnet'
    properties: {
        addressPrefix: '10.0.0.0/24'
        delegations: [
        {
            name: '${name}-subnet-delegation'
            properties: {
            serviceName: 'Microsoft.DBforPostgreSQL/flexibleServers'
            }
        }]
    }
}

If you're new to '10.0.0.0/24' notation, see this post on Configuring Azure VNet subnets with CIDR notation

Similarly, the child resource below creates a subnet with address space of 10.0.1.1 - 10.0.1.255 and delegates it to the other resource type (App Service):

resource webappSubnet 'subnets' = {
    name: 'webapp-subnet'
    properties: {
        addressPrefix: '10.0.1.0/24'
        delegations: [
        {
            name: '${name}-subnet-delegation-web'
            properties: {
            serviceName: 'Microsoft.Web/serverFarms'
            }
        }]
    }
}

Private DNS Zone

To make it easy for the App Service to connect to the PostgreSQL server, create a private DNS Zone resource: 

resource privateDnsZone 'Microsoft.Network/privateDnsZones@2020-06-01' = {
    name: '${pgServerPrefix}.private.postgres.database.azure.com'
    location: 'global'
    resource vNetLink 'virtualNetworkLinks' = {
        name: '${pgServerPrefix}-link'
        location: 'global'
        properties: {
            registrationEnabled: false
            virtualNetwork: { id: virtualNetwork.id }
        }
    }
}

The name of the resource must end in .private.postgres.database.azure.com. For more details, read these reference materials:

Configure the PostgreSQL server

With the VNet and DNS zone created, the next step is to modify the configuration of the PostgreSQL server to connect to both of them.

The Bicep below adds the network property of the server to inject it into the VNet and connect it to the DNS Zone:

resource postgresServer 'Microsoft.DBforPostgreSQL/flexibleServers@2022-01-20-preview' = {
    name: pgServerPrefix
    ...

    properties: {
        ...
        network: {
            delegatedSubnetResourceId: virtualNetwork::databaseSubnet.id
            privateDnsZoneArmResourceId: privateDnsZone.id
        }
    }
}

Configure the Web App

The App Service also needs to be connected to the VNet (though it doesn't need to be connected to the DNS Zone).

This Bicep adds a networkConfig child resource on the Web App Service to inject it into the VNet:

resource web 'Microsoft.Web/sites@2022-03-01' = {
    name: '${name}-app-service'
    ...

    resource webappVnetConfig 'networkConfig' = {
        name: 'virtualNetwork'
        properties: {
            subnetResourceId: virtualNetwork::webappSubnet.id
        }
    }
}

All together: PostgreSQL in VNet

That's everything needed!
👁️ See full example in postgres_vnet.bicep

After deploying the Bicep, the Azure Portal shows all four resources:

Tips & Tricks

Here are a few things I've learnt while deploying PostgreSQL with Bicep dozens of times over the last year.

PostgreSQL tips

  • Managed identity is a more secure approach than username/password. See sample at: github.com/Azure-Samples/flask-postgresql-managed-identity . That example uses the service connector command, as it doesn't seem possible yet to create a managed identity for PostgreSQL servers entirely with Bicep. We are hoping that it becomes easier in the future.
  • Location, location, location:
    There are some location constraints for PostgreSQL servers, especially for Microsoft employees. If you get a funky error, try again with a new location (like "centralus").
  • Many things can't be changed after creation: admin username, PostgreSQL version, location, networking option, etc. Give everything a good look before you start adding production data.

Bicep linting

I love linting all my code, and Bicep is no exception.

You can use az bicep build command to check file for errors:

$ az bicep build -f pg.bicep

You could use that in a pre-commit hook and/or setup a CI/CD workflow to always check for errors:

steps:
    - name: Checkout
    uses: actions/checkout@v2

    - name: Azure CLI script
    uses: azure/CLI@v1
    with:
        inlineScript: az bicep build -f infra/main.bicep

👁️ See whole file: azure-bicep.yaml

Bicep security validation

It's also possible to run automated checks for security issues and best practices based solely on the Bicep files.

In CI/CD, you can use Microsoft Security DevOps action to find security issues in Bicep files:

- name: Run Microsoft Security DevOps Analysis
    uses: microsoft/security-devops-action@preview
    id: msdo
    with:
    tools: templateanalyzer

Here's an example of the kind of output it produces:


Error:      1. TemplateAnalyzer Error AZR-000284 - File: infra/main.bicep. Line: 54. Column 0. 
Tool: TemplateAnalyzer: Rule: AZR-000284 (Azure.Deployment.AdminUsername).
https://azure.github.io/PSRule.Rules.Azure/en/rules/Azure.Deployment.AdminUsername/
Resource properties can be configured using a hardcoded value or Azure Bicep/ template
expressions. When specifing sensitive values, use secure parameters such as secureString
or secureObject.

👁️ See whole file: azure-dev-validate.yaml

AZD templates

You can use Bicep to create all of your Azure resources, and use the new Azure Developer CLI to take care of the whole deployment workflow. It is my favorite way to deploy web apps on Azure these days.

To get started with the Azure Developer CLI, check out the many aka.ms/azd-pg templates that use PostgreSQL in the AZD templates gallery.

For more Bicep tips, check out my previous blog post: 🔗 aka.ms/bicep-tips Tips for writing Bicep files

Let me know if you have any questions or suggestions for the Azure team!

Posted by
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)