llm-fuzzer.yaml

# This workflow uses an LLM agent to find bugs not covered by the existing
# tests. The LLM finds the possible bugs and generates a test script that
# triggers some kind of internal program error. The workflow then
# deterministically checks if the script actually reproduces the error, to
# avoid false positives.
name: LLM fuzzer
"on":
  workflow_dispatch:
  push:
    branches:
      - main
      - trigger/llm-fuzzer
  pull_request:

env:
  PG_SRC_DIR: "pgbuild"
  PG_INSTALL_DIR: "postgresql"
  extra_packages: "clang-15 llvm-15 llvm-15-dev llvm-15-tools"
  CC: "clang-15"
  # gcc CFLAGS, disable inlining for function name pattern matching to work for suppressions
  # CFLAGS: "-g -fsanitize=address,undefined -fno-omit-frame-pointer -O1 -fno-inline"
  # CXXFLAGS: "-g -fsanitize=address,undefined -fno-omit-frame-pointer -O1 -fno-inline"
  # clang CFLAGS
  CFLAGS: "-g -fsanitize=address,undefined -fno-omit-frame-pointer -Og -fno-inline-functions"
  CXXFLAGS: "-g -fsanitize=address,undefined -fno-omit-frame-pointer -Og -fno-inline-functions"

  # We do not link libasan dynamically to avoid problems with libdl and our libraries.
  # clang does this by default, but we need to explicitly state that for gcc.
  # static gcc LDFLAGS
  # LDFLAGS: "-fsanitize=address,undefined -static-libasan -static-liblsan -static-libubsan"
  # static sanitizer clang LDFLAGS or dynamic sanitizer gcc LDFLAGS
  LDFLAGS: "-fsanitize=address,undefined"
  ASAN_OPTIONS: suppressions=${{ github.workspace }}/scripts/suppressions/suppr_asan.txt
    detect_odr_violation=0 log_path=${{ github.workspace }}/sanitizer_logs/sanitizer
    log_exe_name=true print_suppressions=false exitcode=27
    detect_leaks=0 abort_on_error=1

  LSAN_OPTIONS: suppressions=${{ github.workspace }}/scripts/suppressions/suppr_leak.txt
    print_suppressions=0 log_path=${{ github.workspace }}/sanitizer_logs/sanitizer
    log_exe_name=true print_suppressions=false exitcode=27

  UBSAN_OPTIONS: suppressions=${{ github.workspace }}/scripts/suppressions/suppr_ub.txt
    print_stacktrace=1 halt_on_error=1 log_path=${{ github.workspace }}/sanitizer_logs/sanitizer
    log_exe_name=true print_suppressions=false exitcode=27

  EXTENSIONS: "postgres_fdw test_decoding"

