When deploying this, I got an error in the hydra-init

hydra-init[26198]: upgrading Hydra schema from version 56 to 57
hydra-init[26198]: executing SQL statement: CREATE EXTENSION pg_trgm
hydra-init[26198]: schema upgrade failed: main::run_(): DBI Exception: DBD::Pg::db do failed: ERROR: permission denied to create extension "pg_trgm"
hydra-init[26198]: HINT:  Must be superuser to create this extension. at /nix/store/js1q5qy2vskbgzg4fr6y4sjsl2lnlvy8-hydra-0.1.2619.00a3686d56c63aa4ec0455219e1f6380a8242a95/bin/.hydra-init-wrapped line 59

Is there something globally that can be set in the hydra module to allow this extension to be configured?

Also, when running the CREATE EXTENSION as postgres user, I could not run the other query that was in the upgrade script.

hydra=> CREATE INDEX IndexTrgmBuildsOnDrvpath ON builds USING gin (drvpath gin_trgm_ops);
ERROR:  operator class "gin_trgm_ops" does not exist for access method "gin"

@nlewo Any idea how to properly fix this?

I'm using a Postgres container image and the default user is superuser... This is why I didn't notice this issue.
This is pretty annoying since it seems only a superuser can create this extension. So, we cannot enable the extension this in the migration scripts.
It would be possible to temporary give superuser powers to the postgres hydra user in the hydra-init prestart script.
Something like:

echo "alter user hydra with superuser;" | sudo -u postgres psql
echo "create extension pg_trgm" | psql -U hydra
echo "alter user hydra with nosuperuser;" | sudo -u postgres psql

Then, I have been able to create the index with the hydra user.

If I understand well, the extension is then owned by the hydra user. On multi user/db setups, this may then be an issue.

EDIT: the initial sql script was wrong.

@rbvermaa I propose to:

• create the extension in the NixOS module
• try to create it in the hydra.sql and in the migration script. If it fails, we only emit a warning to the user. Actually, Hydra works without this index but some queries are slower.

I'll push a PR with this tomorrow (it seems to be working but needs more tests).