Meshcentral: Exploring alternative database backends

[whalehub]

In light of the news that RHEL and Debian are going to drop MongoDB from their repos going forward due to MongoDB's licensing change, I think some serious consideration should be given to making MeshCentral compatible with traditional SQL databases such as MySQL/MariaDB or Postgres.

[D4V3M0NK]

Just a thought: if Y&T ("Ylian & Team") decide to persue this, it might be prevalent to get an idea of what current users would prefer? Might make development more expeditious.

(My vote would be MySQL/MariaDB)

[Ylianst]

So, even if these Linux distributions drop MongoDB from their repo, it's not that difficult to still install it. However, I am willing to consider an alternative DB, but I preference would be something that is super security that freely supports encrypt-at-rest.

[solrac901]

MySQL and MariaDB supports the encryption at rest.

https://mariadb.com/kb/en/library/data-at-rest-encryption-overview/
https://dzone.com/articles/mysql-data-at-rest-encryption

[Ylianst]

Nice! Thanks for the info. MongoDB only supports encryption at rest if you pay for the the enterprise version, so this is good.

[whalehub]

@D4V3M0NK @Ylianst @solrac901 Postgres doesn't appear to support encryption-at-rest, so I vote in favor of MariaDB as well.

[Ylianst]

MariaDB seems to be an interesting candidate indeed. Of course, MeshCentral is based on NoSQL and this is a very different database, I will need to do quite a bit of exploring. This is not high priority as I have been busy with a bunch of big ticket items for some customers. So, I don't expect to be looking into this anytime soon.

[Ylianst]

Ok, given how flooded with feature requests I am right now, I am unlikely to look into alternative back-ends anytime soon. I am probably going to put in other big features like server peering first before this.

[whalehub]

@Ylianst Any update on this?

[Ylianst]

OMG. I am so backed up with work, it's a bit overwhelming. Taking a quick look at this now, MariaDB is a SQL relational database, like MySQL. It's not going to be easy to move to that type of DB. MongoDB is a JSON document store, and the easiest DB's would be this list: Amazon DynamoDB, Couchbase, Microsoft Azure Cosmos DB, CouchDB.

How would people feel about Apache CouchDB? It would be easier to support. I have optimized the indexes on MongoDB quite a bit, it will take some work to get the same optimizations on a different DB.

FYI. Look like nano is the official NodeJS adapter for CouchDB, looks popular and well documented. No encrypt at rest (except this workaround).

[D4V3M0NK]

I appreciate the effort being put in here @Ylianst , but I would still vote for the MySQL / MariaDB option (you kinda get two databases in one with this option too!) Once Oracle does what I'm sure everyone is waiting for them to do, everyone will jump into Maria's ship anyway as that means little or no code change for them: wouldn't it make sense to adopt that point of view too?

