Support SSL connection in MongoDB integration engine

[OmarBazaraa]

Is it possible to support SSL connection when using MongoDB integration engine?

Use Case

This will enable us to integrate securely with MongoDB servers from outside of ClickHouse private network. Besides, it will enable us to integrate with Azure CosmosDB (as it supports MongoDB interface, but it only comes with SSL enabled).

Context

When we tried to connect to an SSL secured MongoDB server from ClickHouse, ClickHouse was not responding (see this for more details), connecting to a MongoDB server without SSL connection was working fine.

Version 1

We tried a quick and dirty experiment to connect to our SSL secured MongoDB server like the way found in ClickHouse in src/Storages/StorageMongoDB.cpp.

Poco::MongoDB::Connection connection(MONGO_HOST, MONGO_PORT);

Poco::MongoDB::Database poco_db(MONGO_DATABASE_NAME);

if (!poco_db.authenticate(connection, MONGO_USERNAME, MONGO_PASSWORD, Poco::MongoDB::Database::AUTH_SCRAM_SHA1))
{
    std::cout << "Cannot authenticate in MongoDB, incorrect user or password";
}
else
{
    std::cout << "Authentication succeeded";
}

Running the previous code gave us a similar behavior - no response.

Version 2

By changing the code to be as follows:

class MongoDbSocketFactory : public Poco::MongoDB::Connection::SocketFactory
{
    public:
        Poco::Net::StreamSocket createSocket(const string & host, int port, Poco::Timespan connectTimeout, bool secure)
        {
            auto socket = secure ? createSecureSocket(host, port, connectTimeout) : createPlainSocket(host, port, connectTimeout);

            socket.connect(Poco::Net::SocketAddress(host, port));

            return socket;
        }
        
    private:
        Poco::Net::StreamSocket createSecureSocket(const string & host, int port, Poco::Timespan connectTimeout)
        {
            Poco::Net::SecureStreamSocket socket;

            socket.setPeerHostName(host);

            return socket;
        }

        Poco::Net::StreamSocket createPlainSocket(const string & host, int port, Poco::Timespan connectTimeout)
        {
            return Poco::Net::StreamSocket();
        }
};

MongoDbSocketFactory socketFactory;

// MONGO_URI = "mongodb://<username>:<password>@<host>:<port>/<database>?ssl=true"
Poco::MongoDB::Connection connection(MONGO_URI, socketFactory);

Poco::MongoDB::Database poco_db(MONGO_DATABASE_NAME);

if (!poco_db.authenticate(connection, MONGO_USERNAME, MONGO_PASSWORD, Poco::MongoDB::Database::AUTH_SCRAM_SHA1))
{
    std::cout << "Cannot authenticate in MongoDB, incorrect user or password";
}
else
{
    std::cout << "Authentication succeeded";
}

We were able to connect successfully to our SSL secured MongoDB server.

[filimonov]

@OmarBazaraa why not just sending it as PR?

Fix looks ok, and follows the Poco recommended/documented way of doing it: "ssl: If ssl=true is specified, a custom SocketFactory subclass creating a SecureStreamSocket must be supplied."

See https://pocoproject.org/docs/Poco.MongoDB.Connection.html#21940