action.yml

name: Setup PostgreSQL for Linux/macOS/Windows
author: Ihor Kalnytskyi
description: Setup a preinstalled PostgreSQL server.
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 PostgreSQL major version to install. Either "14", "15", "16" or "17".
    default: "17"
  ssl:
    description: When "true", encrypt connections using SSL (TLS).
    default: "false"
    required: false
outputs:
  connection-uri:
    description: The connection URI to connect to PostgreSQL.
    value: ${{ steps.set-outputs.outputs.connection-uri }}
  service-name:
    description: The service name with connection parameters.
    value: ${{ steps.set-outputs.outputs.service-name }}
  certificate-path:
    description: The path to the server certificate if SSL is on.
    value: ${{ steps.set-outputs.outputs.certificate-path }}
runs:
  using: composite
  steps:
    - name: Install PostgreSQL
      run: |
        if [[ ! "${{ inputs.postgres-version }}" =~ ^(14|15|16|17)$ ]]; then
          echo "::error::postgres-version must be one of: 14, 15, 16, 17."
          exit 1
        fi

        if [ "$RUNNER_OS" == "Linux" ]; then
          APT_ENTRY="deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main"
          APT_KEY="https://www.postgresql.org/media/keys/ACCC4CF8.asc"

          echo "$APT_ENTRY" | sudo tee /etc/apt/sources.list.d/pgdg.list
          wget --quiet -O - "$APT_KEY" | sudo apt-key add -
          sudo apt-get update
          sudo apt-get -y install postgresql-${{ inputs.postgres-version }}

          PG_BINDIR=$("/usr/lib/postgresql/${{ inputs.postgres-version }}/bin/pg_config" --bindir)
          echo "$PG_BINDIR" >> $GITHUB_PATH

        elif [ "$RUNNER_OS" == "Windows" ]; then
          # The Windows runner has some PostgreSQL environment variables set
          # that may confuse users since they may be irrelevant to the
          # PostgreSQL server we're using. Since GitHub actions does not
          # support unsetting environment variables, the best we can do is to
          # clear their values in order to indicate they must not be used.
          for name in "PGROOT" "PGDATA" "PGBIN" "PGUSER" "PGPASSWORD"; do
            echo "$name=" >> $GITHUB_ENV
          done

          choco install postgresql${{ inputs.postgres-version }} \
            --params "/Password:${{ inputs.password }}" \
            --ia "--enable-components server,commandlinetools --extract-only 1" \
            --no-progress

          PG_BINDIR=$("$PROGRAMFILES/PostgreSQL/${{ inputs.postgres-version }}/bin/pg_config.exe" --bindir)
          PG_LIBDIR=$("$PROGRAMFILES/PostgreSQL/${{ inputs.postgres-version }}/bin/pg_config.exe" --libdir)

          echo "$PG_BINDIR" >> $GITHUB_PATH
          echo "PQ_LIB_DIR=$PG_LIBDIR" >> $GITHUB_ENV

        elif [ "$RUNNER_OS" == "macOS" ]; then
          # HOMEBREW_GITHUB_ACTIONS is used to skip 'initdb' execution to save
          # some seconds. That invocation is not needed because of initdb
          # invocation below.
          export HOMEBREW_GITHUB_ACTIONS=1
          export HOMEBREW_NO_ENV_HINTS=1
          export HOMEBREW_NO_INSTALLED_DEPENDENTS_CHECK=1
          export HOMEBREW_NO_INSTALL_CLEANUP=1
          export HOMEBREW_NO_INSTALL_UPGRADE=1
          brew install --quiet postgresql@${{ inputs.postgres-version }}

          # Link PostgreSQL binaries from /usr/local/bin in order to make them
          # available globally. The --overwrite option is required since some
          # GitHub runners come with preinstalled PostgreSQL binaries, and we
          # have to link the required version of PostgreSQL. The unlinking step
          # is needed to suppress "Already linked" warning which is propagated
          # back to users.
          brew unlink --quiet postgresql@${{ inputs.postgres-version }}
          brew link --quiet --overwrite postgresql@${{ inputs.postgres-version }}
        fi
      shell: bash

    - name: Setup and start PostgreSQL
      run: |
        PGDATA="$RUNNER_TEMP/pgdata"
        PWFILE="$RUNNER_TEMP/pwfile"

        DEFAULT_ENCODING="UTF-8"
        DEFAULT_LOCALE="en_US.$DEFAULT_ENCODING"

        # Unfortunately, Windows Server 2019 doesn't understand locale
        # specified in the format defined by the POSIX standard, i.e.
        # <language>_<country>.<encoding>. Therefore, we have to convert it
        # into something it can swallow, i.e. <language>-<country>.
        if [[ "$RUNNER_OS" == "Windows" && "$(wmic os get Caption)" == *"2019"* ]]; then
          DEFAULT_LOCALE="${DEFAULT_LOCALE%%.*}"
          DEFAULT_LOCALE="${DEFAULT_LOCALE//_/-}"
        fi

        # Unfortunately 'initdb' could only receive a password via file on disk
        # or prompt to enter on. Prompting is not an option since we're running
        # in non-interactive mode.
        echo '${{ inputs.password }}' > $PWFILE

        # There are couple of reasons why we need to create a new PostgreSQL
        # database cluster. First and foremost, we have to create a superuser
        # with provided credentials. Second, we want the PostgreSQL client
        # applications [1] to be available for execution without
        # run-from-another-user dances. Third, we want to make sure that
        # settings are the same between operating systems and aren't changed by
        # package vendors.
        #
        # [1] https://www.postgresql.org/docs/15/reference-client.html
        initdb \
          --pgdata="$PGDATA" \
          --username="${{ inputs.username }}" \
          --pwfile="$PWFILE" \
          --auth="scram-sha-256" \
          --encoding="$DEFAULT_ENCODING" \
          --locale="$DEFAULT_LOCALE" \
          --no-instructions

        # Do not create unix sockets since they are created by default in the
        # directory we have no permissions to (owned by system postgres user).
        echo "unix_socket_directories = ''" >> "$PGDATA/postgresql.conf"
        echo "port = ${{ inputs.port }}" >> "$PGDATA/postgresql.conf"

        if [ "${{ inputs.ssl }}" = "true" ]; then
          # On Windows, bash runs on top of MSYS2, which automatically converts
          # Unix paths to Windows paths for every argument that appears to be a
          # path. This behavior breaks the openssl invocation because the
          # subject argument is mistakenly converted when it should not be.
          # Therefore, we need to exclude it from the path conversion process
          # by setting the MSYS2_ARG_CONV_EXCL environment variable.
          #
          # https://www.msys2.org/docs/filesystem-paths/#automatic-unix-windows-path-conversion
          export MSYS2_ARG_CONV_EXCL="/CN"
          openssl req -new -x509 -days 365 -nodes -text -subj "/CN=localhost" \
            -out "$PGDATA/server.crt" -keyout "$PGDATA/server.key"
          chmod og-rwx "$PGDATA/server.key" "$PGDATA/server.crt"
          echo "ssl = on" >> "$PGDATA/postgresql.conf"
        fi

        pg_ctl start --pgdata="$PGDATA"

        # Save required connection parameters for created superuser to the
        # connection service file [1]. This allows using these connection
        # parameters by setting 'PGSERVICE' environment variable or by
        # requesting them via connection string.
        #
        # HOST is required for Linux/macOS because these OS-es default to unix
        # sockets but we turned them off.
        #
        # PORT, USER, PASSWORD and DBNAME are required because they could be
        # parametrized via action input parameters.
        #
        # [1] https://www.postgresql.org/docs/15/libpq-pgservice.html
        cat <<EOF > "$PGDATA/pg_service.conf"
        [${{ inputs.username }}]
        host=localhost
        port=${{ inputs.port }}
        user=${{ inputs.username }}
        password=${{ inputs.password }}
        dbname=${{ inputs.database }}
        EOF

        if [ "${{ inputs.ssl }}" = "true" ]; then
          echo "sslmode=verify-ca" >> "$PGDATA/pg_service.conf"
          echo "sslrootcert=$PGDATA/server.crt" >> "$PGDATA/pg_service.conf"
        fi

        echo "PGSERVICEFILE=$PGDATA/pg_service.conf" >> $GITHUB_ENV
      shell: bash

    - name: Setup PostgreSQL database
      run: |
        # The 'postgres' database is a pre-created database meant for use by
        # users, utilities and third party applications. There's no way to
        # parametrize the name, so all we can do is to avoid creating a
        # database if provided name is 'postgres'.
        if [ "${{ inputs.database }}" != "postgres" ]; then
          createdb -O "${{ inputs.username }}" "${{ inputs.database }}"
        fi
      env:
        PGSERVICE: ${{ inputs.username }}
      shell: bash

    - name: Set action outputs
      run: |
        CONNECTION_URI="postgresql://${{ inputs.username }}:${{ inputs.password }}@localhost:${{ inputs.port }}/${{ inputs.database }}"
        CERTIFICATE_PATH="$RUNNER_TEMP/pgdata/server.crt"

        if [ "${{ inputs.ssl }}" = "true" ]; then
          # Although SSLMODE and SSLROOTCERT are specific to libpq options,
          # most third-party drivers also support them. By default libpq
          # prefers SSL but doesn't require it, thus it's important to set
          # these options to ensure SSL is used and the certificate is
          # verified.
          CONNECTION_URI="$CONNECTION_URI?sslmode=verify-ca&sslrootcert=$CERTIFICATE_PATH"
          echo "certificate-path=$CERTIFICATE_PATH" >> $GITHUB_OUTPUT
        fi

        echo "connection-uri=$CONNECTION_URI" >> $GITHUB_OUTPUT
        echo "service-name=${{ inputs.username }}" >> $GITHUB_OUTPUT
      shell: bash
      id: set-outputs