Connect/Deploy to Azure Database for PostgreSQL Flexible Server with Service Connection

I am trying to connect and update databases in an Azure Database for PostgreSQL Flexible Server during my release pipeline in Azure DevOps. However due to the security restrictions within my organization, I am not allowed to use a username + password combination. I have been advised to use a Service connection.

However when I work through the Microsoft documentation, I only see them using an admin username and password. Is what I am trying to achieve possible? If so, can anyone provide a code sample for me to look at please. Are there perhaps better alternatives that I am not aware of?

To run the Azure CLI commands "az postgres flexible-server connect", "az postgres flexible-server execute" or other commands "az postgres flexible-server xxxx", you must provide login username of the server administrator.

If you have an existing App registration (ServicePrincipal), you can set this App registration as the server administrator, and then use it to run the commands.

---

Add the App registration as a Microsoft Entra Admins on the Azure Database for PostgreSQL Flexible Server:

Go to the Azure Database for PostgreSQL Flexible Server, ensure the Authentication method of the server is "Microsoft Entra authentication only" or "PostgreSQL and Microsoft Entra authentication". Add the App registration as a Microsoft Entra Admins.

Remember the Name of this App registration, it will be used as the value of option "--admin-user" when calling the commands "az postgres flexible-server xxxx".

---

Use the existing App registration to set up an Azure Resource Manager service connection on Azure DevOps:

1. In the project where your pipeline is in, go to "Project Settings" > "Service connections" to create an ARM connection (Azure Resource Manager service connection) using the existing App registration. Since you have an existing service principal, you can select "Workload Identity federation (manual)".

   

2. Give a customized name to the new ARM connection to complete Step 1. Click "Next" to start Step 2.

   

3. Open the existing service principal on Azure Portal. Go to "Certificates & secrets" > "Federated credentials" tab to add a new credential. Fill in the required information on the new credential.

   • Federated credential scenario: Other issuer
   • Issuer: Copy from Step 2 on the new ARM connection window.
   • Subject identifier: Copy from Step 2 on the new ARM connection window.
   • Name: A customized name of the new credential.

   

4. Back to the new ARM connection window to finish Step 2.

   • Scope Level: Select Subscription if you want this ARM connection can only access resources under the specified Azure Subscription.
   • Subscription Id: The ID of the Azure Subscription.
   • Subscription Name: The name of the Azure Subscription.
   • Service Principal Id: The Application (client) ID of the App registration.
   • Tenant ID: The Tenant ID of your Microsoft Entra ID tenant.

   

---

Set up the pipeline (YAML) to run the Azure CLI commands.

The pipeline main YAML (azure-pipelines.yml).

variables:
  ArmConnection: 'MyArmConnection'
  ResourceGroup: 'xxxx'
  ServerName: 'brightran-postgresql'
  FirewallRuleName: 'AllowAgent'
  AdminUserName: 'xxxx'  # The name of App registration.
  dbName: 'myDB'

steps:
- checkout: none

- task: AzureCLI@2
  displayName: 'Add firewall-rule'
  inputs:
    azureSubscription: '$(ArmConnection)'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      ip=$(curl http://ipinfo.io/json | jq -r '.ip')
      echo "Adding a firewall-rule for PostgreSQL Server to allow the IP address of current agent ($ip)..."
      az postgres flexible-server firewall-rule create \
        --resource-group '$(ResourceGroup)' \
        --name '$(ServerName)' \
        --rule-name '$(FirewallRuleName)' \
        --start-ip-address $ip \
        --end-ip-address $ip

- task: AzureCLI@2
  displayName: 'Connect PostgreSQL Server'
  inputs:
    azureSubscription: '$(ArmConnection)'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      echo "Generate an access token to access PostgreSQL Server."
      accessToken=$(az account get-access-token \
        --resource https://ossrdbms-aad.database.windows.net \
        --query "accessToken" \
        -o tsv)

      echo "Connecting to PostgreSQL Server..."
      az postgres flexible-server connect \
        --name '$(ServerName)' \
        --admin-user '$(AdminUserName)' \
        --admin-password $accessToken \
        --database-name '$(dbName)'

- task: AzureCLI@2
  displayName: 'Delete firewall-rule'
  inputs:
    azureSubscription: '$(ArmConnection)'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      echo "Delete the firewall-rule for PostgreSQL Server."
      az postgres flexible-server firewall-rule delete -y \
        --resource-group '$(ResourceGroup)' \
        --name '$(ServerName)' \
        --rule-name '$(FirewallRuleName)'

In this example, since I use Microsoft-hosted agents to run the pipeline, each time to run the pipeline, it will gets a fresh VM for each job.

So, the IP address of agent might be different each time. It needs to get the IP address of current agent each time, and then use the Azure CLI command "az postgres flexible-server firewall-rule create" to create a firewall-rule for Azure Database for PostgreSQL Flexible Server to allow the IP address.

If you are using self-hosted agents that are hosted on the machines owned by yourself, and if the IP addresses of the agent machines are fixed and not changed often, you can manually create the firewall-rule on the web UI of your Azure Database for PostgreSQL Flexible Server to allow the IP addresses.

---