securityScan.yml

name: Security Scan

# Single workflow, single job. Triggered three ways with DIFFERENT
# thresholds:
#
#   - pull_request to main: fail the job on any unsuppressed
#     CVSS >= 7 finding (HIGH+). MEDIUM/LOW findings show in the step
#     summary but don't block merges. Not yet required-to-merge in
#     branch protection.
#
#   - cron (weekly): report ALL findings regardless of severity. Sends
#     an email with the full sorted list and fails the job on any
#     finding. The intent is full situational awareness for the team --
#     emerging MEDIUM risks should be visible before they cross the PR
#     gate, and the weekly is read by humans, not enforced by code.
#
#   - workflow_dispatch: behaves like the cron run (full reporting).
#
# Scanner: OSV-Scanner v2.3.8 (purl-based via OSV.dev; federates GHSA,
# NVD, npm advisory DB, RustSec, Go vuln DB, PyPA). Reads
# `package-lock.json` natively -- no separate SBOM tool needed.
#
# NOTE: this scans BOTH runtime and devDependencies (OSV treats
# everything in package-lock.json equally). If a finding is dev-only
# and shouldn't block merges, suppress it via osv-scanner.toml with a
# justification ("dev-only, not shipped in dist/").
#
# Suppressions live in `osv-scanner.toml` as [[IgnoredVulns]] entries
# (CVE-id global; OSV-Scanner v2.3.8 doesn't support per-package CVE
# scoping). Each entry has a justification comment.

on:
  pull_request:
    branches: [main]
  schedule:
    - cron: '0 0 * * 0'  # Run every Sunday at midnight UTC
  workflow_dispatch:

permissions:
  id-token: write
  contents: read

