rumble-cve-data.yaml

name: Rumble CVE Files
on:
  schedule:
    - cron: "1 5 * * *"
  workflow_dispatch:
  push:
    branches: [auto-rumble]

env:
  PROJECT_ID: "${{ secrets.PROJECT_ID }}"
  STORAGE_BUCKET: "${{ secrets.STORAGE_BUCKET }}"
  WORKLOAD_IDENTITY_PROVIDER: "${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}"
  SERVICE_ACCOUNT: "${{ secrets.GH_ACTION_SERVICE_ACCOUNT }}"
  GH_TOKEN: ${{ github.token }}

jobs:
  generate-cve-files:
    runs-on: ubuntu-latest

    strategy: 
      matrix:
        image: ["bash","busybox","cassandra","curl","deno","dotnet-runtime","dotnet-sdk","dex","etcd","git","go","gradle","haproxy","jenkins","kube-state-metrics","influxdb","mariadb","maven","memcached","minio","minio-client","nats","nginx","node","opensearch","php","postgres","python","rabbitmq","r-base","redis","ruby","rust","telegraf","traefik","wait-for-it","wolfi-base","zookeeper"]
        format: ["csv"] # supports JSON as well, but CSVs are smaller

    permissions:
      contents: write
      id-token: write
      
    steps:
      - name: 'Checkout default branch to $GITHUB_WORKSPACE dir'
        uses: actions/checkout@2541b1294d2704b0964813337f33b291d3f8596b # v3
    
      - name: Authenticate to Google Cloud
        id: auth
        uses: google-github-actions/auth@ceee102ec2387dd9e844e01b530ccd4ec87ce955 # v0
        with:
          token_format: 'access_token'
          project_id: "${{ env.PROJECT_ID }}"
          workload_identity_provider: "${{ env.WORKLOAD_IDENTITY_PROVIDER }}"
          service_account: "${{ env.SERVICE_ACCOUNT }}"

      - name: set up bigqueryrc
        shell: bash
        run: |
          gcloud config set auth/impersonate_service_account "${{ env.SERVICE_ACCOUNT }}"
          # the following is just used to quiet the bigqueryrc init message, the query result is unused
          bq query --use_legacy_sql=false --format=csv --max_rows=1 'SELECT COUNT(*) FROM base-image-rumble.rumble.scheduled;' 2>&1 > /dev/null

      - name: get images to compare
        run: |
          theirs=$(cat data/rumble.json |jq -r '.[] | select(.image == "${{ matrix.image}}") .left')
          ours=$(cat data/rumble.json |jq -r '.[] | select(.image == "${{ matrix.image}}") .right')
          echo "THEIRS=$theirs" >> "$GITHUB_ENV"
          echo "OURS=$ours" >> "$GITHUB_ENV"

      - name: rumble query
        shell: bash
        run: >
          bq query --use_legacy_sql=false --format=${{ matrix.format }} --max_rows=100000\
            'WITH ruuuumble AS (
              SELECT s1.image,
                      s1.time as t,
                      s2.name as package,
                      s2.vulnerability,
                      s2.installed as version,
                      s2.type,
                      s2.severity
              FROM base-image-rumble.rumble.scheduled_vulns
              AS s2
              INNER JOIN base-image-rumble.rumble.scheduled
              AS s1
              ON s1.id = s2.scan_id
              WHERE s1.image = "${{ env.THEIRS }}"
              OR s1.image = "${{ env.OURS }}"
            )
            SELECT image, package, vulnerability, version, type, severity as s FROM ruuuumble
            WHERE DATE(t) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
            GROUP BY vulnerability, image, package, version, type, s
            ORDER BY (
              CASE WHEN s="Critical" THEN 1
                  WHEN s="High" THEN 2
                  WHEN s="Medium" THEN 3
                  WHEN s="Low" THEN 4
                  WHEN s="Negligible" THEN 5
                  WHEN s="Unknown" THEN 6
                  ELSE 7
              END
            );' > /tmp/${{ matrix.image }}.${{ matrix.format }}

      - name: upload generated file
        shell: bash
        run: |
          gcloud storage cp /tmp/${{ matrix.image }}.${{ matrix.format }} gs://chainguard-academy/cve-data/${{ matrix.image }}.${{ matrix.format }}

      - name: update permissions on file
        shell: bash
        run: |
          gcloud storage objects update gs://chainguard-academy/cve-data/${{ matrix.image }}.${{ matrix.format }} \
            --add-acl-grant=entity=AllUsers,role=READER \
            --content-type text/${{ matrix.format }} \
            --cache-control "public, max-age=60"