I realised that as we are securing our node-red editor and the dashboard by enhancing user-authentication.js and dashboard-auth.js where we use the npm package better-sqlite3 to access the same db that we access from node-red as well, we actually hit bullet point 2.2.1. Multiple copies of SQLite linked into the same application:
"A close() operation on one connection might unknowingly clear the locks on a different database connection, leading to database corruption."
So the question is: is it possible to use node-red-node-sqlite from the node.js side?
Without knowing more, it feels to me as though you are conflating some actions that probably should be subject to a separation of concerns.
If you give access to the DB containing your authentication data from Node-RED, anyone able to make changes to flows could now also make authentication changes - is that really what you want?
Why not have a separate db file for the authentication data and limit access to it to a node.js module that then gets linked to the editor auth and the dashboard auth?
Perhaps I haven't quite understood how you are working thoug?
I'm not sure that I got your suggestion right but it seems to me that it'd work around the issue. However, as the app itself provides the possibility to create users with different roles of which some allow access to the editor while others only to the app we would end up maintaining the editor permission specific db from node-red, right? As far as I can see, that would mean that we'd need to use better-sqlite3 via require in a function node which actually leads to the same problem as two instances of a library would have access to a db file. Unless, it's possible to pass a db handler instance up from the node.js side.
Update:
My last sentence made me think actually about that in settings.js functionGlobalContext I could pass an instance of better-sqlite3 to the node-red which could be used within a function node. If it can also be passed around via app.set and accessed by app.get in user-authentication.js and dashboard-auth.js then that would ensure that there's only one better-sqlite3 instance handling the editor permission specific db.
It isn't necessary to do it that way, that's only 1 way. You could maintain it from another tool. That would be your decision.
You could even have a separate instance of Node-RED that was only accessible to admins to manage the identities. You could then create a simple, controlled API for use in the actually authentication process.
The point I'm trying to get across is that opening the db up to a powerful and flexible platform like Node-RED is easy enough but in doing so, you are probably losing any useful control over the database.
The standard method for doing user security is to have the identity data in a separate service with controlled access. Just as the standard method for storing passwords is to HASH them before storage so that you never keep the plain text version. Similarly, if you aren't using TLS encryption and if you aren't carefully validating user inputs, you might as well not be bothering with security at all.
These complexities are why it is generally best not to roll-your-own identity and authentication tools but to pick up mature 3rd-party tools.
Believe you me, I've seen some unbelievably appalling systems that get this wrong over the years including on systems handling £bn per year!
This is an edge device where everything stays within a local network. The authentication is done by a third party tool which is the node-red api The only thing we added was the user role based editor access blocking. Passwords are hashed so no clear text passwords are stored either. This system is really not that complicated, sticking to the capabilities of node-red and its dashboard including that it's single user as well (meaning one at a time). I'll give a shot to what I wrote in the update of my post. That seems to be a good work around if it works. Splitting the db was a good hint (thanks!) though being able to use the same db handler what node-red-node-sqlite uses would be the best.
By the way, by checking the underlying sqlite3 package code of node-red-node-sqlite, it seems that it caches the db handler objects by their file name. See lines 28-55. So theoretically, I could even require sqlite3 in functionGlobalContext (and also store it by app.set) and that would also guarantee that always the same instance is used (at node.js side and node-red side as well) for db access right?
It will get the same copy of the module. But whether that is the same instance depends on what is exported. For example, in uibuilder library modules I've moved to a singleton class approach which guarantees that every require gets the same instance of the class. You may need to check the code to know whether you genuinely get a single instance connection to the db.
I pondered about it a bit more and came to the conclusion that doing the same in user-authentication.js or dashboard-auth.js what node-red-node-sqlite does as a wrapper with sqlite3 module (i.e. claiming access to a db file) must be ok as otherwise what node-red-node-sqlite does for each sqlite node is also not fine
The only thing we added was the user role based editor access blocking. Passwords are hashed so no clear text passwords are stored either. This system is really not that complicated, sticking to the capabilities of node-red and its dashboard including that it's single user as well (meaning one at a time). I'll give a shot to what I wrote in the update of my post. That seems to be a good work around if it works. Splitting the db was a good hint (thanks!) though being able to use the same db handler what node-red-node-sqlite uses would be the best.