ConnectDeploy to Azure Database for PostgreSQL Flexible Server with Service Connection - Stack Overflow

admin管理员组

文章数量:1122832

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?

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?

• postgresql
• azure-devops
• azure-pipelines
• azure-database-postgresql

Share Improve this question Follow asked Nov 21, 2024 at 12:43 Code VaderCode Vader 75533 gold badges1111 silver badges2828 bronze badges 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? – Bright Ran-MSFT 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. – Bright Ran-MSFT 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? – Code Vader Commented Nov 22, 2024 at 7:21
• I have shared the detailed steps with an example in my answer below. Try with it. @CodeVader – Bright Ran-MSFT Commented Nov 22, 2024 at 8:55

Add a comment  | 

1 Answer 1

Sorted by: 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".

---

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.

---

本文标签： ConnectDeploy to Azure Database for PostgreSQL Flexible Server with Service ConnectionStack Overflow