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

permissions: {}

jobs:
  cleanup:
    name: Cleanup and Image Promotion
    uses: bcgov/quickstart-openshift-helpers/.github/workflows/.pr-close.yml@v0.8.3
    permissions:
      packages: write
    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: Remove PR user and database from crunchy
        continue-on-error: true
        uses: bcgov/action-oc-runner@v0.3.0
        with:
          oc_namespace: ${{ secrets.oc_namespace }}
          oc_token: ${{ secrets.oc_token }}
          oc_server: ${{ vars.oc_server }}
          commands: |
            # 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'