EMQ X Auth & ACL Based on MySQL

Before reading this tutorial, assume you already know it. MQTT,MQTT 5 And EMQ X Simple knowledge.

emqx-auth-mysql It achieves access control to terminals by checking whether the username and password accessed by each terminal are consistent with the information stored in user-specified MySQL database. Its function logic is as follows:

2401557472472_.pic_hd.jpg

This article assumes that MySQL has been installed on your machine and that you can connect to the MySQL server. Note: EMQ X Open Source Edition does not support MySQL 8.0 until v3.1-beta.2, so the following content only applies to MySQL 5.7 and below.

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.25, for macos10.14 (x86_64) using  EditLine wrapper

Plug-in Configuration Item Description

mqtt.sql

emqx-auth-mysql provides mqtt.sql files to help users quickly create data tables and import default data. Mqtt.sql will import the following default rules for the mqtt_acl data table:

mysql> select * from mqtt_acl;
+----+-------+-----------+-----------+----------+--------+--------+
| id | allow | ipaddr    | username  | clientid | access | topic  |
+----+-------+-----------+-----------+----------+--------+--------+
|  1 |     1 | NULL      | $all      | NULL     |      2 | #      |
|  2 |     0 | NULL      | $all      | NULL     |      1 | $SYS/# |
|  3 |     0 | NULL      | $all      | NULL     |      1 | eq #   |
|  4 |     1 | 127.0.0.1 | NULL      | NULL     |      2 | $SYS/# |
|  5 |     1 | 127.0.0.1 | NULL      | NULL     |      2 | #      |
|  6 |     1 | NULL      | dashboard | NULL     |      1 | $SYS/# |
+----+-------+-----------+-----------+----------+--------+--------+
6 rows in set (0.00 sec)

allow - 1: allow; 0: deny

access - 1: subscribe; 2: publish; 3: publish and subscribe

The above rules indicate that:

• Allow any user to publish messages on any topic other than the'$'character
• Refuse any user to subscribe to any theme that starts with "$SYS/"
• Refuse any user to subscribe to the # theme
• Allow native users to publish any theme starting with "$SYS/"
• Allow native users to publish messages on any topic other than the'$'character
• Allow dashboard users to subscribe to any theme starting with "$SYS/"

In addition, users can import custom ACL rules.

Auth and ACL Functional Verification

1. Installation of mosquitto in Mac environment

   brew install mosquitto

2. Create a database and import data

   The mqtt.sql path can be changed by itself according to the actual situation.

   mysql> create database mqtt;
   mysql> use mqtt;
   mysql> source ./emqx_auth_mysql/mqtt.sql
   mysql> insert into mqtt_user (id, is_superuser, username, password, salt)
       -> values (1, false, 'test', 'password', 'salt');
   mysql> insert into mqtt_acl (id, allow, ipaddr, username, clientid, access, topic)
       -> values (7, 0, NULL, 'test', NULL, 1, 'mytopic');
   mysql> exit;
   

3. Modify configuration files

   Anonymous access is prohibited:

   ## .../etc/emqx.conf
   allow_anonymous = false
   

   The password in the configuration database is encrypted by plain, i.e. not encrypted:

   ## .../etc/plugins/emqx_auth_mysql.conf
   auth.mysql.password_hash = plain
   

   Configure the database to be accessed and the username password:

   ## .../etc/plugins/emqx_auth_mysql.conf
   auth.mysql.username = root
   auth.mysql.password = public
   auth.mysql.database = mqtt
   

4. Start EMQ X and emqx-auth-mysql

   $ ./_rel/emqx/bin/emqx start
   emqx 3.1 is started successfully!
   $ ./_rel/emqx/bin/emqx_ctl plugins load emqx_auth_mysql
   

1. test

   1. Connect with the correct username and password and subscribe to the topic

      $ mosquitto_sub -p 1883 -u test -P password -t 'topic' -d
      Client mosqsub|91114-zhouzibod sending CONNECT
      Client mosqsub|91114-zhouzibod received CONNACK
      Client mosqsub|91114-zhouzibod sending SUBSCRIBE (Mid: 1, Topic: topic, QoS: 0)
      Client mosqsub|91114-zhouzibod received SUBACK
      Subscribed (mid: 1): 0
      

      Phenomenon: Successful connection and subscription

   2. Connect with the wrong username or password and subscribe to the topic

      $ mosquitto_sub -p 1883 -u bad_user -P password -t 'topic' -d
      Client mosqsub|91136-zhouzibod sending CONNECT
      Client mosqsub|91136-zhouzibod received CONNACK
      Connection Refused: not authorised.
      

      Phenomenon: Connection rejected

   3. Connect with the correct username and password and subscribe to the # theme

      $ mosquitto_sub -p 1883 -u test -P password -t '#' -d
      Client mosqsub|11257-zhouzibod sending CONNECT
      Client mosqsub|11257-zhouzibod received CONNACK
      Client mosqsub|11257-zhouzibod sending SUBSCRIBE (Mid: 1, Topic: #, QoS: 0)
      Client mosqsub|11257-zhouzibod received SUBACK
      Subscribed (mid: 1): 128
      

      Phenomenon: Successful connection, unsuccessful subscription, reason code 128

   4. Connect with the correct username and password and subscribe to the "mytopic" topic

      $ mosquitto_sub -p 1883 -u test -P password -t 'mytopic' -d
      Client mosqsub|13606-zhouzibod sending CONNECT
      Client mosqsub|13606-zhouzibod received CONNACK
      Client mosqsub|13606-zhouzibod sending SUBSCRIBE (Mid: 1, Topic: mytopic, QoS: 0)
      Client mosqsub|13606-zhouzibod received SUBACK
      Subscribed (mid: 1): 128
      

      Phenomenon: Successful connection, unsuccessful subscription, reason code 128

For more information, please visit our website. emqx.io Or focus on our open source projects github.com/emqx/emqx For detailed documentation, please visit Official Documents.