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:
- An Azure DevOps account and project.
- An Azure subscription with a PostgreSQL database set up.
- An Azure Key Vault to store your PostgreSQL credentials.
- 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.
- Navigate to your Azure Key Vault in the Azure portal.
- 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.
- Go to your Azure DevOps project.
- Navigate to Project Settings > Service connections.
- Create a new service connection for Azure Resource Manager.
- Select the appropriate subscription and resource group that contains your Key Vault.
- 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.
- Navigate to your Azure Key Vault in the Azure portal.
- Under Access policies, add a new access policy.
- Select the Get permission for secrets.
- 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
-
Trigger:
- The pipeline triggers on changes to the
main
branch.
- The pipeline triggers on changes to the
-
Pool:
- Specifies the VM image to use for the pipeline.
-
Variables:
- Defines the PostgreSQL host and database name.
-
UsePythonVersion Task:
- Ensures Python is available in the pipeline (optional step depending on further needs).
-
AzureKeyVault Task:
- Fetches the
PGUSER
andPGPASSWORD
secrets from the specified Key Vault using the defined service connection.
- Fetches the
-
Install PostgreSQL Client:
- Installs the PostgreSQL client on the build agent.
-
Run SQL Script:
- Uses the
psql
command to execute the SQL script, passing the retrieved PostgreSQL user and password as environment variables.
- Uses the
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
- Commit your changes to the repository.
- Go to Pipelines and select your pipeline.
- 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.
Top comments (0)