vulndb.yaml

name: VulnDB Workflow

on: 
  workflow_dispatch:
  schedule:
    - cron: '0 */2 * * *' # every hour

env:
  POSTGRES_DB: devguard
  POSTGRES_USER: devguard
  POSTGRES_HOST: localhost
  POSTGRES_PASSWORD: not_reachable_from_the_internet
  DATE : $(date +%s)


jobs:
  build:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: ghcr.io/l3montree-dev/devguard-postgresql:v0.5.3@sha256:a06c9e7c8ee334790cc66d52e89ff5ef05352ab264841d3d9f3659c046732251
        env:
          POSTGRES_DB: ${{env.POSTGRES_DB}}
          POSTGRES_USER: ${{env.POSTGRES_USER}}
          POSTGRES_PASSWORD: ${{env.POSTGRES_PASSWORD}}


        ports:
        - 5432:5432
        options: "--health-cmd=\"pg_isready -U devguard\"  --health-interval=10s  --health-timeout=5s  --health-retries=5 "
    steps:
    - name: Install postgresql client
      run: |
        sudo apt-get update
        sudo apt-get install -y wget
        wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
        echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
        sudo apt-get update
        sudo apt-get install -y postgresql-client-16
    - name: Create semver extension
      run: |
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "CREATE EXTENSION IF NOT EXISTS semver;"
    - name: Checkout code
      uses: actions/checkout@v3
    - name: Install Golang
      uses: actions/setup-go@v5
      with:
        go-version: 1.22

     
    - name: Import the last database version (this takes some time)
      run: |
        go run ./cmd/devguard-cli/main.go vulndb import || true
    - name: Build the database (this takes some time)
      run: |
        # will fetch the latest build database from ghcr.io
        go run ./cmd/devguard-cli/main.go vulndb sync
    - name: Dump the PostgreSQL database
      # skip:checkov:CKV_SECRET_6
      run: |
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM affected_components) TO STDOUT WITH DELIMITER ',' CSV HEADER" > affected_component.csv 
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM cve_affected_component) TO STDOUT WITH DELIMITER ',' CSV HEADER" > cve_affected_component.csv 
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM cves) TO STDOUT WITH DELIMITER ',' CSV HEADER" > cves.csv 
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM cpe_matches) TO STDOUT WITH DELIMITER ',' CSV HEADER" > cpe_matches.csv  
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM cve_cpe_match) TO STDOUT WITH DELIMITER ',' CSV HEADER" > cve_cpe_match.csv 
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM cwes) TO STDOUT WITH DELIMITER ',' CSV HEADER" > cwes.csv 
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM exploits) TO STDOUT WITH DELIMITER ',' CSV HEADER" > exploits.csv 
        PGPASSWORD=${{env.POSTGRES_PASSWORD}} psql -h localhost -U devguard devguard -c "COPY (SELECT * FROM weaknesses) TO STDOUT WITH DELIMITER ',' CSV HEADER" > weaknesses.csv
    
    - name: install zip
      run: sudo apt-get install zip

    - name: Zip the CSV files
      run: zip vulndb.zip affected_component.csv  cve_affected_component.csv cves.csv cpe_matches.csv cve_cpe_match.csv cwes.csv exploits.csv weaknesses.csv
    - name: Install Cosign 
      uses: sigstore/cosign-installer@main

    - name: Write signing key to disk
      run: echo "${{ secrets.COSIGN_PRIVATE_KEY }}" > cosign.key

    - name: Sign the database zip file  
      env:
        COSIGN_PASSWORD: ${{ secrets.COSIGN_PASSWORD }}
      run: cosign sign-blob --yes --key cosign.key vulndb.zip > vulndb.zip.sig
    - name: Login to GitHub Container Registry
      uses: docker/login-action@v3
      with:
        registry: ghcr.io
        username: ${{ github.actor }}
        password: ${{ secrets.GITHUB_TOKEN }}
    - name: Setup oras cli
      uses: oras-project/setup-oras@v1

    - name: set the date
      run: echo "date="${{env.DATE}} >> "$GITHUB_ENV"

    - name: Push the database ZIP file to GitHub Container Registry
      run: |
        oras push ghcr.io/l3montree-dev/devguard/vulndb:$date vulndb.zip
        oras push ghcr.io/l3montree-dev/devguard/vulndb:latest vulndb.zip
    - name: Push the signatures to the GitHub Container Registry  
      run: |
        oras push ghcr.io/l3montree-dev/devguard/vulndb:$date.sig vulndb.zip.sig
        oras push ghcr.io/l3montree-dev/devguard/vulndb:latest.sig vulndb.zip.sig