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

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

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 Provided mqtt.sql Files help users quickly create data tables and import default data. mqtt.sql Will be mqtt_acl The data table imports the following default rules:

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 official website emqx.io Or focus on our open source projects github.com/emqx/emqx For detailed documentation, please visit Official Documents.