run-python-tests-pg.yml

name: Run Python tests on PostgreSQL

# Note: we use a custom port (and installation directory on Windows) for
# PostgreSQL as the runners may already have a version installed.

on:
  push:
    branches: [ "master" ]
    paths:
      - 'web/**'
      # Not the message catalogues: a .po change cannot break a build or a
      # test, and check-translations already compiles them, which is the
      # check that would catch a malformed one.
      - '!web/pgadmin/translations/**'
      - 'requirements.txt'
      - 'Makefile'
      - 'tools/setup-python-env.sh'
      - 'tools/get_sb_package.py'
      - '.github/workflows/run-python-tests-pg.yml'
  pull_request:
    branches: [ "master" ]
    paths:
      - 'web/**'
      # Not the message catalogues: a .po change cannot break a build or a
      # test, and check-translations already compiles them, which is the
      # check that would catch a malformed one.
      - '!web/pgadmin/translations/**'
      - 'requirements.txt'
      - 'Makefile'
      - 'tools/setup-python-env.sh'
      - 'tools/get_sb_package.py'
      - '.github/workflows/run-python-tests-pg.yml'

  workflow_dispatch:
    inputs:
      pg_timetable_version:
        description: 'pg_timetable version to install for the tests'
        required: false
        type: string

concurrency:
  group: '${{ github.workflow }} @ ${{ github.event.pull_request.head.label || github.head_ref || github.ref }}'
  cancel-in-progress: true