jobs:
  security-scan:
    name: Security Scan
    runs-on:
      group: databricks-protected-runner-group
      labels: linux-ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@34e114876b0b11c390a56381ad16ebd13914f8d5 # v4

      # JFrog OIDC + npm registry: skipped on fork PRs (no OIDC token
      # from GitHub's perspective). OSV-Scanner reads package-lock.json
      # directly without fetching from the npm registry, so fork PRs
      # still work; we keep setup-jfrog here only for parity with the
      # other workflows in this repo. If you remove it later, also
      # remove the `id-token: write` permission above.
      - name: Setup JFrog
        if: github.event_name != 'pull_request' || github.event.pull_request.head.repo.full_name == github.repository
        uses: ./.github/actions/setup-jfrog

      - name: Install osv-scanner
        run: |
          set -euo pipefail
          curl -fsSL -o /tmp/osv-scanner \
            https://github.com/google/osv-scanner/releases/download/v2.3.8/osv-scanner_linux_amd64
          chmod +x /tmp/osv-scanner
          /tmp/osv-scanner --version

      - name: Run OSV-Scanner
        # Drop -e because osv-scanner exits 1 on ANY finding regardless of
        # severity. The severity >= 7 filter below is our actual gate, so
        # we explicitly tolerate osv-scanner's non-zero exit via `|| true`.
        run: |
          set -uo pipefail

          if [ ! -f package-lock.json ]; then
            echo "::error::package-lock.json not found at repo root."
            exit 1
          fi

          /tmp/osv-scanner scan source \
            --lockfile=package-lock.json \
            --config=osv-scanner.toml \
            --format=json \
            --output-file=/tmp/osv-out.json \
            || true

          if [ ! -s /tmp/osv-out.json ]; then
            echo "::error::OSV-Scanner did not produce an output file."
            exit 1
          fi

      # Parse OSV's JSON into job outputs. The terminal steps below
      # (PR-fail and email) consume these outputs.
      #
      # Two thresholds: PR gating uses CVSS >= 7 (high_count) so we don't
      # block merges on MEDIUM/LOW noise; the weekly email reports
      # everything (total_findings) so the team has full situational
      # awareness of emerging risk before it crosses the gate.
      - name: Collect findings
        id: findings
        run: |
          set -uo pipefail

          # All findings (sorted by severity desc). Anything missing a
          # CVSS score sorts to 0 -- visible in the report but not silent.
          ALL_FINDINGS=$(jq -c '[
            .results[].packages[]? |
            .package as $pkg |
            .groups[]? |
            {pkg: ($pkg.name + "@" + $pkg.version), ids: .ids, severity: (.max_severity // "0")}
          ] | sort_by(- (.severity | tonumber? // 0))' /tmp/osv-out.json)
          TOTAL_FINDINGS=$(echo "$ALL_FINDINGS" | jq 'length')

          # High findings (CVSS >= 7). Both counters are logged so a
          # mismatch (e.g. 50 total / 0 high) is visible -- protects
          # against silent fail-open if OSV ever changes its severity
          # format (e.g. emits "HIGH" instead of a number, which
          # `tonumber? // 0` would mask).
          HIGH_FINDINGS=$(echo "$ALL_FINDINGS" | jq -c '[.[] | select((.severity | tonumber? // 0) >= 7)]')
          HIGH_COUNT=$(echo "$HIGH_FINDINGS" | jq 'length')

          # Persist the full findings list to a file rather than a job
          # output -- GitHub Actions outputs are size-capped at 1 MB and
          # the formatted email body can be larger than that for big
          # finding lists.
          echo "$ALL_FINDINGS" > /tmp/all-findings.json

          echo "total_findings=$TOTAL_FINDINGS" >> "$GITHUB_OUTPUT"
          echo "high_count=$HIGH_COUNT" >> "$GITHUB_OUTPUT"

          # Step summary so findings are visible in the GH Actions UI
          # without downloading artifacts.
          {
            echo "## OSV-Scanner Findings"
            echo ""
            echo "- Total findings (any severity): \`$TOTAL_FINDINGS\`"
            echo "- High findings (CVSS >= 7, PR-blocking): \`$HIGH_COUNT\`"
            if [ "$TOTAL_FINDINGS" -gt 0 ]; then
              echo ""
              echo "All findings (sorted by severity desc):"
              echo ""
              echo "| Severity | Package | IDs |"
              echo "|---|---|---|"
              echo "$ALL_FINDINGS" | jq -r '.[] | "| \(.severity) | \(.pkg) | \(.ids | join(",")) |"'
            fi
          } >> "$GITHUB_STEP_SUMMARY"

          # Also dump the findings to the job log so they're visible in
          # the default "Logs" view, not just the step summary panel.
          echo "OSV: $TOTAL_FINDINGS total findings, $HIGH_COUNT at CVSS>=7"
          if [ "$TOTAL_FINDINGS" -gt 0 ]; then
            echo ""
            echo "All findings (sorted by severity desc):"
            echo "$ALL_FINDINGS" | jq -r '.[] | "  [\(.severity)] \(.pkg)  \(.ids | join(", "))"'
          fi

      # --- Terminal: PR event ---
      # Fail the job so the PR's check goes red. No email.
      # PR gate is CVSS >= 7 only; MEDIUM/LOW findings show up in the
      # step summary but don't block merges.
      - name: Fail on findings (PR)
        if: github.event_name == 'pull_request' && steps.findings.outputs.high_count != '0'
        run: |
          set -uo pipefail
          # List the actual HIGH findings inline so the author sees what
          # needs fixing without clicking through to the step summary
          # panel or downloading artifacts.
          HIGH_FINDINGS=$(jq -c '[.[] | select((.severity | tonumber? // 0) >= 7)]' /tmp/all-findings.json)

          echo "::error::${{ steps.findings.outputs.high_count }} unsuppressed CVSS>=7 finding(s) in this PR:"
          echo ""
          echo "$HIGH_FINDINGS" | jq -r '.[] | "  [\(.severity)] \(.pkg)  \(.ids | join(", "))"'
          echo ""
          echo "Fix by either:"
          echo "  1. Bumping the affected dependency to a patched version, or"
          echo "  2. Adding a documented [[IgnoredVulns]] entry to osv-scanner.toml"
          echo "     with a clear justification for why the CVE doesn't apply to our usage."
          echo ""
          echo "Full step summary: $GITHUB_SERVER_URL/$GITHUB_REPOSITORY/actions/runs/$GITHUB_RUN_ID"
          exit 1

      # --- Terminal: scheduled/manual event ---
      # Weekly reports ALL findings (not just CVSS >= 7) so the team sees
      # emerging risk before it crosses the PR gate. PR-time is narrower
      # to avoid blocking on MEDIUM/LOW noise; weekly is broader because
      # it's read by humans, not enforced.
      - name: Compose email body
        if: (github.event_name == 'schedule' || github.event_name == 'workflow_dispatch') && steps.findings.outputs.total_findings != '0'
        run: |
          set -uo pipefail
          {
            echo "<!DOCTYPE html><html><head><title>SQL Node.js Driver Security Scan Results</title>"
            echo "<style>"
            echo "  body { font-family: -apple-system, sans-serif; }"
            echo "  table { border-collapse: collapse; margin-top: 1em; }"
            echo "  th, td { border: 1px solid #ddd; padding: 6px 12px; text-align: left; }"
            echo "  th { background: #f5f5f5; }"
            echo "  tr.high { background: #ffe5e5; }"
            echo "  tr.medium { background: #fff5e5; }"
            echo "</style></head><body>"
            echo "<h1>Security Vulnerabilities Found</h1>"
            echo "<p><b>${{ steps.findings.outputs.total_findings }}</b> total finding(s) on main; <b>${{ steps.findings.outputs.high_count }}</b> are CVSS &gt;= 7 (PR-blocking).</p>"
            echo "<p>Full reports are attached to the GitHub Actions run as artifacts: <a href='https://github.com/${{ github.repository }}/actions/runs/${{ github.run_id }}'>View Artifacts</a></p>"
            echo "<table><tr><th>Severity</th><th>Package</th><th>IDs</th></tr>"
            jq -r '.[] |
              (if (.severity | tonumber? // 0) >= 7 then "high"
               elif (.severity | tonumber? // 0) >= 4 then "medium"
               else "" end) as $cls |
              "<tr class=\"\($cls)\"><td>\(.severity)</td><td>\(.pkg)</td><td>\(.ids | join(", "))</td></tr>"
            ' /tmp/all-findings.json
            echo "</table>"
            echo "</body></html>"
          } > security-scan-report.html

      - name: Send Email
        if: (github.event_name == 'schedule' || github.event_name == 'workflow_dispatch') && steps.findings.outputs.total_findings != '0'
        uses: dawidd6/action-send-mail@4226df7daafa6fc901a43789c49bf7ab309066e7 # v3
        with:
          server_address: smtp.gmail.com
          server_port: 465
          username: ${{ secrets.SMTP_USERNAME }}
          password: ${{ secrets.SMTP_PASSWORD }}
          subject: OSS SQL Node.js Driver Security Scan - 🚨 Vulnerabilities Found
          html_body: file://security-scan-report.html
          to: ${{ secrets.EMAIL_RECIPIENTS }}
          from: SQL Node.js Driver Security Scanner
          content_type: text/html

      - name: Fail on findings (scheduled/manual)
        if: (github.event_name == 'schedule' || github.event_name == 'workflow_dispatch') && steps.findings.outputs.total_findings != '0'
        run: |
          echo "::error::${{ steps.findings.outputs.total_findings }} OSV finding(s) on main (${{ steps.findings.outputs.high_count }} at CVSS>=7). Email sent."
          exit 1

      # Always upload artifacts so triagers can pull the full reports
      # without having to rerun anything.
      - name: Upload reports
        if: always()
        uses: actions/upload-artifact@ea165f8d65b6e75b540449e92b4886f43607fa02 # v4
        with:
          name: security-scan-reports
          path: |
            /tmp/osv-out.json
            security-scan-report.html
          if-no-files-found: ignore