action.yml

name: Setup PostgreSQL and PostGIS for Linux/macOS/Windows
author: Yuri Astrakhan
description: Setup PostgreSQL server with PostGIS and configurable additional extensions.
branding:
  icon: database
  color: purple
inputs:
  username:
    description: The username of the user to setup.
    default: postgres
    required: false
  password:
    description: The password of the user to setup.
    default: postgres
    required: false
  database:
    description: The database name to setup and grant permissions to created user.
    default: postgres
    required: false
  port:
    description: The server port to listen on.
    default: "5432"
    required: false
  postgres-version:
    description: The version of PostgreSQL to install.
    default: "17"
    required: false
  postgis_version:
    description: The version of PostGIS to install.
    default: ""
    required: false
  cached-dir:
    description: Where should the temporary downloads be placed. Used to download and cache PostGIS binary.
    default: "${{ runner.temp }}/setup-postgis-downloads"
    required: false
  import-schema:
    description: Import database schema.
    default: "false"
    required: false
  schema-file:
    description: Path to the SQL schema file to import (relative to workspace or absolute path).
    default: ""
    required: false
  setup-odbc:
    description: Configure ODBC drivers.
    default: "false"
    required: false
  search-path:
    description: Default schema search path for database and user.
    default: ""
    required: false
  additional-extensions:
    description: Comma-separated list of additional PostgreSQL extensions to install (beyond PostGIS).
    default: ""
    required: false
outputs:
  connection-uri:
    description: The connection URI to connect to PostgreSQL.
    value: ${{ steps.pg.outputs.connection-uri }}
  service-name:
    description: The service name with connection parameters.
    value: ${{ steps.pg.outputs.service-name }}
  npgsql-connection-string:
    description: Npgsql connection string with search path.
    value: "Host=localhost;Port=${{ inputs.port }};Database=${{ inputs.database }};Username=${{ inputs.username }};Password=${{ inputs.password }};Search Path=${{ inputs.search-path }};"
  odbc-connection-string:
    description: ODBC connection string.
    value: "Driver={PostgreSQL ANSI};Servername=localhost;Port=${{ inputs.port }};Database=${{ inputs.database }};Username=${{ inputs.username }};Password=${{ inputs.password }};"