Personally, one of the deployments that I am looking at using this for requires encryption at rest, and for a system that makes access to remote systems unbelievably easy, I would suggest that security remains a top priority. I appreciate the offer of nano, but a workaround (that supposedly takes an hour to implement) sounds more like open-heart surgery to me (I didn't go into depth with the link though - this is all written with my gut reaction).

I certainly appreciate the team reaching out to it's community, to get our thoughts on the matter.

[solrac901]

I vote for Couch first and Maria where i can see the ER diagram of the tables that mesh use?
Regards.

[Ylianst]

MeshCentral uses a set of 4 or 5 flat tables with Key-->JSON. You then have a few values in the JSON that are indexed. So, it's a NoSQL model that is a good fit for NeDB, MongoDB, Couch... It currently uses no SQL queries at all.

This said, MariaDB does seem to support semi-structured data. Also can also see an example here of using MariaDB as a JSON document store with virtual indexes. So now, I am thinking I could give this a try.

There is no way in my view that I am going to go into strict detained ER diagram in the future since the code evolves quickly and there is little chance of keeping a stable schema. I did that a long time ago with MeshCentral1 (That ran on MSSQL) and it was terrible.

[Ylianst]

Published MeshCentral v0.4.8-m with added support for MariaDB and MySQL.

As noted in the picture above, you need to set only one database and you can pick from one of these 3 lines:

    "MariaDB" : { "host": "localhost", "user": "", "password": "" },
    "MySQL" : { "host": "localhost", "user": "", "password": "" },
    "MongoDb": "mongodb://127.0.0.1:27017"

If you don't specify any of these, NeDB is going to be used. I would appreciate a bunch of testing on MariaDB and/or MySQL, also auto-backup is currently not supported on MariaDB or MySQL. Feedback appreciated.

[D4V3M0NK]

Node : 8.10.0, NPM : 3.5.2, Ubuntu 18.04.3 (Server), MySQL Server 5.7.29 (ubuntu)

Initial testing looks good if the MySQL server is configured to permit insecure connections from clients.

Is there an additional configuration option in config.json for setting secure connections?
(eg
"MySQL" : { "host": "127.0.0.1", "user": "meshcentral", "password" : "meshcentral", "secure" : true },
It appears that if I force the meshcentral database user to connect using TLS, then I get the following when I run the server (manually) from the command line:

TypeError: Cannot read property 'length' of undefined
at /home/vroot/node_modules/meshcentral/db.js:45:23
at Query. (/home/vroot/node_modules/meshcentral/db.js:725:37)
at Query. (/home/vroot/node_modules/mysql/lib/Connection.js:526:10)
at Query._callback (/home/vroot/node_modules/mysql/lib/Connection.js:488:16)
at Query.Sequence.end (/home/vroot/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at /home/vroot/node_modules/mysql/lib/protocol/Protocol.js:236:14
at _combinedTickCallback (internal/process/next_tick.js:131:7)
at process._tickCallback (internal/process/next_tick.js:180:9)
TypeError: Cannot read property 'length' of undefined
at /home/vroot/node_modules/meshcentral/meshcentral.js:987:26
at Query. (/home/vroot/node_modules/meshcentral/db.js:725:37)
at Query. (/home/vroot/node_modules/mysql/lib/Connection.js:526:10)
at Query._callback (/home/vroot/node_modules/mysql/lib/Connection.js:488:16)
at Query.Sequence.end (/home/vroot/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at /home/vroot/node_modules/mysql/lib/protocol/Protocol.js:236:14
at _combinedTickCallback (internal/process/next_tick.js:131:7)
at process._tickCallback (internal/process/next_tick.js:180:9)

[Floche87]

MC don't start with MariaDB. I tested on a fresh install and on my production instance.
There is no table in database and I get the following error when i manually start MC or by systemd :

févr. 05 07:56:13 smtp systemd[1]: Started Meshcentral Server.
févr. 05 07:56:20 smtp nodejs[3532]: Installing mariadb...
févr. 05 07:58:00 smtp nodejs[3532]: TypeError: Cannot read property 'length' of undefined
févr. 05 07:58:00 smtp nodejs[3532]: at /opt/meshcentral/node_modules/meshcentral/db.js:45:23
févr. 05 07:58:00 smtp nodejs[3532]: at /opt/meshcentral/node_modules/meshcentral/db.js:721:61
févr. 05 07:58:00 smtp nodejs[3532]: at process._tickCallback (internal/process/next_tick.js:68:7)
févr. 05 07:58:10 smtp nodejs[3532]: TypeError: Cannot read property 'length' of undefined
févr. 05 07:58:10 smtp nodejs[3532]: at /opt/meshcentral/node_modules/meshcentral/meshcentral.js:987:26
févr. 05 07:58:10 smtp nodejs[3532]: at /opt/meshcentral/node_modules/meshcentral/db.js:721:61
févr. 05 07:58:10 smtp nodejs[3532]: at process._tickCallback (internal/process/next_tick.js:68:7)

Conf : Debian buster on arm64, NodeJS 10.15.2~dfsg-2, MariaDB 10.3.18-0+deb10u1, NPM 5.8.0+ds6-4
Meshcentral : 0.4.8-m

[Ylianst]

Just published MeshCentral v0.4.9-v with improvements for MariaDB and MySQL. You can also do:

node node_modules/meshcentral --debug db

To see any database errors. Any data on this would be appreciated.