codeql-analysis.yml

# For most projects, this workflow file will not need changing; you simply need
# to commit it to your repository.
#
# You may wish to alter this file to override the set of languages analyzed,
# or to provide custom queries or build logic.
name: "CodeQL"

on:
  push:
    branches: [master]
  pull_request:
    # The branches below must be a subset of the branches above
    branches: [master]
  schedule:
    - cron: "0 4 * * 3"

env:
  javaVersion: java26

permissions: read-all

jobs:
  analyze:
    name: Analyze
    runs-on: ubuntu-24.04
    permissions:
      contents: write # Required for dependency submission and repository access
      security-events: write # Required for CodeQL, checkov, and dependency submission
      pull-requests: write # Needed for PR analysis
      actions: read # Needed for CodeQL action metadata
    strategy:
      fail-fast: false
      matrix:
        language: ["java"] # Define language matrix explicitly

    steps:
      - name: Harden Runner
        uses: step-security/harden-runner@ab7a9404c0f3da075243ca237b5fac12c98deaa5 # v2.19.3
        with:
          egress-policy: audit
          allowed-endpoints: >
            api.adoptopenjdk.net:443
            api.adoptium.net:443
            api.github.com:443
            apt.postgresql.org:443
            archive.apache.org:443
            azure.archive.ubuntu.com:80
            azure.archive.ubuntu.com:443
            security.ubuntu.com:80
            security.ubuntu.com:443
            github.com:443
            maven.java.net:443
            maven.vaadin.com:443
            objects.githubusercontent.com:443
            oss.sonatype.org:443
            raw.githubusercontent.com:443
            repo.maven.apache.org:443
            maven.mirrors.opennms.org:443
            repo1.maven.org:443
            repository.mulesoft.org:443
            tools.google.com:80
            tools.vaadin.com:443
            uploads.github.com:443
            spdx.org:443
            sonarcloud.io:443
            repository.jboss.org:443
            repository.sonatype.org:443
            files.pythonhosted.org:443
            pypi.org:443
            www.bridgecrew.cloud:443
            www.postgresql.org:443
            docs.github.com:443
            dlcdn.apache.org:443
            gwtproject.org:443
            java.sun.com:443
            jetty.org:443
      - name: Checkout repository
        uses: actions/checkout@8e8c483db84b4bee98b60c0593521ed34d9990e8 # v6.0.1
        with:
          # We must fetch at least the immediate parents so that if this is
          # a pull request then we can checkout the head.
          fetch-depth: 2
      - name: Run Checkov action
        id: checkov
        uses: bridgecrewio/checkov-action@4048c972aae68d0b983a48bb3479aab2d877b898 # master
        with:
          file: cia-dist-cloudformation/src/main/resources/cia-dist-cloudformation.json
          skip_check: CKV_AWS_111,CKV_AWS_109,CKV_AWS_149,CKV_AWS_364,CKV_AWS_161,CKV_AWS_118,CKV_AWS_111
          framework: cloudformation
          output_format: sarif
          output_file_path: reports/results.sarif
          download_external_modules: true
      # Initializes the CodeQL tools for scanning.
      - name: Initialize CodeQL
        uses: github/codeql-action/init@9e0d7b8d25671d64c341c19c0152d693099fb5ba # v3.29.5
        with:
          languages: ${{ matrix.language }}
          dependency-caching: true
          queries: security-extended # More comprehensive security analysis

      # Autobuild attempts to build any compiled languages  (C/C++, C#, or Java).
      # If this step fails, then you should remove it and run the build manually (see below)
      #    - name: Autobuild
      #      uses: github/codeql-action/autobuild@v1

      # ████ Command-line programs to run using the OS shell.
      # ████ https://git.io/JvXDl

      # ████ If the Autobuild fails above, remove it and uncomment the following three lines
      #    and modify them (or add more) to build your code if your project
      #    uses a compiled language

      #- run: |
      #   make bootstrap
      #   make release

      - name: Set up JDK 26
        uses: actions/setup-java@be666c2fcd27ec809703dec50e508c2fdc7f6654 # v5.2.0
        with:
          distribution: "temurin"
          java-version: "26"
          # Disable built-in cache - we use optimized manual caching below
          cache: ""

      # Optimized Maven caching with multiple fallback levels for better resilience
      - name: Cache Maven dependencies
        uses: actions/cache@27d5ce7f107fe9357f9df03efb73ab90386fccae # v5.0.5
        with:
          path: |
            ~/.m2/repository
            ~/.m2/wrapper
            ~/.sonar/cache
          # Include Maven version in key for isolation
          key: ${{ runner.os }}-maven-3.9.15-${{ hashFiles('**/pom.xml', '.mvn/**') }}
          restore-keys: |
            ${{ runner.os }}-maven-3.9.15-${{ hashFiles('**/pom.xml') }}
            ${{ runner.os }}-maven-3.9.15-
            ${{ runner.os }}-maven-

      - name: Cache APT packages
        uses: actions/cache@27d5ce7f107fe9357f9df03efb73ab90386fccae # v5.0.5
        with:
          path: /var/cache/apt/archives
          key: ${{ runner.os }}-apt-${{ hashFiles('.github/workflows/codeql-analysis.yml') }}
          restore-keys: |
            ${{ runner.os }}-apt-

      - name: Add PostgreSQL PGDG repository
        run: |
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
          echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

      - name: APT update
        run: sudo apt-get update

      - name: Install buildtools and PostgreSQL
        run: sudo apt-get install -y graphviz build-essential fakeroot devscripts debhelper dh-make wget ant postgresql-18 postgresql-contrib-18 postgresql-18-pgaudit postgresql-18-pgvector

      - name: Create PostgreSQL 18 cluster
        run: |
          # Drop pre-installed PostgreSQL clusters that block port 5432
          sudo pg_dropcluster --stop 14 main || true
          sudo pg_dropcluster --stop 16 main || true
          # Create and start PostgreSQL 18 cluster (idempotent)
          if sudo pg_lsclusters 18 main >/dev/null 2>&1; then
            sudo pg_ctlcluster 18 main start
          else
            sudo pg_createcluster 18 main --start
          fi

      - name: Configure PostgreSQL
        run: |
          # Enable prepared transactions and required extensions
          sudo sed -i "s/#max_prepared_transactions = 0/max_prepared_transactions = 100/" /etc/postgresql/18/main/postgresql.conf
          sudo sed -i "s/#shared_preload_libraries = ''/shared_preload_libraries = 'pg_stat_statements, pgaudit, pgcrypto'/" /etc/postgresql/18/main/postgresql.conf
          echo "pgaudit.log = ddl" | sudo tee -a /etc/postgresql/18/main/postgresql.conf
          echo "pg_stat_statements.track = all" | sudo tee -a /etc/postgresql/18/main/postgresql.conf
          echo "pg_stat_statements.max = 10000" | sudo tee -a /etc/postgresql/18/main/postgresql.conf

          # Add IPv6 loopback access
          echo "host all all ::1/128 md5" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf

      - name: Generate SSL certificates for PostgreSQL
        run: |
          # Generate secure random passphrase
          openssl rand -base64 48 > passphrase.txt

          # Create passphrase-protected private key (increased to 4096 bits)
          openssl genrsa -des3 -passout file:passphrase.txt -out server.pass.key 4096

          # Remove passphrase protection from private key
          openssl rsa -passin file:passphrase.txt -in server.pass.key -out server.key
          rm server.pass.key

          # Create OpenSSL config file with Subject Alternative Names
          cat > openssl.cnf <<EOL
          [req]
          default_bits = 4096
          prompt = no
          default_md = sha256
          req_extensions = req_ext
          distinguished_name = dn

          [dn]
          C=UK
          ST=PostgreSQL
          L=Docker
          O=Hack23
          OU=demo
          CN=localhost

          [req_ext]
          subjectAltName = @alt_names

          [alt_names]
          DNS.1 = localhost
          IP.1 = 127.0.0.1
          EOL

          # Create Certificate Signing Request with config
          openssl req -new -key server.key -out server.csr -config openssl.cnf

          # Self-sign the certificate with extensions (valid for 10 years)
          openssl x509 -req -days 3650 -in server.csr -signkey server.key -out server.crt -extensions req_ext -extfile openssl.cnf

          # Clean up temporary files
          rm passphrase.txt server.csr openssl.cnf

      - name: Deploy SSL certificate and key for PostgreSQL
        run: |
          # Secure the certificate files
          chmod 600 server.key server.crt

          # Copy certificate and key to PostgreSQL data directory
          sudo cp server.crt /var/lib/postgresql/18/main/server.crt
          sudo cp server.key /var/lib/postgresql/18/main/server.key

          # Secure the certificate and key
          sudo chmod 600 /var/lib/postgresql/18/main/server.key
          sudo chmod 644 /var/lib/postgresql/18/main/server.crt
          sudo chown postgres:postgres /var/lib/postgresql/18/main/server.key
          sudo chown postgres:postgres /var/lib/postgresql/18/main/server.crt

          # Enable SSL in PostgreSQL
          echo "ssl = on" | sudo tee -a /etc/postgresql/18/main/postgresql.conf
          echo "ssl_cert_file = '/var/lib/postgresql/18/main/server.crt'" | sudo tee -a /etc/postgresql/18/main/postgresql.conf
          echo "ssl_key_file = '/var/lib/postgresql/18/main/server.key'" | sudo tee -a /etc/postgresql/18/main/postgresql.conf

          # Provide SSL certificate to the runner user
          mkdir -p $HOME/.postgresql
          cp server.crt $HOME/.postgresql/root.crt
          chmod 600 $HOME/.postgresql/root.crt

          # Clean up
          rm server.key server.crt

          # Restart PostgreSQL to apply all changes
          sudo systemctl restart postgresql

      - name: Create CIA database and user
        run: |
          sudo -u postgres psql -c "CREATE USER eris WITH PASSWORD 'discord';"
          sudo -u postgres psql -c "CREATE DATABASE cia_dev;"
          sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE cia_dev TO eris;"
          sudo -u postgres psql -c "ALTER USER eris WITH SUPERUSER;"
          sudo -u postgres psql -d cia_dev -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
          sudo -u postgres psql -d cia_dev -c "CREATE EXTENSION IF NOT EXISTS pgaudit;"
          sudo -u postgres psql -d cia_dev -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"
          sudo -u postgres psql -d cia_dev -c "GRANT ALL ON SCHEMA public TO eris;"
          sudo -u postgres psql -d cia_dev -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO eris;"
          sudo -u postgres psql -d cia_dev -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO eris;"
          sudo -u postgres psql -d cia_dev -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO eris;"

      - name: Load database schema
        run: |
          # Load the full schema into the database
          sudo -u postgres psql -d cia_dev -f service.data.impl/src/main/resources/full_schema.sql || echo "Schema load completed with warnings (expected for first-time setup)"

          # Verify schema loaded successfully by checking for key tables
          echo "Verifying schema loaded..."
          sudo -u postgres psql -d cia_dev -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" || echo "Schema verification completed"

      - name: Verify PostgreSQL SSL configuration
        run: |
          # Verify SSL is enabled
          sudo -u postgres psql -c "SHOW ssl;" | grep on

          # Verify prepared transactions are enabled
          sudo -u postgres psql -c "SHOW max_prepared_transactions;" | grep 100

          # Verify extensions are loaded
          sudo -u postgres psql -d cia_dev -c "\dx" | grep pg_stat_statements
          sudo -u postgres psql -d cia_dev -c "\dx" | grep pgaudit
          sudo -u postgres psql -d cia_dev -c "\dx" | grep pgcrypto

          # Test SSL connection from localhost
          echo "Testing SSL connection..."
          PGPASSWORD=discord PGSSLMODE=require psql -h localhost -U eris -d cia_dev -c "SELECT version();" || echo "SSL connection test completed"

      - name: Set up Maven
        uses: stCarolas/setup-maven@d6af6abeda15e98926a57b5aa970a96bb37f97d1 # v5
        with:
          maven-version: 3.9.15

      - name: Build with Maven
        run: mvn -B clean install --file pom.xml -Prelease-site,all-modules -DskipTests -DfailIfNoTests=false -Dsurefire.failIfNoSpecifiedTests=false -Dspdx.skip=true -Dmaven.wagon.http.retryHandler.count=3 -Dmaven.wagon.httpconnectionManager.ttlSeconds=120 -Dmaven.wagon.http.pool=true

      - name: Run all unit tests (excluding integration tests)
        run: mvn -B test --file pom.xml -Prelease-site,all-modules -Dtest='!**ITest*,!**/XmlDateTypeAdapterTest,!**/XmlTimeTypeAdapterTest,!**/XmlDateTimeTypeAdapterTest' -DfailIfNoTests=false -Dsurefire.failIfNoSpecifiedTests=false -Dspdx.skip=true -Dmaven.wagon.http.retryHandler.count=3 -Dmaven.wagon.httpconnectionManager.ttlSeconds=120 -Dmaven.wagon.http.pool=true
        continue-on-error: false

      - name: Validate app startup and database connection
        run: |
          cd citizen-intelligence-agency

          # Start the application in background with a timeout
          echo "Starting application..."
          timeout 240 ant start > /tmp/app-startup.log 2>&1 &
          APP_PID=$!

          echo "Application starting with PID: $APP_PID"

          # Wait for application to start and apply database changelogs
          MAX_WAIT=60
          for i in $(seq 1 $MAX_WAIT); do
            if sudo -u postgres psql -d cia_dev -c "SELECT COUNT(*) FROM databasechangelog;" > /dev/null 2>&1; then
              echo "✓ Database changelogs verified successfully"
              CHANGELOG_COUNT=$(sudo -u postgres psql -d cia_dev -t -c "SELECT COUNT(*) FROM databasechangelog;")
              echo "  Found $CHANGELOG_COUNT changelog entries"
              break
            fi
            echo "Waiting for application to initialize and apply changelogs... ($i/$MAX_WAIT)"
            sleep 3
          done

          # Check if we successfully verified the changelogs
          if sudo -u postgres psql -d cia_dev -c "SELECT COUNT(*) FROM databasechangelog;" > /dev/null 2>&1; then
            echo "✓ Application startup validation successful"
          else
            echo "⚠ Database changelog table not created yet - check logs"
            echo "Application logs:"
            tail -50 /tmp/app-startup.log || echo "No logs available"
          fi

          PORT=28443
          APP_URL="https://localhost:${PORT}/cia/"
          MAX_PORT_WAIT=60
          PORT_READY=0

          # Wait for port to be listening
          echo "Waiting for port ${PORT} to open..."
          for i in $(seq 1 30); do
            if ss -ltn | grep -q ":${PORT} "; then
              echo "✓ Port ${PORT} is listening (${i}/30)"
              break
            fi
            echo "  Waiting for port ${PORT} to open (${i}/30)"
            sleep 2
          done

          # Now wait for actual HTTP readiness by monitoring logs
          echo "Waiting for application to be ready for requests..."
          for i in $(seq 1 $MAX_PORT_WAIT); do
            # Check if server reports it's started
            if grep -q "Server Started" /tmp/app-startup.log 2>/dev/null; then
              echo "✓ Application reports server started"
              # Give it a moment to finish initialization
              sleep 3
              
              # Try HTTP request
              if curl --insecure --silent --show-error --fail --max-time 5 "$APP_URL" >/dev/null 2>&1; then
                echo "✓ Application responded over HTTPS at ${APP_URL}"
                PORT_READY=1
                break
              else
                echo "  Server started but HTTP not ready yet, retrying..."
              fi
            fi
            
            # Fallback: check for Spring context initialization complete
            if grep -q "Root WebApplicationContext: initialization completed" /tmp/app-startup.log 2>/dev/null; then
              echo "✓ Spring context initialization completed"
              sleep 2
              
              if curl --insecure --silent --show-error --fail --max-time 5 "$APP_URL" >/dev/null 2>&1; then
                echo "✓ Application responded over HTTPS at ${APP_URL}"
                PORT_READY=1
                break
              fi
            fi
            
            sleep 2
          done

          if [ "$PORT_READY" -ne 1 ]; then
            echo "⚠ Application never responded on ${APP_URL}"
            echo "Server status:"
            ss -ltnp | grep "${PORT}" || true
            echo ""
            echo "Last 100 lines of application log:"
            tail -100 /tmp/app-startup.log || true
          else
            echo "✓ Application validation successful - server is ready"
          fi

          # Stop the application gracefully
          if kill -0 $APP_PID 2>/dev/null; then
            echo "Stopping application (PID: $APP_PID)..."
            kill $APP_PID 2>/dev/null || true
            sleep 2
            kill -9 $APP_PID 2>/dev/null || true
          fi

          wait $APP_PID 2>/dev/null || echo "Application validation completed"

      - name: Perform CodeQL Analysis
        uses: github/codeql-action/analyze@9e0d7b8d25671d64c341c19c0152d693099fb5ba # v3.29.5