Goodbye thingsboard – welcome grafana Part 2

Today I’ll cover some basics of InfluxDB and some security aspects of our MQTT deployment and how to implement them.
I’ll scratch retention policies so that your DB doesn’t grow too large and how to enable user auth on mosquitto, influx and telegraf.

Retention Policy (RP)

Retention policies are per-database and define the time data is stored in the database.
You can have multiple RPs in a database. In our specific use case we create 2 RP. Since we later create a CQ and that CQ does only run on new data we start with a fresh database.

> CREATE DATABASE "weatherstation"
> CREATE RETENTION POLICY "14_days" ON "weatherstation" DURATION 14d REPLICATION 1 DEFAULT
> CREATE RETENTION POLICY "10_years" ON "weatherstation" DURATION 10y REPLICATION 1

What we aim for is

  • to keep raw sensor data in the database for 14 days
  • to keep aggregated sensor data in the database for 10 years
  • by setting a new default RP we switch the database from “keep-everything” to the newly created policy

A little bit of math:

Currently we publish 9 values every 300 seconds. Let these be 64bit integers and a timestamp so let’s say 640bit. That amounts to (24*3600) / 300 * 640 = 184320Bit/d or 23KByte per day. That makes 322KByte in our 14day retention period.

Continuous queries (CQ)

CQs run periodically on Influx and can transform data. Think of them like a cron job.

> CREATE CONTINUOUS QUERY "cq_30m" ON "weather" BEGIN
  SELECT mean("humidity") AS "mean_humidity",mean("temperature") AS "mean_temp"
  INTO "10_years"."aggregated_data"
  FROM "weather"
  GROUP BY time(30m)
END

This fetches temperature and humidity every 30 minutes from our live table and puts the 30min average (should be 6 values when publishing every 300secs) into a table aggregated data under our 10year policy.
If you’re by any chance familiar with zabbix – this behaviour would equal to zabbix’ trends.

So after 10 years our influxDB would just be 14MByte in size.

Something you might consider if you’re handling a lot of data. For my small weather station I’ll go with infinite storage time.

InfluxDB Security

By default, auth is disabled.

Bind influx to localhost, enable auth and add some users.

# send usage stats to influxdata every 24h
# up to you
reporting-disabled = true
[http]  
  enabled = true  
  bind-address = "localhost:8086"  
  auth-enabled = true # ‚ú®
  log-enabled = true  
  write-tracing = false  
  pprof-enabled = false  
  https-enabled = false  
  https-certificate = "/etc/ssl/influxdb.pem"  
CREATE USER admin WITH PASSWORD 'secret' WITH ALL PRIVILEGES
CREATE USER bernd WITH PASSWORD 'secretz'
GRANT [READ,WRITE,ALL] ON <dbname> TO bernd
SHOW GRANTS FOR <dbname>
SET PASSWORD FOR bernd = ''
DROP USER bernd

Be sure to edit telegraf.conf

Mosquitto & PubSubClient

Edit mosquitto.conf

password_file /etc/mosquitto/passwd
allow_anonymous false
port 1234

and enable basic authentication.
While you’re here you might as well set your listener to some non default port (“Security through obscurity”)

Create a password file

mosquitto_passwd -c /etc/mosquitto/passwd bernd

and add some users. Be sure to at least add one for each device your running and one for telegraf or your MQTT<->InfluxDB bridge of choice.
We’ll need these in the ACL in a minute.

Update your PubSubClient code accordingly. (hint: some code examples use an AuthToken which is basically the username with no password)
Enable topic ACLs in mosquitto.conf

acl_file /etc/mosquitto/aclfile.example
# everyone w/ username can read $SYS
topic read $SYS/#
# user bernd can readwrite v1/#
user bernd
topic v1/#
# substitution: %u becomes username, %c becomes clientID
# so, i.e every client gets it's own topic subtree unter v1/telemetry
pattern readwrite v1/telemetry/%c/#

See Steves internet guide