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?

[vaibhavdalvi93]

Thanks, @GreenReaper for detailed requirement and information. Below is my understanding about the authentication mechanisms:

As per official documentation of mongo-c driver, By default, if a username and password are provided as part of the connection string (and an optional authentication database), they are used to connect via the default authentication mechanism of the server. For detailed explanation about this, please refer https://mongoc.org/libmongoc/current/authentication.html. So, though we're NOT specifying the authentication mechanism with URI, still it uses default authentication mechanism of the server.

# 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"

I think, you have mentioned wrong/invalid authentication mechanism in the connection string (i.e. uri in mongo_wrapper_meta.c) . Could you please cross-check.

...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;

Looks like you missed to create the foreign table keywords_autosuggest_nl post dropping it. The existence of the table happens before authentication check.

I think, we don't need to do anything here. Request you to please share your valuable feedback.

[GreenReaper]

I identified one problem with what I had been doing: I'd thought DROP EXTENSION would fully unload the extension and CREATE EXTENSION would reload a newly-installed version, but it seems a restart of the server is required to clear the library from RAM.

As such the changes I had been making were not 'taking' and so I was getting the default behaviour with the originally compiled connection strings, i.e. Cosmos DB for MongoDB (RU) works, Cosmos DB for MongoDB (vCore) does not.

This suggests that Cosmos DB for MongoDB (vCore) does not work the same way as MongoDB itself (i.e. it requires the authMechanism to select SCRAM-SHA-256), because I was getting the error "The authentication mechanism provided is not supported..." with mongo_fdw's default connection string for this combination of options:

 uri = bson_strdup_printf("mongodb://%s:%s@%s:%hu/%s?ssl=%s",
          opt->svr_username, opt->svr_password,
          opt->svr_address,
          opt->svr_port, opt->svr_database,
          opt->ssl ? "true" : "false");

Once I changed this string to "mongodb://%s:%s@%s:%hu/%s?ssl=%s&authMechanism=SCRAM-SHA-256" and got it to apply changes, I encountered a different error:

select * from keywords_autosuggest_nl;
ERROR:  could not connect to server mongo_vcore_nl_ib
HINT:  Mongo error: "SCRAM Failure: invalid salt length of 16 in sasl step2"

This appears to be a bug, quite possibly on Microsoft's end. I've tried upgrading Debian's libmongoc-1.0-0 from 1.23.1-1+b1 to 1.25.2-1 to see if it was related to the SASLprep ICU -> utf8proc changes. However, this does not appear to have had any impact.

Looking at the code, I think they are sending the salt size for SCRAM-SHA-1, i.e. MONGOC_SCRAM_SHA_1_HASH_SIZE (20) - 4 = 16, when in this case it should be a decoded salt of length MONGOC_SCRAM_SHA_256_HASH_SIZE (32) - 4 = 28.

[shurarama]

Hi, today I tested the mongoc driver with cosmos and apparently the salt issue was resolved on their end

[GreenReaper]

That is good to know! I will close this issue then, at least until I have retested and found that it cannot be made to work in some way (it seemed that the vCore version was more problematic, perhaps because it was newer).