Installation on postgres fails when MD5 function disabled

[ealott]

Installation on postgres fails when MD5 function disabled

Description

New deployments of the latest FusionAuth (1.53.2) are failing on Azure Database for PostgreSQL Flexible Server version 15.8 with the error below.

Note that Azure does not allow specifying minor versions of Postgres, and auto-upgrades all PostgreSQL Flexible Servers to the latest minor release, potentially breaking existing installations.

 fusionauth-86b55b4f87-d9xgs fusionauth -- Initialize migrations so they don't run on new installs                                                                    │
│ fusionauth-86b55b4f87-d9xgs fusionauth INSERT INTO migrations (name, run_instant)                                                                                    │
│ fusionauth-86b55b4f87-d9xgs fusionauth   VALUES ('io.fusionauth.api.migration.guice.Migration_1_8_0', 0),                                                            │
│ fusionauth-86b55b4f87-d9xgs fusionauth     ('io.fusionauth.api.migration.guice.Migration_1_9_2', 0),                                                                 │
│ fusionauth-86b55b4f87-d9xgs fusionauth     ('io.fusionauth.api.migration.guice.Migration_1_10_0', 0),                                                                │
│ fusionauth-86b55b4f87-d9xgs fusionauth     ('io.fusionauth.api.migration.guice.Migration_1_13_0', 0),                                                                │
│ fusionauth-86b55b4f87-d9xgs fusionauth     ('io.fusionauth.api.migration.guice.Migration_1_15_3', 0),                                                                │
│ fusionauth-86b55b4f87-d9xgs fusionauth     ('io.fusionauth.api.migration.guice.Migration_1_30_0', 0),                                                                │
│ fusionauth-86b55b4f87-d9xgs fusionauth     ('io.fusionauth.api.migration.guice.Migration_1_42_0', 0);                                                                │
│ fusionauth-86b55b4f87-d9xgs fusionauth .  Cause: org.postgresql.util.PSQLException: ERROR: could not compute MD5 hash: disabled for FIPS                             │
│ fusionauth-86b55b4f87-d9xgs fusionauth 2024-09-27 08:35:30.978 PM INFO  com.inversoft.maintenance.db.JDBCMaintenanceModeDatabaseService - [6ZLRZw] Attempting to unl │
│ fusionauth-86b55b4f87-d9xgs fusionauth 2024-09-27 08:35:30.979 PM INFO  com.inversoft.maintenance.db.JDBCMaintenanceModeDatabaseService - [6ZLRZw] Unlock completed  │

Calling the postgres md5 function produces the same error:

SELECT MD5('PostgreSQL MD5');
ERROR:  could not compute MD5 hash: disabled for FIPS

This error appears to be caused by Microsoft disabling MD5 support in Azure Postgres 15.8.

Note that MS re-enabled the MD5 function per this comment, but it is unclear how long it will be enabled.

Searched briefly and didn't find any long term plans to remove the md5 function from postgresql, but it still might be nice to move towards FIPS compliance.

Community guidelines

All issues filed in this repository must abide by the FusionAuth community guidelines.

[mooreds]

@ealott thanks for posting this bug report.

It looks like this change (disabling md5 hashing) might have been rolled back?

From https://stackoverflow.com/questions/79038112/error-could-not-compute-md5-hash-disabled-for-fips-pginternalerror

and I can confirm that the change was rolled back now for newly created Postgres Flexible Servers (at least for our cloud rooms).

Can you confirm that this is still an issue? This community forum post indicates it still is but I don't have an easy way to stand up a testbed in Azure to confirm.

[mooreds]

Internal: from reading the database schema files, it looks like we use md5 to generate random UUIDs.

We didn't start with 1.53, though. Maybe we should consider another hashing algo like sha256 instead?

[ealott]

@mooreds Thanks for taking a look, and for the reply!

I can confirm Microsoft has re-enabled the Postgres MD5 function on our existing and new Azure Postgres Flexible Server 15.8 instances, and presumably all other versions as well. My issue is resolved.

I agree that another hashing algorithm would be ideal for those UUIDs, as Postgres disables MD5 when enabling FIPS mode, and it seems likely Microsoft will eventually disable MD5 again in the future (though hopefully with some forewarning, next time).

[mooreds]

Thanks @ealott . I'll raise this with our product/engineering team.

[mooreds]

We could also document that MD5 function support is required for any database FusionAuth is installed into (though that seems like a pretty common baseline).

[robotdan]

Historically, PostgresSQL required the uuid-ossp module to generate a UUID using uuid_generate_v4() and pgcrypto module to use gen_random_uuid(). We have intentionally not required these modules to be installed at runtime.

I think Postgres version 13 added gen_random_uuid() natively w/out requiring the pgcrypto module. But our min. version is still Postgres version 10. We could consider increasing our min. requirement, but 10 to 13 is a big jump all at once.

• https://fusionauth.io/docs/get-started/download-and-install/system-requirements

Perhaps that is another way to generate a UUID w/out MD5, but I tend to think it is simpler to just re-enable MD5 unless this is being turned off en-masse.

Here is a useful thread in SO, all of these examples show MD5 but perhaps we need to investigate further.

• https://stackoverflow.com/a/21327318/3892636

Or maybe we can create a stored procedure that uses gen_random_uuid() if available, and falls back to our current method which could allow us to work when MD5 is disabled as long as we are on version >= 13?

[mooreds]

it is simpler to just re-enable MD5 unless this is being turned off en-masse.

It was turned off by Azure disabling support. They later re-enabled it.

I'm not sure what 'en-masse' means, but a hyperscaler disabling it in a new minor release of a supported database platform is going to affect a significant number of folks who want to run FusionAuth there.

Not as many as if PG turned off this function in their default settings, though.