jobs:
  run-python-tests-pg:
    env:
      PG_TIMETABLE_VERSION: ${{ inputs.pg_timetable_version || '6.3.0' }}
    strategy:
      fail-fast: false
      matrix:
        os: [macos-latest, ubuntu-22.04, windows-latest]
        pgver: [14, 15, 16, 17, 18]

    runs-on: ${{ matrix.os }}

    steps:
      - uses: actions/checkout@v7

      - name: Update python version
        uses: actions/setup-python@v7
        with:
          python-version: '3.10'

      - name: Setup the PGDG APT repo on Linux
        if: ${{ matrix.os == 'ubuntu-22.04' }}
        run: |
          sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

      - name: Uninstall PostgreSQL if already present on linux
        if: ${{ matrix.os == 'ubuntu-22.04' }}
        run: |
          if [ -n "$(ls /etc/postgresql/*/*/postgresql.conf 2>/dev/null)" ]; then
            # Extract the major version from pg_config
            installed_pg_version=$( pg_config --version | cut -d ' ' -f 2 | cut -d '.' -f 1 )
            echo "Installed PostgreSQL version: $installed_pg_version"
            if [ $installed_pg_version != ${{ matrix.pgver }} ]; then
              sudo pg_dropcluster $installed_pg_version main --stop
              sudo apt-get -y remove "postgresql-${installed_pg_version}"
            fi
          fi

      - name: Install platform dependencies on Linux
        if: ${{ matrix.os == 'ubuntu-22.04' }}
        run: |
          sudo apt update
          sudo apt install -y libpq-dev libffi-dev libssl-dev libkrb5-dev zlib1g-dev postgresql-${{ matrix.pgver }} postgresql-${{ matrix.pgver }}-pldebugger pgagent postgresql-${{ matrix.pgver }}-postgis-3

      - name: Install platform dependencies on macOS
        if: ${{ matrix.os == 'macos-latest' }}
        run: |
          brew install postgresql@${{ matrix.pgver }}
          echo "/opt/homebrew/opt/postgresql@${{ matrix.pgver }}/bin" >> $GITHUB_PATH


      - name: Uninstall PostgreSQL if already present on windows
        if: ${{ matrix.os == 'windows-latest' }}
        run: |
          if exist "C:\Program Files\PostgreSQL\{{ matrix.pgver }}\uninstall-postgresql.exe" (
              "C:\Program Files\PostgreSQL\{{ matrix.pgver }}\uninstall-postgresql.exe" --mode unattended
          )
          reg delete "HKLM\SOFTWARE\PostgreSQL" /f
        shell: cmd

      - name: Install platform dependencies on Windows
        if: ${{ matrix.os == 'windows-latest' }}
        run: |
          FOR /f "delims=" %%F IN ('python tools\get_sb_package.py "https://www.postgresql.org/applications-v2.xml" "postgresql_${{ matrix.pgver }}" "windows-x64"') DO SET INSTALLER_EXE=%%F
          ECHO Running %INSTALLER_EXE%...
          %INSTALLER_EXE% --prefix C:\PostgreSQL_custom\${{ matrix.pgver }} --datadir C:\PostgreSQL_custom\${{ matrix.pgver }}\data --serverport 59${{ matrix.pgver }} --superpassword postgres --install_runtimes 0 --mode unattended --unattendedmodeui none --disable-components pgAdmin,stackbuilder --enable-components server,commandlinetools"
          choco install -y mitkerberos

          REM Ignore error 3010 (reboot required)
          IF %ERRORLEVEL% EQU 3010 cmd /c "exit /b 0"
        shell: cmd

      - name: Add pg_config path to PATH
        shell: pwsh
        run: |
          $pgPath = "C:\\PostgreSQL_custom\\${{ matrix.pgver }}\\bin"
          Write-Output $pgPath
          Add-Content -Path $env:GITHUB_PATH -Value $pgPath
          pg_config --version

      - name: Create the tablespace directory on Linux
        if: ${{ matrix.os == 'ubuntu-22.04' }}
        run: |
          sudo mkdir -p /var/lib/postgresql/tablespaces/${{ matrix.pgver }}
          sudo chown postgres:postgres /var/lib/postgresql/tablespaces/${{ matrix.pgver }}

      - name: Create the tablespace directory on macOS
        if: ${{ matrix.os == 'macos-latest' }}
        run: |
          mkdir -p /opt/homebrew/var/tablespaces/${{ matrix.pgver }}

      - name: Create the tablespace directory on Windows
        if: ${{ matrix.os == 'windows-latest' }}
        run: |
          mkdir "C:\PostgreSQL\${{ matrix.pgver }}\tablespaces"
          icacls "C:\PostgreSQL\${{ matrix.pgver }}\tablespaces" /grant "NETWORK SERVICE":(OI)(CI)F /T
        shell: cmd

      - name: Start PostgreSQL on Linux
        if: ${{ matrix.os == 'ubuntu-22.04' }}
        run: |
          sudo su -c "echo local all all trust > /etc/postgresql/${{ matrix.pgver }}/main/pg_hba.conf"
          sudo sed -i "s/port = 543[0-9]/port = 59${{ matrix.pgver }}/g" /etc/postgresql/${{ matrix.pgver }}/main/postgresql.conf
          sudo sed -i "s/#shared_preload_libraries = ''/shared_preload_libraries = '\$libdir\/plugin_debugger'/g" /etc/postgresql/${{ matrix.pgver }}/main/postgresql.conf
          sudo su - postgres -c "/usr/lib/postgresql/${{ matrix.pgver }}/bin/postgres -D /var/lib/postgresql/${{ matrix.pgver }}/main -c config_file=/etc/postgresql/${{ matrix.pgver }}/main/postgresql.conf &"

          until sudo runuser -l postgres -c "pg_isready -p 59${{ matrix.pgver }}" 2>/dev/null; do
            >&2 echo "Postgres is unavailable - sleeping for 2 seconds"
            sleep 2
          done

          psql -U postgres -p 59${{ matrix.pgver }} -c 'CREATE EXTENSION pgagent;'
          psql -U postgres -p 59${{ matrix.pgver }} -c 'CREATE EXTENSION pldbgapi;'
          psql -U postgres -p 59${{ matrix.pgver }} -c 'CREATE EXTENSION postgis;'

      - name: Start PostgreSQL on macOS
        if: ${{ matrix.os == 'macos-latest' }}
        run: |
          echo local all all trust > /opt/homebrew/var/postgresql@${{ matrix.pgver }}/pg_hba.conf
          sed -i '' "s/#port = 543[0-9]/port = 59${{ matrix.pgver }}/g" /opt/homebrew/var/postgresql@${{ matrix.pgver }}/postgresql.conf
          brew services restart postgresql@${{ matrix.pgver }}

          until /opt/homebrew/opt/postgresql@${{ matrix.pgver }}/bin/pg_isready -p 59${{ matrix.pgver }} 2>/dev/null; do
            >&2 echo "Postgres is unavailable - sleeping for 2 seconds"
            sleep 2
          done

          psql postgres -p 59${{ matrix.pgver }} -c 'CREATE ROLE postgres SUPERUSER LOGIN;'

      # pg_timetable tables are created by the agent and need the agent to test run_now.
      - name: Install and start pg_timetable on Linux
        if: ${{ matrix.os == 'ubuntu-22.04' }}
        run: |
          wget --quiet -O /tmp/pg_timetable.tar.gz https://github.com/cybertec-postgresql/pg_timetable/releases/download/v${{ env.PG_TIMETABLE_VERSION }}/pg_timetable_Linux_x86_64.tar.gz
          tar -xzf /tmp/pg_timetable.tar.gz -C /tmp --strip-components=1
          /tmp/pg_timetable --init --clientname=runner "host=/var/run/postgresql port=59${{ matrix.pgver }} dbname=postgres user=postgres"
          nohup /tmp/pg_timetable --clientname=runner "host=/var/run/postgresql port=59${{ matrix.pgver }} dbname=postgres user=postgres" > /tmp/pg_timetable.log 2>&1 &
          echo $! > /tmp/pg_timetable.pid
          sleep 2

      - name: Install and start pg_timetable on macOS
        if: ${{ matrix.os == 'macos-latest' }}
        run: |
          curl -sSL -o /tmp/pg_timetable.tar.gz https://github.com/cybertec-postgresql/pg_timetable/releases/download/v${{ env.PG_TIMETABLE_VERSION }}/pg_timetable_Darwin_arm64.tar.gz
          tar -xzf /tmp/pg_timetable.tar.gz -C /tmp --strip-components=1
          /tmp/pg_timetable --init --clientname=runner "host=/tmp port=59${{ matrix.pgver }} dbname=postgres user=postgres"
          nohup /tmp/pg_timetable --clientname=runner "host=/tmp port=59${{ matrix.pgver }} dbname=postgres user=postgres" > /tmp/pg_timetable.log 2>&1 &
          echo $! > /tmp/pg_timetable.pid
          sleep 2

      - name: Install and start pg_timetable on Windows
        if: ${{ matrix.os == 'windows-latest' }}
        shell: pwsh
        run: |
          $env:PGPASSWORD = 'postgres'
          curl.exe -sSL -o pg_timetable.zip https://github.com/cybertec-postgresql/pg_timetable/releases/download/v${{ env.PG_TIMETABLE_VERSION }}/pg_timetable_Windows_x86_64.zip
          Expand-Archive -Path pg_timetable.zip -DestinationPath .
          & .\pg_timetable_Windows_x86_64\pg_timetable.exe --init --clientname=runner "postgresql://postgres:postgres@127.0.0.1:59${{ matrix.pgver }}/postgres"
          Start-Process -FilePath .\pg_timetable_Windows_x86_64\pg_timetable.exe -ArgumentList @('--clientname=runner', "postgresql://postgres:postgres@127.0.0.1:59${{ matrix.pgver }}/postgres") -RedirectStandardError .\pg_timetable_Windows_x86_64\pg_timetable.err -NoNewWindow
          Start-Sleep -Seconds 3

      - name: Install Python dependencies on Linux and macOS
        if: ${{ matrix.os == 'macos-latest' || matrix.os == 'ubuntu-22.04' }}
        run: make install-python-testing

      - name: Install Python dependencies on Windows
        if: ${{ matrix.os == 'windows-latest' }}
        run: |
          SET LIB=C:\PostgreSQL\${{ matrix.pgver }}\lib;%LIB%
          SET INCLUDE=C:\PostgreSQL\${{ matrix.pgver }}\include;%INCLUDE%
          python -m venv venv
          call venv\Scripts\activate.bat
          python -m pip install --upgrade pip
          pip install wheel sphinx sphinxcontrib-youtube -r web\regression\requirements.txt
        shell: cmd

      - name: Create the test configuration on Linux and macOS
        if: ${{ matrix.os == 'macos-latest' || matrix.os == 'ubuntu-22.04' }}
        run: |
          cat <<EOF > web/config_local.py
          from config import *

          # Debug mode
          DEBUG = True

          # App mode
          SERVER_MODE = False

          # Log
          CONSOLE_LOG_LEVEL = DEBUG
          FILE_LOG_LEVEL = DEBUG

          DEFAULT_SERVER = '127.0.0.1'

          UPGRADE_CHECK_ENABLED = False

          LOG_FILE = "$(pwd)/var/pgadmin4.log"
          SESSION_DB_PATH = "$(pwd)/var/sessions"
          STORAGE_DIR = "$(pwd)/var/storage"
          SQLITE_PATH = "$(pwd)/var/pgadmin4.db"
          TEST_SQLITE_PATH = "$(pwd)/var/pgadmin4.db"
          AZURE_CREDENTIAL_CACHE_DIR = "$(pwd)/var/azurecredentialcache"
          EOF

          cat <<EOF > web/regression/test_config.json
          {
            "pgAdmin4_login_credentials": {
              "new_password": "NEWPASSWORD",
              "login_password": "PASSWORD",
              "login_username": "USER@EXAMPLE.COM"
            },
            "pgAdmin4_test_user_credentials": {
              "new_password": "NEWPASSWORD",
              "login_password": "PASSWORD",
              "login_username": "USER2@EXAMPLE.COM"
            },
            "pgAdmin4_test_non_admin_credentials": {
              "new_password": "NEWPASSWORD",
              "login_password": "PASSWORD",
              "login_username": "USER@EXAMPLE.COM"
            },
            "server_group": 1,
            "server_credentials": [
              {
                "name": "PostgreSQL ${{ matrix.pgver }}",
                "comment": "PostgreSQL ${{ matrix.pgver }} Server",
                "db_username": "postgres",
                "host": "${{ matrix.os == 'macos-latest' && '/tmp' || matrix.os == 'ubuntu-22.04' && '/var/run/postgresql' || '127.0.0.1' }}",
                "db_password": "postgres",
                "db_port": 59${{ matrix.pgver }},
                "maintenance_db": "postgres",
                "sslmode": "prefer",
                "tablespace_path": "${{ matrix.os == 'macos-latest' && format('/opt/homebrew/var/tablespaces/{0}', matrix.pgver) || format('/var/lib/postgresql/tablespaces/{0}', matrix.pgver) }}",
                "enabled": true,
                "default_binary_paths": {
                  "pg": "${{ matrix.os == 'macos-latest' && format('/opt/homebrew/opt/postgresql@{0}/bin', matrix.pgver) || format('/usr/lib/postgresql/{0}/bin', matrix.pgver) }}",
                  "ppas": ""
                }
              }
            ],
            "server_update_data": [
              {
                "comment": "This is test update comment"
              }
            ]
          }
          EOF

      - name: Create the test configuration on Windows
        if: ${{ matrix.os == 'windows-latest' }}
        run: |
          FOR /f "delims=" %%D IN ('python -c "import os; print(os.getcwd().replace('\\', '\\\\'))"') DO SET WORKING_DIR=%%D

          > web\config_local.py (
            @echo.from config import *
            @echo.
            @echo.# Debug mode
            @echo.DEBUG = True
            @echo.
            @echo.# App mode
            @echo.SERVER_MODE = False
            @echo.
            @echo.# Log
            @echo.CONSOLE_LOG_LEVEL = DEBUG
            @echo.FILE_LOG_LEVEL = DEBUG
            @echo.
            @echo.DEFAULT_SERVER = '127.0.0.1'
            @echo.
            @echo.UPGRADE_CHECK_ENABLED = False
            @echo.
            @echo.LOG_FILE = "%WORKING_DIR%\\var\\pgadmin4.log"
            @echo.SESSION_DB_PATH = "%WORKING_DIR%\\var\\sessions"
            @echo.STORAGE_DIR = "%WORKING_DIR%\\var\\storage"
            @echo.SQLITE_PATH = "%WORKING_DIR%\\var\\pgadmin4.db"
            @echo.TEST_SQLITE_PATH = "%WORKING_DIR%\\var\\test_pgadmin4.db"
            @echo.AZURE_CREDENTIAL_CACHE_DIR = "%WORKING_DIR%\\var\\azurecredentialcache"
          )

          > web\regression\test_config.json (
            @echo.{
            @echo.  "pgAdmin4_login_credentials": {
            @echo.    "new_password": "NEWPASSWORD",
            @echo.    "login_password": "PASSWORD",
            @echo.    "login_username": "USER@EXAMPLE.COM"
            @echo.  },
            @echo.  "pgAdmin4_test_user_credentials": {
            @echo.    "new_password": "NEWPASSWORD",
            @echo.    "login_password": "PASSWORD",
            @echo.    "login_username": "USER2@EXAMPLE.COM"
            @echo.  },
            @echo.  "pgAdmin4_test_non_admin_credentials": {
            @echo.    "new_password": "NEWPASSWORD",
            @echo.    "login_password": "PASSWORD",
            @echo.    "login_username": "USER@EXAMPLE.COM"
            @echo.  },
            @echo.  "server_group": 1,
            @echo.  "server_credentials": [
            @echo.    {
            @echo.      "name": "PostgreSQL ${{ matrix.pgver }}",
            @echo.      "comment": "PostgreSQL ${{ matrix.pgver }} Server",
            @echo.      "db_username": "postgres",
            @echo.      "host": "127.0.0.1",
            @echo.      "db_password": "postgres",
            @echo.      "db_port": 59${{ matrix.pgver }},
            @echo.      "maintenance_db": "postgres",
            @echo.      "sslmode": "prefer",
            @echo.      "tablespace_path": "C:\\PostgreSQL\\${{ matrix.pgver }}\\tablespaces",
            @echo.      "enabled": true,
            @echo.      "default_binary_paths": {
            @echo.        "pg": "C:\\PostgreSQL\\${{ matrix.pgver }}\\bin",
            @echo.        "ppas": ""
            @echo.      }
            @echo.    }
            @echo.  ],
            @echo.  "server_update_data": [
            @echo.    {
            @echo.      "comment": "This is test update comment"
            @echo.    }
            @echo.  ]
            @echo.}
          )
        shell: cmd

      - name: Run the tests on Linux and macOS
        if: ${{ matrix.os == 'macos-latest' || matrix.os == 'ubuntu-22.04' }}
        run: |
          . venv/bin/activate
          make check-python

      - name: Run the tests on Windows
        if: ${{ matrix.os == 'windows-latest' }}
        run: |
          call venv\Scripts\activate.bat
          python web\regression\runtests.py --exclude feature_tests
        shell: cmd

      - name: Stop pg_timetable on Linux and macOS
        if: always() && (matrix.os == 'macos-latest' || matrix.os == 'ubuntu-22.04')
        run: |
          if [ -f /tmp/pg_timetable.pid ]; then
            kill $(cat /tmp/pg_timetable.pid) 2>/dev/null || true
          fi

      - name: Stop pg_timetable on Windows
        if: always() && matrix.os == 'windows-latest'
        shell: pwsh
        run: |
          Get-Process -Name pg_timetable -ErrorAction SilentlyContinue | Stop-Process -Force

      - name: Archive server log
        if: success() || failure()
        uses: actions/upload-artifact@v7
        with:
          name: server-log-${{ matrix.os }}-pg${{ matrix.pgver }}
          path: var/pgadmin4.log

      - name: Archive regression log
        if: success() || failure()
        uses: actions/upload-artifact@v7
        with:
          name: regression-log-${{ matrix.os }}-pg${{ matrix.pgver }}
          path: web/regression/regression.log