jobs:
  config:
    runs-on: ubuntu-latest
    env:
        key: ${{ secrets.ANTHROPIC_API_KEY }}
    outputs:
      pg_latest: ${{ steps.setter.outputs.PG18_LATEST
        && fromJson(steps.setter.outputs.PG18_LATEST)
        || '' }}
    steps:
    - name: Checkout source code
      uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1
    - name: Read configuration
      if: env.key
      id: setter
      run: python .github/gh_config_reader.py

  fuzzer:
    if: needs.config.outputs.pg_latest
    name: ${{ matrix.display_name }}
    runs-on: timescaledb-runner-arm64
    needs: config
    permissions:
      contents: read
      pull-requests: read
      issues: read
    strategy:
      fail-fast: false
      matrix:
        include:
          - oracle: "internal-error"
            display_name: "Internal Program Errors"
          - oracle: "optimizations"
            display_name: "Optimizations ON-OFF"
          - oracle: "hypertable"
            display_name: "Hypertable ON-OFF"

    steps:
    - name: Install Linux Dependencies
      timeout-minutes: 15
      run: |
        sudo apt-get update
        sudo apt-get install ccache cmake flex bison icu-devtools systemd-coredump \
          gdb jq postgresql-client ${{ env.extra_packages }}

    - name: Checkout TimescaleDB
      uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1
      with:
        fetch-depth: 200

    - name: Fetch base branch
      if: github.event_name == 'pull_request'
      run: git fetch origin ${{ github.event.pull_request.base.ref }} --depth=200

    - name: Fetch main branch
      run: git fetch origin main --depth=200

    # We are going to rebuild Postgres weekly, so that it doesn't suddenly break
    # ages after the original problem.
    - name: Get date for build caching
      id: get-date
      run: |
        echo "date=$(date +"%V")" >> $GITHUB_OUTPUT

    # Create a directory for sanitizer logs. This directory is referenced by
    # ASAN_OPTIONS, LSAN_OPTIONS, and UBSAN_OPTIONS
    - name: Create sanitizer log directory
      run: |
        mkdir ${{ github.workspace }}/sanitizer_logs

    # we cache the build directory instead of the install directory here
    # because extension installation will write files to install directory
    # leading to a tainted cache
    - name: Restore PostgreSQL ${{ needs.config.outputs.pg_latest }} cache
      id: cache-postgresql
      uses: actions/cache/restore@55cc8345863c7cc4c66a329aec7e433d2d1c52a9 # v6.1.0
      with:
        path: ~/${{ env.PG_SRC_DIR }}
        key: "llm-fuzzer-postgresql-${{ needs.config.outputs.pg_latest }}-${{ env.CC }}\
         -${{ steps.get-date.outputs.date }}-${{ hashFiles('.github/**') }}"

    - name: Build PostgreSQL ${{ needs.config.outputs.pg_latest }} if not in cache
      if: steps.cache-postgresql.outputs.cache-hit != 'true'
      run: |
        wget -q --tries=6 --waitretry=15 -O postgresql.tar.bz2 \
          https://ftp.postgresql.org/pub/source/v${{ needs.config.outputs.pg_latest }}/postgresql-${{ needs.config.outputs.pg_latest }}.tar.bz2
        mkdir -p ~/$PG_SRC_DIR
        tar --extract --file postgresql.tar.bz2 --directory ~/$PG_SRC_DIR --strip-components 1
        # Add instrumentation to the Postgres memory contexts. For more details, see
        # https://github.com/timescale/eng-database/wiki/Using-Address-Sanitizer#adding-more-instrumentation
        PG_MAJOR=$(echo "${{ needs.config.outputs.pg_latest }}" | sed -e 's![.].*!!')
        if [ ${PG_MAJOR} -lt 18 ]; then
          patch -F5 -p1 -d ~/$PG_SRC_DIR < test/postgres-asan-instrumentation.patch
        else
          patch -F5 -p1 -d ~/$PG_SRC_DIR < test/postgres-asan-instrumentation-PG18GE.patch
        fi
        cd ~/$PG_SRC_DIR
        ./configure --prefix=$HOME/$PG_INSTALL_DIR --enable-debug --enable-cassert \
          --with-openssl --without-readline --without-zlib --without-libxml
        make -j$(getconf _NPROCESSORS_ONLN)
        for ext in ${EXTENSIONS}; do
          make -j$(getconf _NPROCESSORS_ONLN) -C contrib/${ext}
        done

    - name: Save PostgreSQL ${{ needs.config.outputs.pg_latest }} cache
      if: steps.cache-postgresql.outputs.cache-hit != 'true'
      uses: actions/cache/save@55cc8345863c7cc4c66a329aec7e433d2d1c52a9 # v6.1.0
      with:
        path: ~/${{ env.PG_SRC_DIR }}
        key: ${{ steps.cache-postgresql.outputs.cache-primary-key }}

    - name: Upload config.log
      if: always() && steps.cache-postgresql.outputs.cache-hit != 'true'
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: ${{ matrix.display_name }} config.log for PostgreSQL
        path: ~/${{ env.PG_SRC_DIR }}/config.log

    - name: Install PostgreSQL ${{ needs.config.outputs.pg_latest }}
      run: |
        cd ~/$PG_SRC_DIR
        make install
        for ext in ${EXTENSIONS}; do
          make -C contrib/${ext} install
        done
        ~/$PG_INSTALL_DIR/bin/pg_config --version

    - name: Build TimescaleDB
      run: |
        ./bootstrap -DCMAKE_BUILD_TYPE=Debug -DPG_SOURCE_DIR=~/$PG_SRC_DIR \
          -DPG_PATH=~/$PG_INSTALL_DIR -DCODECOVERAGE=OFF \
          -DCMAKE_C_COMPILER_LAUNCHER=ccache
        make -j$(getconf _NPROCESSORS_ONLN) -C build
        make -C build install

    - name: Install Claude Code
      run: npm install -g @anthropic-ai/claude-code@2.1.104

    - name: LLM Fuzzing
      id: llm-fuzzing
      env:
        ANTHROPIC_API_KEY: ${{ secrets.ANTHROPIC_API_KEY }}
        EVENT_WHAT: ${{ github.event.pull_request.title || github.event.head_commit.message || github.event.schedule || github.event_name }}
        EVENT_BRANCH: ${{ github.head_ref || github.ref_name }}
        EVENT_BASE: ${{ github.base_ref || format('{0}~', github.ref_name) }}
        EVENT_NAME: ${{ github.event_name }}
        SHA: ${{ github.event.pull_request.head.sha || github.sha }}
        ORACLE_NAME: ${{ matrix.oracle }}
      timeout-minutes: 60
      run: |
        export PATH=$HOME/$PG_INSTALL_DIR/bin:$PATH
        export PGDATABASE=postgres

        rc=0
        claude -p --output-format stream-json --verbose \
          --max-budget-usd 15.00 --dangerously-skip-permissions \
          --model opus[1m] --effort high \
          "$(envsubst < .github/workflows/llm-fuzzer/prompt.txt)" \
          > ${{ runner.temp }}/claude-execution-output.json || rc=$?

        # When it goes over the budget, mostly it terminates gracefully because
        # the agent is aware of the remaining budget, but sometimes there's a
        # forceful termination which we detect here so that it doesn't lead to
        # job failure.
        subtype=$(jq -r 'select(.type == "result") | .subtype // ""' \
          ${{ runner.temp }}/claude-execution-output.json 2>/dev/null | head -1)
        if [ "$subtype" = "error_max_budget_usd" ]; then
          echo "The agent was forcefully terminated because of going over the budget"
          rc=0
        fi

        exit $rc

    - name: Write job summary
      if: always()
      run: |
        jq -r 'select(.type == "result") | "## ${{ matrix.display_name }} Fuzzer\n\n"
          + (.result // "No result produced.")
          + "\n\n**Cost:** $\(.total_cost_usd // 0 | tostring) | **Duration:** \((.duration_ms // 0) / 1000 | tostring)s | **Turns:** \(.num_turns // 0)"' \
          ${{ runner.temp }}/claude-execution-output.json \
          >> "$GITHUB_STEP_SUMMARY" 2>/dev/null || true

        if [ -f ~/llm-fuzzer-repro.sql ]; then
          printf '\n## Reproducer\n\n```sql\n' >> "$GITHUB_STEP_SUMMARY"
          cat ~/llm-fuzzer-repro.sql >> "$GITHUB_STEP_SUMMARY"
          printf '\n```\n' >> "$GITHUB_STEP_SUMMARY"
        fi

    - name: Upload Claude log
      if: always()
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: ${{ matrix.display_name }} Claude Execution Log
        path: ${{ runner.temp }}/claude-execution-output.json
        if-no-files-found: ignore

    - name: Check for reproducer
      id: repro
      run: |
        if [ -f ~/llm-fuzzer-repro.sql ]; then echo "found=true" >> "$GITHUB_OUTPUT"; fi

    - name: Rebuild after fuzzing
      if: steps.repro.outputs.found == 'true'
      run: |
        set -xeu

        # PG can get stuck in background worker termination after an
        # AddressSanitizer failure (upstream bug), so we have to kill it
        # forcefully.
        killall -9 postgres || true

        sleep 2

    - name: Restore clean PostgreSQL build
      if: steps.repro.outputs.found == 'true'
      uses: actions/cache/restore@55cc8345863c7cc4c66a329aec7e433d2d1c52a9 # v6.1.0
      with:
        path: ~/${{ env.PG_SRC_DIR }}
        key: ${{ steps.cache-postgresql.outputs.cache-primary-key }}

    - name: Reinstall PostgreSQL and TimescaleDB
      if: steps.repro.outputs.found == 'true'
      run: |
        set -xeu
        cd ~/$PG_SRC_DIR && make install
        cd $GITHUB_WORKSPACE
        git checkout -f ${{ github.sha }}
        git clean -fd
        make -j$(getconf _NPROCESSORS_ONLN) -C build install

    - name: Run the reproducer
      if: steps.repro.outputs.found == 'true'
      run: |
        set -xeu

        sudo journalctl --rotate --vacuum-time=1s
        sudo rm -f /var/lib/systemd/coredump/*

        export PATH=$HOME/$PG_INSTALL_DIR/bin:$PATH

        export PGDATA=repro_pgdata

        PGPORT=$(( 6600 + $RANDOM % 100 ))
        export PGPORT

        initdb

        cat >> $PGDATA/postgresql.conf <<CONF
        shared_preload_libraries = 'timescaledb'
        statement_timeout = '30s'
        listen_addresses = ''
        logging_collector = on
        log_destination = 'jsonlog,stderr'
        log_directory = '$(readlink -f .)'
        log_filename = 'postmaster.log'
        log_error_verbosity = verbose
        CONF

        pg_ctl start

        export PGDATABASE=postgres
        psql -c "create user repro_user"
        psql -c "create database repro_database owner repro_user"

        export PGDATABASE=repro_database
        export PGUSER=repro_user
        psql -c "create extension timescaledb;"

        printf '\\restrict %s\n' "${RANDOM}" > restricted-repro.sql
        cat ~/llm-fuzzer-repro.sql >> restricted-repro.sql

        PSQLRC=oracle-psqlrc
        export PSQLRC
        echo "
          \set QUIET on
          \set ON_ERROR_STOP on
          set client_min_messages = error;
        " > "${PSQLRC}"

        .github/workflows/llm-fuzzer/oracle/${{ matrix.oracle }}/verify.sh restricted-repro.sql &> repro_result.txt

        psql -c "select 1;"

        pg_ctl stop

    - name: Add repro result to summary
      if: always() && steps.repro.outputs.found == 'true'
      run: |
        if [ -s repro_result.txt ]
        then
            printf '\n## Result\n\n' >> "$GITHUB_STEP_SUMMARY"
            head -100 repro_result.txt >> "$GITHUB_STEP_SUMMARY"
        fi

    # The jq --exit-code option is broken with select() on jq-1.6 which we see
    # on some machines, so we use grep instead (see https://github.com/jqlang/jq/issues/1139).
    - name: Check for internal program errors
      if: always() && steps.repro.outputs.found == 'true'
      run: |
        ! jq 'select(
            (
              .state_code == "XX000" and .func_name != "exec_stmt_raise"
              and .error_severity != "LOG" and (.message | test("TestFailure") | not)
            ) or (
              .message | test("resource was not closed|could not close|leak")
            )
          ) | [.message, .func_name,  .statement] | @tsv
        ' -r postmaster.json | grep .

    - name: Collect the logs
      if: always()
      id: collectlogs
      run: |
        # wait in case there are in-progress coredumps
        sleep 10
        if coredumpctl -q list >/dev/null; then echo "coredumps=true" >>$GITHUB_OUTPUT; fi
        # print OOM killer information
        sudo journalctl --system -q --facility=kern --grep "Killed process" || true

    - name: Stack trace
      if: always() && steps.collectlogs.outputs.coredumps == 'true' && steps.repro.outputs.found == 'true'
      run: |
        sudo coredumpctl gdb <<<"
          set verbose on
          set trace-commands on
          show debug-file-directory
          printf "'"'"query = '%s'\n\n"'"'", debug_query_string
          frame function ExceptionalCondition
          printf "'"'"condition = '%s'\n"'"'", conditionName
          up 1
          l
          info args
          info locals
          bt full
        " 2>&1 | tee stacktrace.log
        pwd
        ls
        ./scripts/bundle_coredumps.sh
        exit 1 # Fail the job if we have core dumps.

    - name: Show sanitizer logs
      if: always()
      run: |
        tail -vn +1 ${{ github.workspace }}/sanitizer_logs/sanitizer* || :

    - name: Upload sanitizer logs
      if: always()
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: ${{ matrix.display_name }} Sanitizer Logs
        # The log_path sanitizer option means "Write logs to 'log_path.pid'".
        # https://github.com/google/sanitizers/wiki/SanitizerCommonFlags
        path: ${{ github.workspace }}/sanitizer_logs/*
        if-no-files-found: ignore

    - name: Upload core dumps
      if: always() && steps.collectlogs.outputs.coredumps == 'true'
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: ${{ matrix.display_name }} Coredumps
        path: coredumps
        if-no-files-found: ignore

    - name: Save PostgreSQL log
      if: always()
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: ${{ matrix.display_name }} PostgreSQL Log
        path: postmaster.*
        if-no-files-found: ignore

    - name: Upload llm-fuzzer-repro.sql
      if: always()
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: ${{ matrix.display_name }} repro.sql
        path: ~/llm-fuzzer-repro.sql
        if-no-files-found: ignore

    - name: Upload reproduction details
      if: always() && steps.repro.outputs.found == 'true'
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: ${{ matrix.display_name }} Reproduction Details
        path: |
          result*
          repro_result.txt
        if-no-files-found: ignore

    - name: Upload results to the database
      if: always() && steps.repro.outputs.found == 'true'
      env:
        JOB_NAME: LLM fuzzer PG${{ needs.config.outputs.pg_latest }}
        CI_STATS_DB: ${{ secrets.CI_STATS_DB }}
        GITHUB_EVENT_NAME: ${{ github.event_name }}
        GITHUB_REF_NAME: ${{ github.ref_name }}
        GITHUB_REPOSITORY: ${{ github.repository }}
        GITHUB_RUN_ATTEMPT: ${{ github.run_attempt }}
        GITHUB_RUN_ID: ${{ github.run_id }}
        GITHUB_RUN_NUMBER: ${{ github.run_number }}
        JOB_STATUS: ${{ job.status }}
      run: |
        if [[ "${{ github.event_name }}" == "pull_request" ]] ;
        then
            GITHUB_PR_NUMBER="${{ github.event.number }}"
        else
            GITHUB_PR_NUMBER=0
        fi
        export GITHUB_PR_NUMBER
        scripts/upload_ci_stats.sh