runs:
  using: composite
  steps:
    - name: Preflight checks
      shell: bash
      id: preflight
      env:
        INPUT_PORT: ${{ inputs.port }}
        INPUT_POSTGRES_VERSION: ${{ inputs.postgres-version }}
      run: |
        PORT="$INPUT_PORT"
        if [[ "$(uname -s)" == "Linux" && "$(uname -m)" == "aarch64" && "$PORT" == '5432' ]]; then
          echo "::notice::Detected Linux ARM64 runner — default PostgreSQL port 5432 may conflict with system service. Using a custom port (34837)."
          PORT=34837
        fi
        echo "port=$PORT" >> "$GITHUB_OUTPUT"
    
        PG_VERSION="$INPUT_POSTGRES_VERSION"
        if [[ "$RUNNER_OS" == "macOS" && "$PG_VERSION" != "17" ]]; then
          echo "::notice::On macOS, only postgres-version '17' is supported due to PostGIS Homebrew limitations. Auto-assigning version 17"
          PG_VERSION=17
        fi
        echo "postgres-version=$PG_VERSION" >> "$GITHUB_OUTPUT"  
        
    - name: Setup PostgreSQL for Linux/macOS/Windows
      id: pg
      uses: ikalnytskyi/action-setup-postgres@c4dda34aae1c821e3a771b68b73b13af3198a7ee # v8
      with:
        username: "${{ inputs.username }}"
        password: "${{ inputs.password }}"
        database: "${{ inputs.database }}"
        port: "${{ steps.preflight.outputs.port }}"
        postgres-version: "${{ steps.preflight.outputs.postgres-version }}"
        
    - name: Install PostGIS (Linux)
      if: runner.os == 'Linux'
      run: |
        # Detect installed PostgreSQL version (major only)
        PG_VERSION=$(postgres --version | awk '{print $3}' | cut -d. -f1)
        [[ "$PG_VERSION" =~ ^[0-9]+$ ]] || (echo "Failed to detect Postgres major version" && exit 1)
        echo "Detected PostgreSQL major version: $PG_VERSION"
        
        # Install PostGIS
        REPO_URL="https://apt.postgresql.org/pub/repos/apt/"
        wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg >/dev/null
        echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] $REPO_URL $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list >/dev/null
        sudo apt update
        sudo apt-get install postgresql-$PG_VERSION-postgis-3
      shell: bash

    - name: Install PostGIS on macOS
      if: runner.os == 'macOS'
      shell: bash
      run: |
        brew install postgis
        
    - name: Detect PostgreSQL Version (Windows)
      if: runner.os == 'Windows'
      id: pg-version
      shell: pwsh
      run: |
        echo "PowerShell version: ${PSVersionTable.PSVersion}"
        # Get major version number (first 2 digits) from PostgreSQL server version number (format: XXYYZZ)
        $PG_VERSION = psql -t -A -c "SHOW server_version_num" '${{ steps.pg.outputs.connection-uri }}' | ForEach-Object { $_.Substring(0,2) }
        echo "Using PostgreSQL version: $PG_VERSION"
        Add-Content $env:GITHUB_OUTPUT "version=$PG_VERSION"

    - name: Detect PGDATA directory (Windows)
      if: runner.os == 'Windows'
      id: pgdata
      shell: pwsh
      run: |
        $PGDATA = psql -t -A -c "SHOW data_directory;" '${{ steps.pg.outputs.connection-uri }}'
        echo "PGDATA directory: $PGDATA"
        echo "PGDATA=$PGDATA" >> $env:GITHUB_ENV

    - name: Decide Postgis version (Windows)
      if: runner.os == 'Windows'
      id: postgis-ver
      shell: pwsh
      run: |
        $PG_VERSION = '${{ steps.pg-version.outputs.version }}'
        
        if ("${{ inputs.postgis_version }}" -ne "") {
          $pgis_bundle = "postgis-bundle-pg$PG_VERSION-${{ inputs.postgis_version }}x64"
          $pgis_bundle_url_main = "https://download.osgeo.org/postgis/windows/pg$PG_VERSION/$pgis_bundle.zip"
          $pgis_bundle_url_archive = "https://download.osgeo.org/postgis/windows/pg$PG_VERSION/archive/$pgis_bundle.zip"
          $pgis_bundle_url = $null
          $urls = @($pgis_bundle_url_main, $pgis_bundle_url_archive)
          foreach ($url in $urls) {
            try {
              Invoke-WebRequest -Uri $url -Method Head -ErrorAction Stop
              $pgis_bundle_url = $url
              break
            } catch {
              continue
            }
          }
          if ($pgis_bundle_url -eq $null) {
            Write-Error "Could not find PostGIS bundle at either $pgis_bundle_url_main or $pgis_bundle_url_archive"
            exit 1
          }
        } else {
          $postgis_page = "https://download.osgeo.org/postgis/windows/pg$PG_VERSION"
          echo "Detecting PostGIS version from $postgis_page for PostgreSQL $PG_VERSION"
          $pgis_bundle = (Invoke-WebRequest -Uri $postgis_page -ErrorAction Stop).Links.Where({$_.href -match "^postgis.*zip$"}).href
          if (!$pgis_bundle) {
            Write-Error "Could not find latest PostGIS version in $postgis_page that would match  ^postgis.*zip$  pattern"
            exit 1
          }
          $pgis_bundle = [IO.Path]::ChangeExtension($pgis_bundle, [NullString]::Value)
          $pgis_bundle_url = "$postgis_page/$pgis_bundle.zip"
        }

        Add-Content $env:GITHUB_OUTPUT "pgis_bundle=$pgis_bundle"
        Add-Content $env:GITHUB_OUTPUT "pgis_bundle_url=$pgis_bundle_url"

    - name: Cache Postgis Download (Windows)
      if: runner.os == 'Windows'
      uses: actions/cache@cdf6c1fa76f9f475f3d7449005a359c84ca0f306 # v5.0.3
      id: cache-downloads
      with:
        path: ${{ inputs.cached-dir }}
        key: ${{ runner.os }}-${{ steps.postgis-ver.outputs.pgis_bundle_url }}

    - name: Download Postgis (Windows)
      if: runner.os == 'Windows' && steps.postgis-ver.outputs.cache-hit != 'true'
      shell: pwsh
      # Download Postgis bundle if not in cache
      env:
        PGIS_BUNDLE: ${{ steps.postgis-ver.outputs.pgis_bundle }}
        PGIS_BUNDLE_URL: ${{ steps.postgis-ver.outputs.pgis_bundle_url }}
      run: |
        echo "Downloading $env:PGIS_BUNDLE from $env:PGIS_BUNDLE_URL"
        $postgis_zip = "postgis.zip"
        Invoke-WebRequest $env:PGIS_BUNDLE_URL -OutFile $postgis_zip -ErrorAction Stop

        echo "Extracting ${{ inputs.cached-dir }}\$env:PGIS_BUNDLE\*"
        Remove-Item ${{ inputs.cached-dir }} -Recurse -Force -ErrorAction Ignore
        echo "Expanded $((Expand-Archive $postgis_zip -DestinationPath ${{ inputs.cached-dir }} -PassThru).count) files from $postgis_zip"
        echo "Moved $((Move-Item -Path "${{ inputs.cached-dir }}\$env:PGIS_BUNDLE\*" -Destination ${{ inputs.cached-dir }} -Force -PassThru).count) files to ${{ inputs.cached-dir }}"
        Remove-Item $postgis_zip
        if (!(Test-Path "${{ inputs.cached-dir }}\*")) {
          Write-Error "Could not find PostGIS files in ${{ inputs.cached-dir }}"
          exit 1
        }

    - name: Install Postgis (Windows)
      if: runner.os == 'Windows'
      shell: pwsh
      env:
        PGDATA: ${{ env.PGDATA }}
      run: |
        if (!(Test-Path "${{ inputs.cached-dir }}\*")) {
          Write-Error "Could not find PostGIS files in ${{ inputs.cached-dir }}"
          exit 1
        }
        
        # Set PG_ROOTDIR environment variable
        echo "PG_ROOTDIR=$env:PROGRAMFILES\PostgreSQL\${{ steps.pg-version.outputs.version }}" >> $env:GITHUB_ENV
        $env:PGROOT = "$env:PROGRAMFILES\PostgreSQL\${{ steps.pg-version.outputs.version }}"
        
        # Copy PostGIS files to PostgreSQL directory
        echo "Copying PostGIS files to $env:PGROOT"
        Copy-Item -Path "${{ inputs.cached-dir }}\*" -Destination $env:PGROOT -Force -Recurse -ErrorAction Continue
        
    - name: Enable PostGIS extension
      run: psql -v ON_ERROR_STOP=1 -c 'CREATE EXTENSION IF NOT EXISTS postgis;' '${{ steps.pg.outputs.connection-uri }}'
      shell: bash
    
    - name: Install additional PostgreSQL extensions
      if: inputs.additional-extensions != ''
      run: |
        # Convert comma-separated list to array and install each extension
        IFS=',' read -ra EXTENSIONS <<< "${{ inputs.additional-extensions }}"
        for ext in "${EXTENSIONS[@]}"; do
          # Trim whitespace
          ext=$(echo "$ext" | xargs)
          if [ -n "$ext" ]; then
            echo "Installing extension: $ext"
            psql -v ON_ERROR_STOP=1 -c "CREATE EXTENSION IF NOT EXISTS $ext;" '${{ steps.pg.outputs.connection-uri }}'
          fi
        done
      shell: bash

    - name: Configure search path for database and user
      if: ${{ inputs.search-path != '' }}
      run: |
        # Set default schema search_path for the database
        psql -v ON_ERROR_STOP=1 -c "ALTER DATABASE \"${{ inputs.database }}\" SET search_path TO ${{ inputs.search-path }};" '${{ steps.pg.outputs.connection-uri }}'
        # Set default schema search_path for the user
        psql -v ON_ERROR_STOP=1 -c "ALTER ROLE \"${{ inputs.username }}\" SET search_path TO ${{ inputs.search-path }};" '${{ steps.pg.outputs.connection-uri }}'
      shell: bash

    - name: Setup ODBC drivers (Linux)
      if: runner.os == 'Linux' && inputs.setup-odbc == 'true'
      run: |
        echo "Configuring PostgreSQL ODBC driver..."
        sudo apt-get update
        sudo apt-get install -y odbc-postgresql unixodbc unixodbc-dev
        sudo odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini || true
        
        # Verify ODBC driver installation
        if odbcinst -q -d -n "PostgreSQL ANSI"; then
          echo "PostgreSQL ODBC driver 'PostgreSQL ANSI' is registered"
        else
          echo "Warning: PostgreSQL ODBC driver 'PostgreSQL ANSI' not found, checking alternatives..."
          odbcinst -q -d || true
        fi
      shell: bash

    - name: Setup ODBC drivers (Windows)
      if: runner.os == 'Windows' && inputs.setup-odbc == 'true'
      shell: pwsh
      run: |
        Write-Host "Configuring PostgreSQL ODBC driver on Windows..."
        
        $pgVersion = "${{ inputs.postgres-version }}"
        $cacheDir = "${{ inputs.cached-dir }}"
        Write-Host "PostgreSQL version: $pgVersion"
        Write-Host "Cache directory: $cacheDir"
        
        # Ensure cache directory exists
        if (!(Test-Path $cacheDir)) {
          New-Item -ItemType Directory -Path $cacheDir -Force | Out-Null
        }
        
        # Download and install PostgreSQL ODBC driver matching PostgreSQL version
        $odbcUrl = "https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_$($pgVersion)_00_0000-x64.msi"
        $odbcInstaller = Join-Path $cacheDir "psqlodbc_$($pgVersion)_x64.msi"
        
        Write-Host "ODBC driver URL: $odbcUrl"
        Write-Host "ODBC installer path: $odbcInstaller"
        
        try {
          # Check if ODBC installer is already cached
          if (Test-Path $odbcInstaller) {
            Write-Host "Using cached PostgreSQL ODBC driver: $odbcInstaller"
          } else {
            Write-Host "Downloading PostgreSQL ODBC driver..."
            try {
              # Try version-specific ODBC driver first
              Invoke-WebRequest -Uri $odbcUrl -OutFile $odbcInstaller -ErrorAction Stop
              Write-Host "Downloaded version-specific ODBC driver"
            } catch {
              Write-Warning "Version-specific ODBC driver not found: $odbcUrl"
              # Fallback to latest stable version (16.x) with different filename
              $fallbackUrl = "https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_16_00_0000-x64.msi"
              $fallbackInstaller = Join-Path $cacheDir "psqlodbc_16_x64_fallback.msi"
              Write-Host "Falling back to: $fallbackUrl"
              
              if (Test-Path $fallbackInstaller) {
                Write-Host "Using cached fallback ODBC driver: $fallbackInstaller"
                $odbcInstaller = $fallbackInstaller
              } else {
                Invoke-WebRequest -Uri $fallbackUrl -OutFile $fallbackInstaller -ErrorAction Stop
                $odbcInstaller = $fallbackInstaller
                Write-Host "Downloaded fallback ODBC driver"
              }
            }
          }
          
          Write-Host "Installing PostgreSQL ODBC driver..."
          Start-Process -FilePath "msiexec.exe" -ArgumentList "/i", $odbcInstaller, "/quiet", "/norestart" -Wait -NoNewWindow
          
          Write-Host "PostgreSQL ODBC driver installation completed"
          
          # Verify installation by checking registry
          $odbcDrivers = Get-ItemProperty "HKLM:\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers" -ErrorAction SilentlyContinue
          if ($odbcDrivers -and ($odbcDrivers.PSObject.Properties.Name -contains "PostgreSQL ANSI")) {
            Write-Host "PostgreSQL ODBC driver 'PostgreSQL ANSI' is registered"
          } else {
            Write-Warning "PostgreSQL ODBC driver may not be properly registered"
            # List available drivers for debugging
            Write-Host "Available ODBC drivers:"
            Get-OdbcDriver | Where-Object {$_.Name -like "*PostgreSQL*"} | Format-Table Name, Platform -AutoSize
          }
          
        } catch {
          Write-Warning "Failed to install PostgreSQL ODBC driver: $($_.Exception.Message)"
          Write-Host "Continuing without ODBC driver installation..."
        }
        
        # Note: ODBC installer files are kept in cache directory for future runs

    - name: Setup ODBC drivers (macOS)
      if: runner.os == 'macOS' && inputs.setup-odbc == 'true'
      run: |
        echo "Configuring PostgreSQL ODBC driver on macOS..."
        
        # Install unixODBC and PostgreSQL ODBC driver via Homebrew
        brew install unixodbc psqlodbc
        
        # Verify ODBC driver installation
        if odbcinst -q -d -n "PostgreSQL ANSI" 2>/dev/null; then
          echo "PostgreSQL ODBC driver 'PostgreSQL ANSI' is registered"
        else
          echo "Warning: PostgreSQL ODBC driver 'PostgreSQL ANSI' not found, checking alternatives..."
          odbcinst -q -d || true
        fi
      shell: bash

    - name: Import database schema
      if: inputs.import-schema == 'true' && inputs.schema-file != ''
      run: |
        SCHEMA_FILE="${{ inputs.schema-file }}"
        CACHE_DIR="${{ inputs.cached-dir }}"
        
        echo "Schema file specified: $SCHEMA_FILE"
        echo "Cache directory: $CACHE_DIR"
        
        # Ensure cache directory exists
        mkdir -p "$CACHE_DIR"
        
        # Determine the final schema file path
        FINAL_SCHEMA_FILE=""
        
        # Check if it's an absolute path
        if [[ "$SCHEMA_FILE" = /* ]]; then
          FINAL_SCHEMA_FILE="$SCHEMA_FILE"
        else
          # Try relative to workspace first
          if [ -f "$SCHEMA_FILE" ]; then
            FINAL_SCHEMA_FILE="$SCHEMA_FILE"
          # Try in cache directory
          elif [ -f "$CACHE_DIR/$SCHEMA_FILE" ]; then
            FINAL_SCHEMA_FILE="$CACHE_DIR/$SCHEMA_FILE"
            echo "Using cached schema file: $FINAL_SCHEMA_FILE"
          else
            echo "Error: Schema file not found: $SCHEMA_FILE"
            echo "Searched in:"
            echo "  - $SCHEMA_FILE (relative to workspace)"
            echo "  - $CACHE_DIR/$SCHEMA_FILE (cached)"
            echo ""
            echo "Please provide either:"
            echo "  - An absolute path to the schema file"
            echo "  - A relative path from the workspace root"
            echo "  - Place the file in the cache directory: $CACHE_DIR"
            exit 1
          fi
        fi
        
        if [ ! -f "$FINAL_SCHEMA_FILE" ]; then
          echo "Error: Schema file does not exist: $FINAL_SCHEMA_FILE"
          exit 1
        fi
        
        echo "Importing database schema from: $FINAL_SCHEMA_FILE"
        echo "File size: $(du -h "$FINAL_SCHEMA_FILE" | cut -f1)"
        
        psql -v ON_ERROR_STOP=1 -f "$FINAL_SCHEMA_FILE" '${{ steps.pg.outputs.connection-uri }}'
        echo "Database schema import completed successfully"
      shell: bash