Use of MD5 for Advisory Locks Crashes pg-boss on FIPS-Compliant Platforms

[mos2]

Hi there,

I tried to run a Node.JS application that I am developing using pg-boss on a FIPs-compliant OS platform, but pg-boss crashes at startup with the following error when trying to execute an SQL query against our PostgreSQL database:

Startup failed: error: could not compute MD5 hash: unsupported
- stack trace: error: could not compute MD5 hash: unsupported
at /app/node_modules/pg-pool/index.js:45:11
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async Db.executeSql (/app/node_modules/pg-boss/src/db.js:28:14)
at async Contractor.create (/app/node_modules/pg-boss/src/contractor.js:53:7)
at async Contractor.start (/app/node_modules/pg-boss/src/contractor.js:46:7)
at async PgBoss.start (/app/node_modules/pg-boss/src/index.js:101:5)

I tracked this down to the use of the MD5 function used at https://github.com/timgit/pg-boss/blob/master/src/plans.js#L694 to compute advisory locks:

function advisoryLock (schema) {
  return `SELECT pg_advisory_xact_lock(
      ('x' || md5(current_database() || '.pgboss.${schema}'))::bit(64)::bigint
  )`
}

My understanding from reading http://peter.eisentraut.org/blog/2023/12/05/postgresql-and-fips-mode, is that since PostgreSQL 14, rather than using a built-in implementation of the hashing algorithm, it instead calls out to openssl on the underlying platform. On a FIPS-compliant system, openssl will simply reject this call immediately, which is what happens here. I confirmed this by trying to manually run an SQL query with md5 on my PostgreSQL instance on the FIPS-compliant system, and observed the same error reported to pg-boss:

users=# SELECT MD5('PostgreSQL MD5');
ERROR:  could not compute MD5 hash: unsupported

Is there any plan to update pg-boss to be FIPS-compliant by replacing the use of MD5 with a compliant hashing algorithm like SHA256?

[timgit]

This is resolved in v10, but it's only available in beta