pr-close.yml

name: PR Closed

on:
  pull_request:
    types: [closed]

concurrency:
  # PR open and close use the same group, allowing only one at a time
  group: ${{ github.event.number }}
  cancel-in-progress: true

jobs:
  cleanup:
    name: Cleanup and Images
    uses: bcgov/quickstart-openshift-helpers/.github/workflows/.pr-close.yml@v0.8.3
    secrets:
      oc_namespace: ${{ secrets.OC_NAMESPACE }}
      oc_token: ${{ secrets.OC_TOKEN }}
    with:
      cleanup: helm
      packages: backend frontend migrations
  cleanup_db: # TODO move it off to another action later.
    name: Remove DB User from crunchy.
    runs-on: ubuntu-24.04
    steps:
      - uses: actions/checkout@v4
        name: Checkout
      - name: Install CLI tools from OpenShift Mirror
        uses: redhat-actions/openshift-tools-installer@v1
        with:
          oc: "4.14.37"

      - name: OC Login
        shell: bash
        run: |
          # OC Login
          OC_TEMP_TOKEN=$(curl -k -X POST https://api.silver.devops.gov.bc.ca:6443/api/v1/namespaces/${{ secrets.oc_namespace }}/serviceaccounts/pipeline/token --header "Authorization: Bearer ${{ secrets.oc_token }}" -d '{"spec": {"expirationSeconds": 600}}' -H 'Content-Type: application/json; charset=utf-8' | jq -r '.status.token' )

          oc login --token=$OC_TEMP_TOKEN --server=https://api.silver.devops.gov.bc.ca:6443
          oc project ${{ secrets.oc_namespace }} # Safeguard!

      - name: Remove PR user and database from crunchy.
        shell: bash
        run: |
          # check if postgres-crunchy exists or else exit
          oc get PostgresCluster/postgres-crunchy || exit 0

          # Remove the user from the crunchy cluster yaml and apply the changes
          USER_TO_REMOVE='{"databases":["app-${{ github.event.number }}"],"name":"app-${{ github.event.number }}"}'
          
          echo 'getting current users from crunchy'
          CURRENT_USERS=$(oc get PostgresCluster/postgres-crunchy -o json | jq '.spec.users')
          echo "${CURRENT_USERS}"
          
          # Remove the user from the list, 
          UPDATED_USERS=$(echo "${CURRENT_USERS}" | jq --argjson user "${USER_TO_REMOVE}" 'map(select(. != $user))')

          PATCH_JSON=$(jq -n --argjson users "${UPDATED_USERS}" '{"spec": {"users": $users}}')
          oc patch PostgresCluster/postgres-crunchy --type=merge -p "${PATCH_JSON}"
          
          # get primary crunchy pod and remove the role and db
          CRUNCHY_PG_PRIMARY_POD_NAME=$(oc get pods -l postgres-operator.crunchydata.com/role=master -o json | jq -r '.items[0].metadata.name')
          
          echo "${CRUNCHY_PG_PRIMARY_POD_NAME}"
          # Terminate all connections to the database before trying terminate
          oc exec "${CRUNCHY_PG_PRIMARY_POD_NAME}" -- psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'app-${{ github.event.number }}' AND pid <> pg_backend_pid();"
          
          # Drop the database and role
          oc exec "${CRUNCHY_PG_PRIMARY_POD_NAME}" -- psql -c "DROP DATABASE \"app-${{ github.event.number }}\" --cascade"
          oc exec "${CRUNCHY_PG_PRIMARY_POD_NAME}" -- psql -c "DROP ROLE \"app-${{ github.event.number }}\" --cascade"
          echo 'database and role deleted'
          
          exit 0