Migrations not running on new Azure Postgres Flexible Server

[WatcherWhale]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Ory Network Project

No response

Describe the bug

When starting a new Hydra instance in a fresh Azure Postgres Flexible Server (v15) the migrations will fail immediately due to the md5 being disabled for FIPS compliancy.

The culprits in this situation are following migrations:

• https://github.com/ory/hydra/blob/master/persistence/sql/migrations/20150101000001000000_networks.postgres.up.sql
• https://github.com/ory/hydra/blob/f83193f90814691d10d88493eb6357cf37565115/persistence/sql/migrations/20211011000001000000_change_jwk_primary_key.postgres.up.sql

A possibillity is to change the uuid generation with the uuid-ossp extenstion.

Reproducing the bug

1. Start an Azure Postgres Flexible Server version 15
2. Point hydra to this database and run the migrations

Relevant log output

{"audience":"application","level":"info","migration_file":"migrations/20150101000001000000_networks.postgres.up.sql","migration_name":"networks","msg":"Migration has not yet been applied, running
 migration.","service_name":"Ory Hydra","service_version":"v2.2.0","time":"2024-09-25T13:37:00.960450904Z","version":"20150101000001000000"}
this error should never be printed
Could not apply migrations:
ERROR: could not compute MD5 hash: disabled for FIPS (SQLSTATE XX000)
error executing migrations/20150101000001000000_networks.postgres.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
-- hydra:generate hydra migrate gen
CREATE TABLE "networks" (
  "id" UUID NOT NULL,
  PRIMARY KEY("id"),
  "created_at" timestamp NOT NULL,
  "updated_at" timestamp NOT NULL
);

INSERT INTO networks (id, created_at, updated_at) VALUES (uuid_in(
  overlay(
    overlay(
      md5(random()::text || ':' || clock_timestamp()::text)
      placing '4'
      from 13
    )
    placing to_hex(floor(random()*(11-8+1) + 8)::int)::text
    from 17
  )::cstring
), '2013-10-07 08:23:19', '2013-10-07 08:23:19');

Relevant configuration

No response

Version

2.2.0

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Kubernetes with Helm

Additional Context

No response

[aeneasr]

The only way to fix this is to find an alternative uuid generator for PostgreSQL that is compatible with all PostgreSQL version that are still being updated & maintained by the postgres team