Install and create the postgres pg_trgm extension in docker container

For now, we should add this in our "recommended" db hosting docs: https://farmos.org/hosting/install/#database-server

I added some guidance to our installation doc about the PostgreSQL version and pg_trgm requirements in the 2.x-d10 branch I'm working on over in #3330490: Update Drupal core to 10.x in farmOS → .

I think this will largely be a manual step that site admins need to do. We can't really automate it, because CREATE EXTENSION pg_trgm; must be run by a PostgreSQL user with higher privileges than that Drupal database user should have.

Install the pg_trgm extension in the db container of our docker-compose environments.

This is what we need to figure out... specifically for our development environments.

I think this will largely be a manual step that site admins need to do. We can't really automate it, because CREATE EXTENSION pg_trgm; must be run by a PostgreSQL user with higher privileges than that Drupal database user should have.

https://www.postgresql.org/docs/current/pgtrgm.html

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

Which means that pg_trgm should be installable by the standard Drupal db user without any additional privileges.

As for how pg_trgm could be automatically installed in the docker setup, one solution could be to install the extension in the farmOS docker image entrypoint using drush. It has the advantage that it should automatically work for new as well as existing databases. I solved it this way in my custom farmOS docker image https://github.com/wotnak/farmos-docker/blob/main/docker-entrypoint#L12-L26.

Which means that pg_trgm should be installable by the standard Drupal db user without any additional privileges.

Oh interesting. I was under the impression that this wasn't possible, based on this comment by @daffie:

The problem is that we cannot create the extension from Drupal. It must be done on the database by a superuser account. Which is hopefully not the account used by Drupal. We need add a hook_requirements() to test if the extension is created.

https://www.drupal.org/project/drupal/issues/3190516#comment-14107574 📌 [Policy] For PostgreSQL require that the pg_trgm extension is not only installed, but also created. Fixed

My understanding is that is why they decided not to simply enable the extension in Drupal core installation logic itself.

It looks like the 'trusted extensions' concept was added in postgres 13 https://www.postgresql.org/docs/13/release-13.html#id-1.11.6.15.5.14. Drupal 10 supports postgres versions starting with 12 which in fact requires superuser db account to create extensions.

Since by updating to D10, farmOS v3 will already increase minimum required postgres version to 12 maybe we could consider increasing it to 13 instead which would allow to safely automatically create pg_trgm extension.

Ah ok that makes sense - thanks for investigating this @wotnak!

Since by updating to D10, farmOS v3 will already increase minimum required postgres version to 12 maybe we could consider increasing it to 13 instead which would allow to safely automatically create pg_trgm extension.

It's worth considering. Debian Bullseye (the current stable version) has PostgreSQL 13. I wonder about other common platforms... and shared hosting platforms etc...

Even if we will stick with Drupal PostgreSQL minimum version requirements, it could be nice to add automatic pg_trgm extension installation when PostgreSQL v13 or newer is detected.

it could be nice to add automatic pg_trgm extension installation when PostgreSQL v13 or newer is detected.

I agree!

I wonder if there is an upstream Drupal core issue for this already. If not maybe we can open one and contribute a patch that we use in farmOS. This seems like it would make sense for Drupal core. We could do it in farmOS itself, but if there's any chance it will happen in Drupal core eventually maybe it's better to aim for that.

We talked about this on the dev call today. I'm leaning towards requiring PostgreSQL 13 for farmOS 3.x, and patching Drupal core to enable the pg_trgm extension automatically.

I wonder if there is an upstream Drupal core issue for this already. If not maybe we can open one and contribute a patch that we use in farmOS.

I got the ball rolling (still needs to be tested): ✨ Automatically install pg_trgm extension on PostgreSQL 13+ Fixed

I have this implemented in my 3.x-d10 branch. Working great! We don't need to require PostgreSQL 13, but *if* they have it then it will "just work".

Automatically closed - issue fixed for 2 weeks with no activity.