Installing PostgreSQL extensions on GitHub Actions

On GitHub Actions, you can easily start a PostgreSQL service using its service container.

For instance:

    runs-on: ubuntu-latest

        image: postgres
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

The job starts a PostgreSQL service using the postgres Docker image.

If you wish to install additional PostgreSQL extensions, you could build a custom PostgreSQL Docker image with all the extensions installed.

But, there is also another way to install the extensions without going through all the hassles.

The slumbering PostgreSQL in GitHub hosted runners

At the time of writing, the latest version of GitHub Linux hosted runner (Ubuntu 22.04) has a PostgreSQL installed in it, but it is disabled by default:

PostgreSQL 14.7

User: postgres
PostgreSQL service is disabled by default.
Use the following command as a part of your job to start the service: 'sudo systemctl start postgresql.service'

All you have to do is start it according to the instruction.

Install PostgreSQL extensions

You can install PostgreSQL extensions by using apt. For instance, if you are looking to install pg_cron on PostgreSQL 14, run sudo apt install postgres-14-cron.

Once installed, you may have to edit the main PostgreSQL configuration file. For PostgreSQL 14, this file is located at /etc/postgresql/14/main/postgresql.conf.

Append your custom configuration into the file using the tee command:

echo "shared_preload_libraries = 'pg_cron'" | sudo -u postgres tee -a "/etc/postgresql/14/main/postgresql.conf" > /dev/null

Note that our tee command is preceded with sudo -u postgres. The PostgreSQL configuration file is owned by the postgres user, so we will have to impersonate as the user to edit the file.

After editing the configuration file, restart the PostgreSQL service.

And that’s it, you can now use the installed extension in your PostgreSQL database server!

Addon: Executing SQL scripts stored in your repository in GitHub Actions workflow

If your repository contains SQL scripts, you can execute them by using the psql command while impersonating as the postgres user:

sudo -u postgres psql -f your_script.sql

But, you will run into an error:

could not change directory to "/home/runner/work/your-repo/your-repo": Permission denied
psql: error: your_script.sql: No such file or directory

Since psql is executed with the postgres user, it lacks the permission to change its working directory to the repository cloned by the runner user, which is the user that runs our workflow.

Since it could not change its working directory, naturally it could not see your SQL scripts.

I’m not sure why it could not change its working directory to /home/runner/work/x/x. The destination directory already has an execute permission bit set on the “others” permission group".

Regardless, to resolve the issue, we will have to run psql as the current host user, instead of the postgres user.

We create a new database user together with its database in PostgreSQL. The new database user must match the host user:

sudo -u postgres createuser -e -s "$USER" # which should create the "runner" user

Now, you can execute your SQL scripts as the runner user:

psql -f your_script.sql

Since pg_hba.conf specifies that all local connections are authenticated using peer method, as long as our host user is the same as the database user, we can connect to the database server.

For reference, this is the content of pg_hba.conf (after omitting all comments) for the PostgreSQL installed on GitHub hosted runners :

local   all             postgres                                peer
local   all             all                                     peer
host    all             all               scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all               scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

The complete workflow configuration

name: My Job


    runs-on: ubuntu-latest
      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Setup PostgreSQL with extensions
        run: |
          sudo apt update
          sudo apt install -y postgresql-14-cron
          sudo -u postgres tee -a "/etc/postgresql/14/main/postgresql.conf" > /dev/null << EOF
          shared_preload_libraries = 'pg_cron'
          cron.database_name = 'dbname'
          sudo systemctl start postgresql.service

      - name: Prepare database user
        run: |
          sudo -u postgres createuser -e -s "$USER"

      - name: Run script
        run: |
          psql -f your_script.sql