Access Control Setup Part 1

The access control setup for my NodeRed telegram bot isn't intended to be an all singing & dancing affair. Adding/removing/changing people isn't something that I expect to do much, so it's functional & a bit clunky. You can improve on it I'm sure.

The first thing that needs setting up is a database so that we can keep track of the users, their permission level to run commands & the permission level of the commands themselves

Step 1: Install the NodeRed mysql module (to be able to use the database that we set up in the docker stack

Then set up the configuration (nodereddb being the name that we set in the stack configuration for the database).

That gives us a database, but we need to fill it with the information that we need, which is where things get clunky. Instead of a lovely user friendly interactive interface it's just a way to feed the important parts of commands into the SQL interface. I'll show you the layout then go through it...

Basically, create the database then add a user & their permissions & in a different table the commands & permission level for the bot to respond to.

Create the database

Pretty much everything that feeds into the database is an inject node. I'll walk you through this one & then you can just change the important bits for the other nodes.

Drag an inject node from the palette, connect it to the database node & set the configuration of this inject node to deliver the topic of "create database nodered" to the mysql node. This command creates the database, but that's all. We'll need the tables within that database so that we can use them! That comes next

Click the button on the inject node to send this command to the database. If you put a debug node on the output of the database node you'll be able to see the resulting output.

Next steps - add the tables. Create another couple of inject nodes & set them with the topics as shown

CREATE TABLE users ( chatId INT(20) PRIMARY KEY, UserName VARCHAR(30), Permission INT(3))

CREATE TABLE command ( command VARCHAR(20) PRIMARY KEY, permission INT(3))

Click the inject node buttons for these two nodes & now we've got a database with 2 tables in in. But no content....

Let's start with the users. Add another inject node & set the data that's pushed to the database so that we add a user, with the right information

In the box below are the command for the .topic & the msg. elements for each of the lines that you can see. The chatId & Username are the ones that belong to the telegram user that you want to add to the database as a recognised user. We'll explain the permission number a little later, so don't start adding users quite yet...

INSERT into users (`chatId`, `UserName`, `Permission`) VALUES (:chatId, :UserName, :Permission )
payload.chatId
payload.UserName
payload.Permission

So, if I want to add a user with a UserName of Fred & a chatId of 5463728 then those are the values that I set for the msg.payload.UserName & msg.payload.chatId field. (You may have to ask them to run /getID & let you have the result (there are other ways, but let's keep it simple).

Permissions

0=blocked
1=public questions
2=safe, but consumable (such as /comic where the image is "spent" after it has been viewed)
4=safe, but potentially disclosing (such as cat photos (which are family only))
8=unsafe (such as car location. Most people don't need to know)
16=Superuser

You'll note that this is basically just bits being set in a binary representation of the number. You can also see that there are no real use cases for intermediate permission levels. i.e a level of 4 implies that 1 & 2 are allowed as well.

A full list of permission levels that will be used is therefore: 0,1,3,7,15,31

Adding a User

Set the UserName, chatId & permission level & then deploy the node. Inject it into the database & you're done!

Updating User Permissions

You're quite likely to want to do this at some point. You could delete them & add them with new levels, so you could create another inject node to do the job. We set chatId as the primary key, so the topic of the injected message (using our hypothetical Fred user & letting him into the family inner circle where he can see photos of the cats) is shown below.

UPDATE users SET Permission = 7 WHERE chatId = 5463728

I've forgotten what users & permissions I added

Time for another inject node, & a debug node attached to the database (see the node diagram all the way up the top of this page)

The next section on access control is here.