Azure Database for PostgreSQL - Commands & Configuration

Introduction

Azure Database for PostgreSQL is a managed service that lets you run PostgreSQL databases in the cloud without worrying about hardware or maintenance. It solves the problem of setting up and managing a reliable database server by handling backups, updates, and scaling automatically.

When you want to host a PostgreSQL database without managing the underlying server.

When you need automatic backups and easy recovery for your database.

When your app requires scaling the database resources up or down based on demand.

When you want built-in security features like encryption and firewall rules.

When you want to connect your cloud app to a reliable, managed PostgreSQL database.

Config File - azure-postgresql-deployment.bicep

azure-postgresql-deployment.bicep

param serverName string = 'mypgserver123'
param adminUser string = 'pgadmin'
param adminPassword string
param location string = resourceGroup().location

resource postgresqlServer 'Microsoft.DBforPostgreSQL/servers@2022-12-01' = {
  name: serverName
  location: location
  sku: {
    name: 'B_Gen5_1'
    tier: 'Basic'
    capacity: 1
    family: 'Gen5'
  }
  properties: {
    administratorLogin: adminUser
    administratorLoginPassword: adminPassword
    version: '13'
    sslEnforcement: 'Enabled'
    storageProfile: {
      storageMB: 5120
      backupRetentionDays: 7
      geoRedundantBackup: 'Disabled'
    }
  }
}

resource firewallRule 'Microsoft.DBforPostgreSQL/servers/firewallRules@2022-12-01' = {
  name: '${serverName}/AllowMyIP'
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '0.0.0.0'
  }
  dependsOn: [postgresqlServer]
}

This Bicep file creates an Azure Database for PostgreSQL server with basic SKU and version 13.

The administratorLogin and administratorLoginPassword set the admin user credentials.

The sslEnforcement is enabled for secure connections.

The firewallRule allows connections from all IPs (0.0.0.0) for simplicity; in real use, restrict this to your IP range.

Commands

This command deploys the PostgreSQL server and firewall rule to the specified Azure resource group using the Bicep template. It sets the admin password securely.

Terminal

az deployment group create --resource-group myResourceGroup --template-file azure-postgresql-deployment.bicep --parameters adminPassword=MyStrongP@ssw0rd

Expected Output

{

  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/myResourceGroup/providers/Microsoft.Resources/deployments/azure-postgresql-deployment",

  "name": "azure-postgresql-deployment",

  "properties": {

    "provisioningState": "Succeeded",

    "outputs": {}

  },

  "type": "Microsoft.Resources/deployments"

}

→

--resource-group - Specifies the Azure resource group to deploy into

→

--template-file - Points to the Bicep template file to use for deployment

→

--parameters - Passes parameters like admin password securely

This command retrieves details about the deployed PostgreSQL server to verify it was created successfully.

Terminal

az postgres server show --resource-group myResourceGroup --name mypgserver123

Expected Output

{

  "administratorLogin": "pgadmin",

  "fullyQualifiedDomainName": "mypgserver123.postgres.database.azure.com",

  "location": "eastus",

  "name": "mypgserver123",

  "sslEnforcement": "Enabled",

  "version": "13",

  "userVisibleState": "Ready"

}

→

--resource-group - Specifies the resource group where the server exists

→

--name - Specifies the name of the PostgreSQL server

This command connects to the PostgreSQL server using the psql client and lists all databases to confirm connectivity.

Terminal

psql "host=mypgserver123.postgres.database.azure.com port=5432 dbname=postgres user=pgadmin@mypgserver123 password=MyStrongP@ssw0rd sslmode=require" -c "\l"

Expected Output

                                   List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+-----------------------

 postgres  | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgadmin           +

           |          |          |             |             | pgadmin=CTc/pgadmin

 template1 | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgadmin           +

           |          |          |             |             | pgadmin=CTc/pgadmin

(3 rows)

Key Concept

If you remember nothing else from this pattern, remember: Azure Database for PostgreSQL lets you run a managed, secure PostgreSQL server in the cloud without handling hardware or maintenance.

Common Mistakes

Using weak or simple passwords for the admin user

Weak passwords can lead to unauthorized access and data breaches.

Always use strong, complex passwords with letters, numbers, and symbols.

Setting firewall rules to allow all IP addresses in production

This exposes the database to the internet and increases security risks.

Restrict firewall rules to only trusted IP addresses or ranges.

Not enabling SSL enforcement for connections

Without SSL, data between client and server can be intercepted.

Enable SSL enforcement to secure data in transit.

Summary

Use a Bicep template to define and deploy an Azure Database for PostgreSQL server with admin credentials and firewall rules.

Deploy the template using Azure CLI and verify the server creation with 'az postgres server show'.

Connect securely to the PostgreSQL server using psql client with SSL enabled to list databases.