Securely Update a PostgreSQL Database on Azure Using Azure DevOps Pipelines

In modern cloud environments, ensuring the security of your database credentials while maintaining automated CI/CD workflows is crucial. In this guide, we'll walk you through the process of using Azure DevOps pipelines to securely update and modify a PostgreSQL database hosted on Azure. We'll leverage Azure Key Vault to securely store and retrieve your database credentials during the pipeline execution.

Prerequisites

Before we start, ensure you have the following:

1. An Azure DevOps account and project.
2. An Azure subscription with a PostgreSQL database set up.
3. An Azure Key Vault to store your PostgreSQL credentials.
4. SQL scripts ready for modifying your PostgreSQL database.

Step 1: Store Secrets in Azure Key Vault

First, securely store your PostgreSQL username and password in Azure Key Vault.

1. Navigate to your Azure Key Vault in the Azure portal.
2. Go to the Secrets section and add two secrets:
   • PGUSER: Your PostgreSQL username.
   • PGPASSWORD: Your PostgreSQL password.

Step 2: Set Up Azure Service Connection in Azure DevOps

Next, set up a service connection in Azure DevOps to allow access to your Azure resources.

1. Go to your Azure DevOps project.
2. Navigate to Project Settings > Service connections.
3. Create a new service connection for Azure Resource Manager.
4. Select the appropriate subscription and resource group that contains your Key Vault.
5. Grant the service connection access to the Key Vault.

Step 3: Configure Key Vault Access Policy

Ensure Azure DevOps has permission to read the secrets from your Key Vault.

1. Navigate to your Azure Key Vault in the Azure portal.
2. Under Access policies, add a new access policy.
3. Select the Get permission for secrets.
4. Choose the service principal associated with your Azure DevOps service connection.

Step 4: Define the Pipeline in YAML

Now, define your pipeline in YAML to automate the process of updating your PostgreSQL database.

Here's an example of the YAML pipeline definition:

trigger:
- main

pool:
  vmImage: 'ubuntu-latest'

variables:
  PGHOST: 'your-postgresql-server.postgres.database.azure.com'
  PGDATABASE: 'your-database-name'

steps:
- task: UsePythonVersion@0
  inputs:
    versionSpec: '3.x'
    addToPath: true

- task: AzureKeyVault@1
  inputs:
    azureSubscription: '<your-service-connection-name>'
    KeyVaultName: '<your-key-vault-name>'
    SecretsFilter: 'PGUSER,PGPASSWORD'

- script: |
    sudo apt-get update
    sudo apt-get install -y postgresql-client
  displayName: 'Install PostgreSQL Client'

- script: |
    psql "sslmode=require host=$PGHOST dbname=$PGDATABASE user=$(PGUSER) password=$(PGPASSWORD)" -f path/to/your/script.sql
  displayName: 'Run SQL Script'
  env:
    PGPASSWORD: $(PGPASSWORD)

Explanation of YAML Pipeline

1. Trigger:

   • The pipeline triggers on changes to the main branch.

2. Pool:

   • Specifies the VM image to use for the pipeline.

3. Variables:

   • Defines the PostgreSQL host and database name.

4. UsePythonVersion Task:

   • Ensures Python is available in the pipeline (optional step depending on further needs).

5. AzureKeyVault Task:

   • Fetches the PGUSER and PGPASSWORD secrets from the specified Key Vault using the defined service connection.

6. Install PostgreSQL Client:

   • Installs the PostgreSQL client on the build agent.

7. Run SQL Script:

   • Uses the psql command to execute the SQL script, passing the retrieved PostgreSQL user and password as environment variables.

Step 5: Securely Reference Secrets

By using the AzureKeyVault task, the secrets PGUSER and PGPASSWORD are securely retrieved and can be used in subsequent steps within the pipeline. Ensure the names used in SecretsFilter match the secret names in Azure Key Vault.

Step 6: Commit and Run the Pipeline

1. Commit your changes to the repository.
2. Go to Pipelines and select your pipeline.
3. Run the pipeline.

Conclusion

By following these steps, you can securely automate the process of updating and modifying your PostgreSQL database hosted on Azure using Azure DevOps pipelines. Leveraging Azure Key Vault ensures that your database credentials are securely managed, enhancing the security of your CI/CD process. With this setup, you can maintain a seamless and secure workflow, ensuring that your database modifications are consistently applied without exposing sensitive information.