db_rollback.yml

name: Rollback DB

on:
  workflow_dispatch:
    inputs:
      rollbackCount:
        description: 'Number of migrations to rollback'
        required: true
        default: # of rollback you require in the file input
      rollbackFile:
        description: "File with migrations you want to rollback"
        required: true
        default: Filepath to yml
      environment:
        type: choice
        description: "Azure environment to rollback on"
        options:
          - dev
          - internal
          - stg
          - prd
        required: true


jobs:
  database-rollback:
    name: Database Rollback
    environment:
      name: ${{ github.event.inputs.environment }}
    runs-on: ubuntu-latest
    env:
      ARM_CLIENT_ID: ${{ github.event.inputs.environment == 'internal' && secrets.AZURE_CLIENT_ID || secrets.AZURE_CDC_CLIENT_ID }}
      ARM_TENANT_ID: ${{ github.event.inputs.environment == 'internal' && secrets.AZURE_TENANT_ID || secrets.AZURE_CDC_TENANT_ID}}
      ARM_SUBSCRIPTION_ID: ${{ github.event.inputs.environment == 'internal' && secrets.AZURE_SUBSCRIPTION_ID || secrets.AZURE_CDC_DMZ_C1_SUBSCRIPTION_ID}}
      ARM_USE_OIDC: true
    permissions:
      id-token: write
      contents: read

    steps:

      - uses: actions/checkout@v4

      - name: Terraform Init
        id: init
        run: |
          cd ./operations/environments/${{ github.event.inputs.environment }}
          terraform init

      - name: Login via Azure CLI
        uses: azure/login@v2
        with:
          client-id: ${{ github.event.inputs.environment == 'internal' && secrets.AZURE_CLIENT_ID || secrets.AZURE_CDC_CLIENT_ID }}
          tenant-id: ${{ github.event.inputs.environment == 'internal' && secrets.AZURE_TENANT_ID || secrets.AZURE_CDC_TENANT_ID}}
          subscription-id: ${{ github.event.inputs.environment == 'internal' && secrets.AZURE_SUBSCRIPTION_ID || secrets.AZURE_CDC_DMZ_C1_SUBSCRIPTION_ID}}

      - name: Extract database hostname and password into GitHub Env
        run: |
          cd ./operations/environments/${{ github.event.inputs.environment }}
          DATABASE_HOSTNAME=$(terraform output -raw database_hostname)
          DATABASE_PASSWORD=$(az account get-access-token --resource-type oss-rdbms --query "[accessToken]" -o tsv)
          echo "::add-mask::$DATABASE_HOSTNAME"
          echo "::add-mask::$DATABASE_PASSWORD"
          echo "DATABASE_HOSTNAME=$DATABASE_HOSTNAME" >> "$GITHUB_ENV"
          echo "DATABASE_PASSWORD=$DATABASE_PASSWORD" >> "$GITHUB_ENV"

      - name: Connect to VPN
        uses: josiahsiegel/action-connect-ovpn@v2.0.2
        id: connect_vpn
        if: github.event.inputs.environment != 'internal'
        with:
          PING_URL: ${{ env.DATABASE_HOSTNAME }}
          FILE_OVPN: ./operations/vpn/${{ github.event.inputs.environment }}.ovpn
          TLS_KEY: ${{ secrets.VPN_TLS_KEY }}
        env:
          CA_CRT: ${{ secrets.VPN_CA_CERTIFICATE }}
          USER_CRT: ${{ secrets.VPN_GITHUB_CERTIFICATE }}
          USER_KEY: ${{ secrets.VPN_GITHUB_SECRET_KEY }}

      - name: Fail if VPN isn't Connected
        if: github.event.inputs.environment != 'internal' && steps.connect_vpn.outputs.STATUS != 'true'
        run: |
          echo 'VPN connected: ${{ steps.connect_vpn.outputs.STATUS }}'
          exit 1

      - name: Install Liquibase
        run: |
          wget -O- https://repo.liquibase.com/liquibase.asc | gpg --dearmor > liquibase-keyring.gpg && \cat liquibase-keyring.gpg | sudo tee /usr/share/keyrings/liquibase-keyring.gpg > /dev/null && \echo 'deb [arch=amd64 signed-by=/usr/share/keyrings/liquibase-keyring.gpg] https://repo.liquibase.com stable main' | sudo tee /etc/apt/sources.list.d/liquibase.list
          sudo apt-get update
          sudo apt-get install liquibase
          liquibase -v

      - name: Run Db migration
        run: liquibase rollback-count --changelog-file ${{ github.event.inputs.rollbackFile }} --count ${{ github.event.inputs.rollbackCount }} --url 'jdbc:postgresql://${{ env.DATABASE_HOSTNAME }}:5432/postgres' --username cdcti-github --password '${{ env.DATABASE_PASSWORD }}'

      - name: Disconnect VPN
        if: github.event.inputs.environment != 'internal' && always()
        run: sudo killall openvpn