deploy-to-nixos: migrations using CREATE EXTENSION fail in production (migrate.service runs as non-superuser DB user) #2752

What happened

A production app deployed via IHP's deploy-to-nixos went down (502) after a routine deploy. The deploy shipped a migration containing:

CREATE EXTENSION IF NOT EXISTS "cube";
CREATE EXTENSION IF NOT EXISTS "earthdistance";

CREATE INDEX listings_active_coordinates_gist_idx
    ON listings USING gist (coordinates)
    WHERE duplicate_of IS NULL AND is_active AND coordinates IS NOT NULL;

migrate.service failed with:

ScriptSessionError ... (ServerError "42501" "permission denied to create extension \"earthdistance\"" Nothing (Just "Must be superuser to create this extension.") Nothing)

The app binary that was deployed alongside expects the new schema, so the deploy is effectively broken until someone SSHes in and creates the extension as the postgres superuser by hand. (In our setup app.service has requires = [ "migrate.service" ], so the whole site was down for ~2h until manual intervention; with the stock module the app keeps running against the old schema, which is arguably worse because it fails at request time.)

Root cause

NixSupport/nixosModules/services/migrate.nix runs migrations as the application DB user from cfg.databaseUrl:

systemd.services.migrate = lib.mkIf (cfg.migrations != null) {
    serviceConfig = {
        Type = "oneshot";
        ExecStart = ihp.apps."${pkgs.system}".migrate.program;
    };
    environment = {
        DATABASE_URL = cfg.databaseUrl;
        ...
    };
};

That user is created in appWithPostgres.nix without SUPERUSER (default databaseUser = "root" — a plain role that just happens to be named root). PostgreSQL only allows non-superusers to CREATE EXTENSION for extensions marked trusted, and quite a few common ones are not. On PostgreSQL 15.18:

     name      | version | trusted
---------------+---------+---------
 cube          | 1.5     | t
 earthdistance | 1.1     | f

Other popular untrusted extensions: postgis, pg_stat_statements, plpython3u, file_fdw, ... (and earthdistance lost its trusted flag in the Aug 2023 PostgreSQL point releases, so this even regresses on Postgres upgrades).

Why this is a trap

• It works in dev. The devenv Postgres runs as the developer's OS user, which is a superuser in the local cluster, so the migration runs fine locally and in ghci.
• The same problem exists at provisioning time: appWithPostgres.nix's initialScript does SET ROLE '${cfg.databaseUser}' before \i ${cfg.schema}, so a Schema.sql containing an untrusted CREATE EXTENSION fails the same way on a fresh server.
• Nothing warns about this until the production deploy fails, and recovery requires manual sudo -u postgres psql + systemctl reset-failed.

Suggested fix

Some options, roughly in order of preference:

1. Add a services.ihp.postgresExtensions option (list of extension names) that IHP provisions as superuser — e.g. via services.postgresql postStart/a small oneshot unit ordered before migrate.service that runs CREATE EXTENSION IF NOT EXISTS ... as postgres. Migrations and Schema.sql can then rely on the extension existing and keep CREATE EXTENSION IF NOT EXISTS as a no-op for dev.
2. Run migrate.service through a superuser connection on the local socket (simplest, but grants migrations full cluster privileges).
3. At minimum: document in the deploy-to-nixos guide that CREATE EXTENSION for untrusted extensions will fail in production migrations, and have migrate print a hint when it hits error code 42501 on a CREATE EXTENSION statement.

Related: #333 and #1262 were the dev-side versions of this problem; this is the production/deploy-to-nixos side.