Uibuilder sqlite security.js

Hello Folks,

It's me again, with another rather stupid question. Shame on me :no_mouth:

How do I connect to a sqlite (node-red-node-sqlite) from within the security.js file? Or do I have to send a message with uibuilder.send()? If I have to do so, how do I wait in the userValidate function for the database response? I might be on the complete wrong track, any help is appreciated. Thanks in advance!

best regards,


No such thing as a stupid question - though plenty of stupid answers :anguished:

And in this case, certainly not stupid, a very reasonable question in fact.

To use SQLlite or any other DB in security.js, you just need to remember that security.js is a node.js module. The code inside the exports section is used inside uibuilder.js which itself is, of course, inside Node-RED which runs under node.js.

Any code outside the export isn't directly accessible to uibuilder but it IS accessible to the functions in the export. That means, to use a DB, you need to find a node.js library that supports the db. You can then require that library at the top of security.js and make use of the functions the library exposes inside the exported functions.

The functions in the export are standard and must be present in order for uibuilder to make use of them. There is a reasonably well defined set of inputs and outputs. Extended documentation is available in the technical docs. Please do, however, bear in mind that the API for security is not fully stable and might need to change in the future.

I hope that makes sense?

Please do let us know when you get something working - it would be great to get an example in the WIKI so that others can learn as well. And keep asking the questions - only way to learn. I also learn as you ask since I've absolutely no doubt that there are lots of things I've missed or bugs that are lurking in dark corners.

Again, the exact sequence of events should be documented in the technical docs.

At the moment, when you turn on security, you start to get more debug messages and nothing is actually secured. However, most if not all of the functions are in place. So in theory, you could add a simple check to the input and output of your uibuilder node that prevented data from flowing if the user wasn't authenticated.

When security is on, all messages should contain a msg._auth object


If the client isn't authenticated, that should be reflected in the object as seen above.

At present, only when you call the logon function will the security.js userValidate function get called. If that function returns an _auth object showing that authentication succeeded, A JWT token is created and attached to the _auth object.

After that, every message received by uibuilder validates the JWT which MUST be provided in the msg._auth for every message. The uibuilderfe library does this for you. That library also provides logon and logoff convenience functions.

The JWT has an embedded timeout. If a msg is received and the timeout has expired, the client will be invalidated and will have to log back in. There is a setting in uibuilder to auto-extend that timeout. What that does is exend the timeout every time a msg is received from the client that contains the JWT.

WARNING: JWT is NOT a security feature on its own. The token can be hijacked and replayed. So token lifespans should be short. This is an area that needs improving in uibuilder, we need a standard mechanism to add other checks to the token check - for example checking if the token was received from the same IP address as the original. So some additional standard functions will need to be added to security.js and linked to from the create/validate token functions but they aren't there yet.

So, yes, you need to call the uibuilder.logon function in your front-end, passing at least the ID (and generally a password). That sends a control msg back to uibuilder which, in turn, calls its own logon function that then calls the security.js userValidate. In the userValidate function, you will replace the default code with something that tests the uid and pw against your db.

Note that when storing passwords in a db - ALWAYS securely hash the pw before saving and only ever compare the hashes. Also, because you are sending the password from the front-end to Node-RED, you MUST always use TLS (HTTPS & WSS) - uibuilder will whinge at you if you don't because implementing that in live without TLS, your passwords are sent over the wire in clear text.

uibuilder then sends a response message back to your front end code that you can validate. The following function is an example of code that will let you take actions in your front-end if the client is logged on or off:

        // If user is logged on/off
        uibuilder.onChange('isAuthorised', function(isAuthorised){
            console.info('[indexjs:uibuilder.onChange:isAuthorised] isAuthorised changed. User logged on?:', isAuthorised)
            console.log('authData: ', uibuilder.get('authData'))
            vueApp.isLoggedOn = isAuthorised

You can use this for turning on/off the appropriate login/logout form in your html.

If you want, once uibuilder v4 is published, I can create a new security branch and we could use that to expand the security features.

If you have any thoughts or ideas on how security should work, please do let me know.

I tried to store usernames and passwords in a sqlite database named Users.db with the columns name, password, salt and scope and use this for validation in security.js. Works fine.
I used better-sqlite3 (install with npm install better-sqlite3, see better-sqlite3 - npm)

/* globals module */
 * Copyright (c) 2020-2021 Julian Knight (Totally Information)
 * https://it.knightnet.org.uk
 * Licensed under the Apache License, Version 2.0 (the 'License');
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an 'AS IS' BASIS,
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * Template security functions for uibuilder.
 * Only used if the node.useSecurity flag is set.
 * Please replace with your own code.
 * You MUST export the following functions:
 *   - userValidate - based on an id, lookup the user data to see if the user is valid.
 *                    MUST return a boolean or object of type userValidation.
 *                    Called from the server's logon process. (uiblib.js::logon)
 * Each function MUST at least return true/false. See each function for more details.
 *   1) IF there is an error in this JavaScript, it is very likely that Node-RED will terminate.
 *   2) You can use different security.js files for different instances of uibuilder.
 *      Simply, place a securiy.js file in the instances root folder (e.g ~/.node-red/uibuilder/<url>/security.js)
 *      Note, however, that this means that the security.js file can be edited using the admin Editor.
 *      You have to restart Node-RED to pick up the new file.
