How to build an Access-Control System based on Carriots and Google Sheets

0. Introduction

We wanted to ease the way we access our headquarters. What did we think about? We are always using smartphones so, why should we not use our smartphones to open the doors? Then we put our minds at work.

When we were deciding how we should use the smartphones, we had some things to consider like security, ease of implementation and our technological background. The decision was simple: a web page.

1. Architecture Overview

Which actors are involved in our solution? Here you are:

  • Architecture overview
  • Our stuff: We are the ones demanding the door to get opened.
  • Smartphones / PCs: Any device running a web browser can be used to make a door-opening request.
  • Carriots Platform: Our platform uses some Listeners we have specifically programmed for this project (see https://www.carriots.com/tutorials/create_listener to learn more):
    • Door_opening: This listener is in charge of:
      Determining which door has been asked to be opened.
      Finding out which device opens this door.
      Communicating the door-opening request to the corresponding device.
    • Listener_status: This listener is in charge of:
      Processing the status info sent by the devices.
      Updating the devices configuration info. This configuration info includes the authentication and authorization data which is stored in a Google Spreadsheet document. This listener asks the Google API for the Spreadsheet data (see https://www.carriots.com/tutorials/others_APIs/google-g-suite to learn more) and generates a new Device_config file, if necessary, to be downloaded by the corresponding raspberry device.
    • Access_Log: This listener is in charge of:
      Processing the log data streams sent by the devices.
      Writing this info into a Google Spreadsheet document.
  • Raspberry devices: The ones that decide if the door must be opened after receiving an opening request. These raspberries are running a Raspbian Lite OS (it could be also a Minibian OS) and a software built in python which control the access. The main features are:
    • A device can manage up to 24 doors. We use its GPIO pins plus a relay switch circuit per door. The amount may vary depending on the necessary pins to control the door. In our case only one programmable pin per door is needed.
    • Authorization & authentication control. The device keeps a copy of the authorized users to open each controlled door, and of these user’s credentials. This allows to discriminate whether the user is or is not authorized or if the authentication went wrong or right.
    • Authorized users and their credentials are updatable. The authorized users info is updated automatically.
    • Programmed updates. User’s info is programmed to be updated at a customized time, e.g. once per day or every 30 seconds.
    • Network services monitoring. The device software has been equipped with a watchdog process to detect if the internet connection is working and reconnect the services if necessary.
    • Local & remote logging. The device logs internally all that is happening inside and it also sends the access attempts info to the Carriots Platform.
    • Auto-recovery. The main process is rebooted if it falls down.
    • Expandable. The device software is ready to expand its features easily, like statistics compilation, if necessary.

2. Security

Obviously, we do not want someone entering our headquarters without authorization. How did we solved this? We have a 4 steps solution:

  • Step 1: the web page used for requesting a door-opening is only visible in our local network, so a user trying to connect to our web browser must be connected to our local network in the first place.
  • Step 2: There is one administrator who maintains the authorized users info via a private Google Spreadsheet document.
  • Step 3: The door-opening requests are received only through a private MQTT topic.
  • Step 4: The authentication process, where the user’s credentials stored in the device must meet the credentials sent by the user.

3. Building the Solution

Let us get to work! We have 4 areas to deal with:

  • Data source: We have decided to use a Google Spreadsheet document due to the ease of maintenance it provides.
  • Web Server: As we are using a web page, we have decided to hide how we cipher the user’s credentials and how we map the logical naming of the doors in the web server.
  • Listeners in the Carriots platform: Our platform must process the door-opening requests and we get this done so by creating 3 listeners inside our access-control project.
  • Raspberry devices: The device must:
    • Control a simple relay switch circuit to handle the door lock.
    • Update its configuration info.
    • Authenticate the users and authorize their access.
    • Log what happens.

3.1. Get your data source ready

As mentioned before, we have chosen a shared file via Google as our data source. Why? We did not needed a complicated powerful system to maintain the authorization and user’s credentials information of our stuff.

One person can manage this information without having a deep knowledge of technology and we did not have to develop a back-end application to edit this data.

If you want to follow our steps to get a Google Spreadsheet document working for your project, see this tutorial https://www.carriots.com/tutorials/others_APIs/google-g-suite.

What information should your data source manage?

  • User’s full name
  • User id
  • Password
  • Door ids

In the following sample you can see a Google Spreadsheet containing 5 columns:

  • Users list
  • Name: The users’ full names.
  • User name: The users identifiers.
  • Password: A ciphered password per user identifier.
  • door-1: Boolean (yes/no) representing the user in the row is authorized or not to open door-1.
  • door-2: Boolean (yes/no) representing the user in the row is authorized or not to open door-2.

Note: You could use any data source like Active Directory or a regular relational DB, to get the same functionality. If your company works worldwide or you just already have an authorization system working, you could integrate it with the rest of the architecture.

3.2. Web Page in an Apache Web Server

In the introduction of this document we have explained why we decided to use a web page as the tool for making requests and now it is time we explain how:

  • Web login

The web page is basically a very simple form with the following fields:

  • username: input type=”text”. The user’s name used for validating the door-opening request.
  • password: input type=”password”. The user’s password used for validating the door-opening request.
  • door: input type=”select”. The list of doors that can be opened.: A ciphered password per user identifier.
  • sendForm: input type=”submit”. The button to send the form to the Apache web server.

The source code of the file index.html.twig, the web page, is:

This form is sent, via https, to a mini PHP-Silex application that retrieves this form data, ciphers the password and builds a data stream that is sent to the Carriots Platform:

The source code of the main file, index.php, is:

Index.php gets some help from this file, carriots_functions.php. The source code is:

The development-stage server script to launch our mini application (for faster testing) is:

Note: We do not manage several buildings as we do not have this need, but if you think at a large scale you could map, for example in a DB:

  • The countries where your buildings are.
  • The cities where your buildings are.
  • Your buildings.
  • The floors in each building.
  • The doors in each floor of your buildings.
  • The users having access to these sets of country-city-building-floor-door.

Then you could add these new fields to the form and process them the same way. You could also use cookies for remembering the last selection, so the changes to the previous form could be the username and the password or just the password to ease filling the form and get a quicker access.

Maybe you could think of creating a mobile app instead of a web page if it is more useful to you; or even imagine another way of telling the Carriots Platform (or directly informing, for example via Bluetooth, the door control device of) who you are and what door you want to open.

3.3. Carriots account

You can start by creating a free account for this project and then upgrade it to a corporate account or another special type depending on your needs. You can check the different possibilities here https://www.carriots.com/pricing.

Once your account is created and confirmed, a default entities hierarchy is generated automatically:

  • Carriots Hierarchy

If your username is “freeuser”, all your entities will have a suffix like this @freeuser.freeuser, and the automatically generated entities will be: defaultProject@freeuser.freeuser, defaultService@freeuser.freeuser, defaultGroup@freeuser.freeuser, defaultDevice@freeuser.freeuser

The rest of the entities in the diagram are not mandatory, so they are not created by default.

3.4. Device provisioning

As you are using a device to control the door opening, you must provision this device into your Carriots account:

  • Device provisioning

And activate MQTT communication:

  • MQTT configuration

MQTT protocol is the way we will communicate with the device. See listeners.

3.5. Listeners In The Carriots Platform

Now, we have to create the Listener (or Listeners) to manage the data/status streams we are going to receive from our devices. A Listener must be associated to an event and an entity, for example, we can create a Listener for the whole project that runs when a data stream is received.

  • Carriots Hierarchy

As we explained in the architecture overview, we have created 3 different Listeners for this project: Door_opening, Listener_status and Access_Log which we are going to fully describe in the following pages.

3.5.1. Door_opening

This is the main listener as it is in charge of:

  • Determining which door has been asked to be opened: In our case, the mini PHP web application is sending this info (“door-1” or “door-2”).
  • Finding out which device opens this door: We have one device per door (although it could be up to 24 doors per device, as said in the architecture overview). In our case, the mapping is direct as we are using the door identifier to indentify the device (“door-1@accesscontrol.accesscontrol”).
  • Communicating the door-opening request to the corresponding device: We achieve this by publishing a door-opening request in the MQTT topic bound to the device. The device is subscribed to this topic, so any message published is consumed by the device, but the topic is password protected, so only authorized MQTT clients can publish messages. To learn more about this go to https://www.carriots.com/documentacion/es-carriots-mqtt-broker.

Here is how our Listener is configured at the Carriots Control Panel:

  • Door_opening listener
  • The listener can be invoked by any entity under the Project entity named defaultProject@accesscontrol.accesscontrol, that is the unique project entity that exists in our hierarchy.
  • The listener will be triggered only when a data stream is received.
  • The data stream must include an attribute named “operation” and its value must be “open”.

And here is the Listener’s code made in Java:

3.5.2. Listener_status

This listener deals with status streams and is in charge of:

  • Processing the status info sent by the devices.
  • Updating the devices configuration info. This configuration info includes the authentication and authorization data which is stored in a Google Spreadsheet document. This listener asks the Google API for the Spreadsheet data (see https://www.carriots.com/tutorials/others_APIs/google-g-suite to learn more) and generates a new Device_config file, if necessary, to be downloaded by the corresponding raspberry device.

Here is how our Listener is configured at the Carriots Control Panel:

  • Listener_status listener
  • The listener can be invoked by any entity under the Project entity named defaultProject@accesscontrol.accesscontrol, that is the unique project entity that exists in our hierarchy.
  • The listener will be triggered only when a status stream is received.
  • The status stream must include an attribute named “configFileName” and its value must be “users”.

And here is the Listener’s code made in Java:

3.5.3. Access_Log

This listener is in charge of:

  • Processing the log data streams sent by the devices.
  • Writing this info into a Google Spreadsheet document.

Here is how our Listener is configured at the Carriots Control Panel:

  • Access_log listener
  • The listener can be invoked by any entity under the Group entity named defaultGroup@accesscontrol.accesscontrol, that is the unique group entity that exists in our hierarchy.
  • The listener will be triggered only when a data stream is received and the attribute operation has the value “log”.

And here is the Listener’s code made in Java/Groovy:

The log information in your spreadsheet could be like this:

  • Access_log listener

3.6. Access Control Devices

As mentioned in point 1, we decided to use Raspberry Pi 3 devices to control the access. The reasons were we are familiar with this device type, is low-cost and allowed us to develop the main features we wanted:

  • A device can manage several doors (up to 24). We use its GPIO pins plus a relay switch circuit per door. The amount may vary depending on the necessary pins to control the door. In our case only one programmable pin per door is needed.
  • Authorization & authentication control. The device keeps a copy of the authorized users to open each controlled door, and of these user’s credentials. This allows to discriminate whether the user is or is not authorized or if the authentication went wrong or right.
  • Authorized users and their credentials are updatable. The authorized users info is updated automatically.
  • Programmed updates. User’s info is programmed to be updated at a customized time, e.g. once per day or every 30 seconds.
  • Network services monitoring. The device software has been equipped with a watchdog process to detect if the internet connection is working and reconnect the services if necessary.
  • Local & remote logging. The device logs internally all that is happening inside and it also sends the access attempts info to the Carriots Platform.
  • Auto-recovery. The main process is rebooted if it falls down.
  • Expandable. The device software is ready to expand its features easily, like statistics compilation, if necessary.

To get the code working in your Raspberry Pi, follow these steps:

  • Download the latest Raspbian Lite release as the OS for your Raspberry Pi from https://www.raspberrypi.org/downloads/raspbian/ and install it.
  • Once you are running your raspberry, download paho-mqtt client library and requests library using pip.
    • pip install requests
    • pip install paho-mqtt
  • Download the source code for your raspberry by clicking on the link Access_Control_Source_Code, unzip it and place it at /home/raspberry in your Raspberry Pi board.
  • Edit the file device_vars.py for
    • DEVICE_NAME = "TYPE_THE_DEVICE_NAME_HERE"
    • DEVICE_DOMAIN = "TYPE_THE_DEVICE_DOMAIN_HERE_WITHOUT_@"
    • CARRIOTS_APIKEY = "TYPE_YOUR_FULL_ACCESS_APIKEY_HERE"
    • WIRING = {"TYPE_THE_DEVICE_NAME_HERE": TYPE_THE_GPIO_PIN_NUMBER_TO_OPEN_THE_LOCK}
    • GATE_TIME = 2 # Number of seconds to keep the lock open
    • VERSION_LABEL = "*version*"
    • CREDENTIALS_DEVICE_FILE = "users"
    • CREDENTIALS_FILE = "files/user_credentials.dict"
    • LOG_MODE = "w"
    • LOG_COUNT = 10
    • LOG_SIZE = 1024**2 # 1MB
    • LOG_LEVEL = INFO
    • LOG_FILE = "logs/gate.log"
    • LOG_FORMAT = '%(asctime)s - %(levelname)-8s %(threadName)-10s: %(message)s'
    • STATISTICS_INTERVAL = 120 #Seconds interval to send a status stream to Carriots
    • WATCHER_INTERVAL = 20
    • TEST_TARGET = 'http://www.google.es'
    • MQTT_USERNAME = 'TYPE_THE_DEVICE_MQTT_USERNAME'
    • MQTT_PASSWORD = 'TYPE_THE_DEVICE_MQTT_PASSWORD'
    • MQTT_TOPPIC = 'TYPE_THE_DEVICE_MQTT_TOPIC_NAME'
3.6.1. Electric circuit

In this tutorial everything you need to build your relay switch is detailed:

https://www.carriots.com/tutorials/raspberrypi_carriots/raspberry-and-relay