pgstac is not FIPS compliant

Description

This issue was discovered after a RHEL 9 machine running postgres 16 and titiler-pgstac was upgraded to use FIPS which, among other things, bars the use of md5 hashes. This makes pgstac unusable on some systems.

Error

The superficial error comes from titiler-pgstac:

GET http://domain.com/titilerpgstac/collections/TimeCogs/tiles/WebMercatorQuad/6/17/39?assets=asset&datetime=2024-01-04T14:00:00Z/2025-01-27T22:05:27Z&exitwhenfull=false&skipcovered=false

"could not compute MD5 hash: disabled for FIPS\nCONTEXT: SQL function "search_hash" statement 1\nPL/pgSQL function search_query(jsonb,boolean,jsonb) line 13 during statement block local variable initialization"

I am suspecting the error is referring to this line here and its usage of the md5() function: https://github.com/stac-utils/pgstac/blob/6da165b7c4f6da321d0b58bbb78da2887763a0d8/src/pgstac/pgstac.sql#L3405

Possible Solution

Here are relevant example SQL commands run on our FIPS postgres instance. One to highlight the limitation and the other as a possible alternative:

postgres=# SELECT md5('hello');
ERROR:  could not compute MD5 hash: disabled for FIPS

postgres=# SELECT encode(sha256('hello')::bytea, 'hex');
                              encode                              
------------------------------------------------------------------
 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
(1 row)

Specs

• pgstac 0.8.6 (I know, not the latest but the md5()s are still in the latest)
• pypgstac 0.8.6
• RHEL 9
• postgres 16
• titiler-pgstac 1.4.0

Start in src/pgstac/pgstac.sql around line 3405 and trace the search_hash function and its use from search_query. Check all remaining md5() uses in the current schema, then verify that the chosen FIPS-compatible hashing behavior preserves query operation on PostgreSQL 16 with FIPS enabled.