0

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?

Improve this question
4
  • You mentioned "due to the security restrictions within my organization, I am not allowed to use a username + password combination." Do you mean that when your organization created the Azure Database for PostgreSQL flexible server, it set the "Authentication method" as "Microsoft Entra authentication only"? If so, which identity is set as the Microsoft Entra admin? Commented Nov 22, 2024 at 3:04
  • If the Microsoft Entra admin is an App registration or Managed Identity, you can use this App registration or Managed Identity to set up the Azure Resource Manager service connection. Commented Nov 22, 2024 at 3:06
  • @BrightRan-MSFT yeah my apologies man, I am struggling to wrap my head around this myself. So my question is probably not phrased very well. From the code samples in the Msft documentation link in the question, it seems they always authenticate using an admin username and password, which I am not allowed to use in my pipelines. However I can use an App Reg (as Msft Entra Admin) to set up the Azure Resource Manager connection. But I am still unclear how the code in the pipeline should look. Can you provide a sample? Commented Nov 22, 2024 at 7:21
  • I have shared the detailed steps with an example in my answer below. Try with it. @CodeVader Commented Nov 22, 2024 at 8:55

1 Answer 1

Reset to default
1

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".

enter image description here

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)".

    enter image description here

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

    enter image description here

  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.

    enter image description here

  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.

    enter image description here

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)'

enter image description here

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Have you tried with the steps shared in this answer? Does it work for you? @CodeVader
thank you for your detailed answer, it seems perfect. I am waiting for one of the administrators in my organization to approve my use of the App Reg for the Service Connection before I can test it. I will remember to come back and mark your answer as correct, but not before I have tested and verified.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.