const crypto = require('crypto');
const Database = require('better-sqlite3');
const db = new Database('Users.db', { readonly: true });

let hasher = (password, salt) => {
    let hash = crypto.createHmac('sha512', salt);
    return hash.digest('hex');

'use strict'
/** Define the _auth object
 * @typedef {Object} _auth The standard auth object used by uibuilder security. See docs for details.
 * Note that any other data may be passed from your front-end code in the uibAuth object.
 * @property {String} id Required. A unique user identifier.
 * @property {String} [password] Required for input to login only.
 * @property {String} [jwt] Required if logged in. Needed for ongoing session validation and management.
 * @property {String} [sessionExpiry] Required if logged in. ISO8601 formatted date/time string. Needed for ongoing session validation and management.
 * @property {boolean} [userValidated] Required after user validation. Whether the input ID (and optional additional data from the _auth object) validated correctly or not.
 * @property {Object=} [info] Optional metadata about the user.

module.exports = {
    /** Validate user against your own user data.
     * The minimum input data is _auth.id which must represent a unique ID.
     * Called from the logon function (uiblib.js::logon) which is triggered by a uibuilder control msg from the client of type 'logon'.
     * May also be called to revalidate users at any time.
     * @param {_auth} _auth Required. 
     * @return {_auth} Object of type _auth
    userValidate: function(_auth) {
        // Always start by invalidating the user credentials
        _auth.userValidated = false
	const stmt = db.prepare('SELECT * FROM users WHERE name = ?');
	const row = stmt.get(_auth.id);
	if(hasher(_auth.password,row.salt) === row.password) {
		_auth.userValidated = true;
		_auth.info = {
			scope: row.scope 

	else {
		_auth.userValidated = false;
        return _auth
    } // ---- End of userValidate ---- //



And some nodes to insert a new user to the database, just replace the inject with the output of uibuilder.

[{"id":"2081d735.efc788","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"dafa7001.df887","type":"function","z":"2081d735.efc788","name":"","func":"msg.userdata = msg.payload;\nmsg.payload = msg.userdata.password;\nmsg.secretkey = getSalt();\nreturn msg;\n\nfunction getSalt() {\n    let salt = \"\";\n    while(salt.length < 32) {\n        salt += Math.floor(Math.random() * (256)).toString(16);\n    }\n  return salt;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":140,"wires":[["b0961f24.3cd978"]]},{"id":"e226b8e6.27b2d","type":"sqlite","z":"2081d735.efc788","mydb":"fb8be12d.799708","sqlquery":"msg.topic","sql":"","name":"users RW","x":740,"y":140,"wires":[[]]},{"id":"594fb5e.665954c","type":"function","z":"2081d735.efc788","name":"","func":"\nmsg.topic = \"INSERT INTO users VALUES ('\" \n+ msg.userdata.name + \"','\"\n+ msg.payload + \"','\"\n+ msg.secretkey + \"','\"\n+ msg.userdata.scope + \"')\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":140,"wires":[["e226b8e6.27b2d"]]},{"id":"b0961f24.3cd978","type":"hmac","z":"2081d735.efc788","name":"","algorithm":"HmacSHA512","key":"000","x":430,"y":140,"wires":[["594fb5e.665954c"]]},{"id":"b84cc579.0127e","type":"inject","z":"2081d735.efc788","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"newUser","payload":"{\"name\": \"name\", \"password\": \"password\", \"scope\": \"scope\"}","payloadType":"json","x":150,"y":140,"wires":[["dafa7001.df887"]]},{"id":"fb8be12d.799708","type":"sqlitedb","db":"Users.db","mode":"RW"}]


Though you need to validate the _auth.id because you are passing it straight into your SQL query and you haven't included surrounding quotes in the query parameter.

I would make sure that the id is a valid string, not an empty string, not more than nn chars (choose a reasonable limit) and does not contain anything other than A-Z, a-z and any other characters you think might be needed.

1 Like

By the way, if uibuilder security isn't quite ready for you, you should try using an external proxy such as Caddy or NGINX which have mature authentication and authorisation configs.

A post was split to a new topic: Uibuilder security with MS-SQL

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.