VerneMQ says Hi to PostgreSQL

We've got asked a couple of times about how to do authentication and authorization with a database like PostgreSQL or Redis. "Easy, write a plugin!", has been our standard answer. But without a proper plugin development guide, this is easier said than done. Brave folks already checked out the VerneMQ Demo Plugin repo on Github that gives all the boilerplate needed together with some explanations on how to implement the hooks. To provide another plugin example, we developed a PostgreSQL plugin that handles the authentication and authorization of clients. (It's under 100 LoC and was done in about 2 hours this morning). While this plugin is fully functional it isn't built for production and should only be used for educational purposes. Hint: we can make this production ready in a day or two, if you need it ;)

Architecture

The plugin is implemented as an Erlang OTP application. It relies on the great Erlang Postgres client implementation epgsql and uses the poolboy library for connection pooling. To talk to PostgreSQL using epgsql and poolboy we just adjusted the example from the poolboy readme.

Database Schema

A pretty naive database schema is used in the plugin; there's certainly much room to improve this if necessary, including the implemented queries. Note: plaintext password... hash & salt for production use please. You have been warned ;) The schema consists of three tables:

er

Note that the table names are chosen after the hooks they relate to. This is just to make things a little clearer.

Implementing the Hooks

VerneMQ currently provides three hooks, concerned with authentication and authorization:

A plugin isn't required to implement all of them. The default vmq_acl  plugin only implements the auth_on_register hook for example. For our PostgreSQL example it certainly makes sense to implement all 3 hooks in one plugin.

auth_on_register

As you see, the plugin uses 'SELECT COUNT' to obtain the number of rows, as the content is not needed itself. If the result of the query is a count of 1 the plugin is allowed to return 'ok'. This indicates to the underlying plugin system that the client was authenticated successfully . For any other query result the plugin returns 'next'. This tells VerneMQ to look for (and try) other auth plugins for this user. 

auth_on_publish

Auth_on_publish uses 'SELECT COUNT' in a similar way. In this case we allow the plugin to check if the client is allowed to publish retained messages, and also whether the message size and chosen quality of service level is appropriate or not.

auth_on_subscribe

Just obtaining the resulting row count isn't enough for the auth_on_subscribe hook. Here the plugin has to verify that every provided topic/qos pair is stored in the database. Instead of creating multiple queries we fold through the result set.

Conclusions

We hope you'll agree that it's quite simple to get started implementing an authorization plugin with a third-party database. Let us know, if any questions remain. Further notes on how to compile and start the plugin can be found on its github repo. To make this more production ready, we'd have to address at least the following points:

  • Security: SQL injection, password handling
  • Wildcard matching support in topics
  • Caching and cache invalidation (currently every rule validation (every single publish for instance) hits the database)
  • Better configuration handling

Have fun!

The VerneMQ Mission Engineers.

VerneMQ MQTT PostgreSQL