Add OSV-Scanner-based security workflow #1
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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, PyPA, RustSec, Go vuln DB). Reads `poetry.lock` natively -- | |
| # no separate SBOM tool needed. | |
| # | |
| # 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 + pip: skipped on fork PRs (no OIDC token from | |
| # GitHub's perspective). OSV-Scanner reads poetry.lock directly | |
| # without needing to download wheels, 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 poetry.lock ]; then | |
| echo "::error::poetry.lock not found at repo root." | |
| exit 1 | |
| fi | |
| /tmp/osv-scanner scan source \ | |
| --lockfile=poetry.lock \ | |
| --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 Python 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 >= 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 Python Driver Security Scan - 🚨 Vulnerabilities Found | |
| html_body: file://security-scan-report.html | |
| to: ${{ secrets.EMAIL_RECIPIENTS }} | |
| from: SQL Python 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 |