sqlite.remote version 1.0

March 22, 2020

A while back I released some simple code to allow access to a SQLite database on my embedded devices. A python/html5 app that provided an HTML5 interface to a python api. My goal is a simple to use method that allows quick and easy access to  databases on raspberry pi (or similar) used for configuration and logging on my IoT devices. The requirements are minimal :

  1. User login via http auth
  2. The ability to change a user password.
  3. SSL via self signed certificates
  4. Basic database access via a browser based app

Today version 1.0 of the sqlite.remote tool is being released. Available via git hub, the app meets the goals specified above.

Some assembly is required — mainly in the form of generating self signed certificates for the devices and browsers that will be used to access them. Check out my previous post about securing local IoT devices.  Generate the certs that you need before installing the sqlite.remote tool.

Installation:

Since I use this on the Raspberry PI it is installed in the pi user’s home directory. Any system that supports python should work, as should any user.

First clone the git hub repository:

 git clone https://github.com/m2ag-labs/m2ag-sqlite-remote.git

Then cd to the install directory and chmod the installer script:

chmod +x install.sh

Then ./install.sh to install dependencies. This app requires flask and flask_httpauth. Depending on your current update status the dependencies may already be installed.

After the install is completed the install can be tested by changing to the project root and running:

python3  api.py sqlite-remote.sqlite

This should start the api server and show a  message like this in the console:

As you can see we are using the barebones development server to run our api. Despite the message to not use this in production, I am using this in production. My usage only calls for one or two users at a time infrequently hitting the api. I decided to trade a simple implementation for a more robust web server and more complicated install. Plus, the web server would generally sit idle for day to day use.

It should also be noted that the api is being served over https. This is requires a self signed certificate to be installed on the device. This is configured at the bottom of api.py:

Adjust the paths to the correct certificate files. The api can be run without ssl if desired. For me, I want to provide at least a minimal level of security to my IoT devices. I intend this to be a config and control interface, but the devices will generally be inside a controlled firewall. Self signed certs  and a basic login meets my needs.

As part of the install a service a file is and copied to the systemd system directory. If the default location (/home/pi/m2ag-sqlite-remote) is changed, change it in this file.  To enable the service:

sudo systemctl m2ag-sqlite-remote enable
and 
sudo systemctl m2ag-sqlite-remote start

After the api is running navigate to your device at:

https://your-device.local:5001  to get the app page:

The default user is “pi” and the password is “raspberry”. These can be set in the credentials popup:

Select close to get back to query screen. If the query button is pressed with an empty query an “Ok” message will be returned. This tells us the configuration is correct:

To change the password use e the credentials popup:

‘Ok’ will appear in the status on a successful change.

To add new users first select * from users and get the hash for pi’s password and then insert a user using the same password hash. You can then use the new user’s credentials and change the password with the set password dialog.

General usage:

This app should handle most updates and edits to tables. Since the services setup specifies the database at startup additional tables to be added to the sqlite-remote.sqlite database. The only requirement for the app is the user table with a text username and password fields. This table could be added to any database. Just insert the pi user (or any other user) into the user tables via the command line app or some other tool.

It is hoped that you find this app useful, please feel free to open issues on github or comment here if there are problems with the app. Keep in mind it is barebones on purpose, all I need it to do is update tables remotely for me. If more complex usage is envisioned it may be modifications will need to be made.

If you find this work helpful perhaps you would consider supporting m2ag.labs open source efforts by buying us coffee. Any amount would be appreciated. Please use this link to do so:

Small Donate Button

Thanks for stopping by.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.