Support SCRAM-SHA-256 auth mechanism

[GreenReaper]

When trying to use Azure's free tier of Cosmos DB for MongoDB (vCore) with MongoDB 6.0 API on PG 16 I ran into the following:

# CREATE EXTENSION mongo_fdw;
CREATE EXTENSION
# CREATE SERVER mongo_vcore_nl_ib
        FOREIGN DATA WRAPPER mongo_fdw
        OPTIONS (address 'c.mongo-vcore-nl-ib.mongocluster.cosmos.azure.com', port '10260', ssl 'true');
CREATE SERVER
# CREATE USER MAPPING FOR postgres
        SERVER mongo_vcore_nl_ib
        OPTIONS (username 'XXX', password 'XXX);
CREATE USER MAPPING
# CREATE FOREIGN TABLE keywords_autosuggest_nl
        (
                _id name,
                submissions_count int,
                keyword text
        )
        SERVER mongo_vcore_nl_ib
        OPTIONS (database 'inkbunny', collection 'keywords_autosuggest');
CREATE FOREIGN TABLE
# SELECT * FROM keywords_autosuggest_nl;
ERROR:  could not connect to server mongo_vcore_nl_ib
HINT:  Mongo error: "The authentication mechanism provided is not supported in the service. Please use SCRAM-SHA-256 auth mechanism"

The recommended connection string¹ for this server cluster is:

mongodb+srv://<user>:<password>@mongo-vcore-nl-ib.mongocluster.cosmos.azure.com/?tls=true&authMechanism=SCRAM-SHA-256&retrywrites=false&maxIdleTimeMS=120000

Examination of mongo_wrapper_meta.c suggests that SCRAM-SHA-256 support is not envisaged. However, since MongoDB mandates server-side digesting (unlike SCRAM-SHA-1, which can pick client-side), enabling support is trivial.

Adding the following to the connection strings in mongoConnect() of mongo_wrapper_meta.c:

&authMechanism=SCRAM-SHA-256&retrywrites=false&maxIdleTimeMS=120000

...and dropping and recreating as above results in a query result indicating a successful connection:

# select * from keywords_autosuggest_nl;
ERROR:  relation "keywords_autosuggest_nl" does not exist
LINE 1: select * from keywords_autosuggest_nl;
                      ^

mongo_fdw also worked with a RU-based Cosmos DB for MongoDB 4.2 cluster with the addition of replica_set 'globaldb'. Username is the resource name, while the password seems to be an autogenerated Base64-encoded token. It may be that authMechanism is ignored, but I have not tested this across all API versions supported (3.2, 3.6, 4.0, 4.2 - vCore is 5.0 or 6.0).

# select * from keywords_autosuggest_nl;
 _id | submissions_count | keyword
-----+-------------------+---------
(0 rows)

# insert into keywords_autosuggest_ru values ('1', '1', 'test');
INSERT 0 1

# select * from keywords_autosuggest_ru;
           _id            | submissions_count | keyword
--------------------------+-------------------+---------
 6580a2e57e2cb8f7b305ff97 |                 1 | test

# insert into keywords_autosuggest_ru values (null, '2', 'test2');
INSERT 0 1
harmony-prod=# select * from keywords_autosuggest_ru;
           _id            | submissions_count | keyword
--------------------------+-------------------+---------
 6580a2e57e2cb8f7b305ff97 |                 1 | test
 6580a30a7e2cb8f7b305ff98 |                 2 | test2
(2 rows)

# delete from keywords_autosuggest_ru;
DELETE 2

For the purpose of testing, shared-resource 32GB Cosmos DB vCore accounts for MongoDB recently started to become available for free in East US (Virginia), West Europe (Netherlands) and East Asia (Singapore).

The RU-based accounts have a 30-day 'try for free' option as well as a persistent Azure Cosmos DB free tier for the first Cosmos DB account created in a subscription. You don't have to be limited to 1000 RU/s and 25 GB of storage, e.g. I have 5000 RUs autoscaled down to 500 and distributed across five regions, two of which are high-availability, using per-region autoscale - it appears to offer a per-hour credit based on the free tier resources priced according to the initial location, so consider selecting the most expensive you might use.

---

¹ The `mongodb+srv' protocol indicates a DNS lookup is desired to identify the server, along these lines:

> nslookup -q=SRV _mongodb._tcp.mongo-vcore-nl-ib.mongocluster.cosmos.azure.com
...
Non-authoritative answer:
_mongodb._tcp.mongo-vcore-nl-ib.mongocluster.cosmos.azure.com   SRV service location:
          priority       = 0
          weight         = 0
          port           = 10260
          svr hostname   = c.mongo-vcore-nl-ib.mongocluster.cosmos.azure.com

Support for this would also be nice, as simply plugging in the hostname won't work. Azure discourages relying on the 'c.' prefix. Perhaps the meta driver will handle this, in which case all that's needed is an option to change the scheme?