Username within dollar sign($) can't pass ACL verify

[churm6044]

Environment

	Version
OS	CentOS Linux release 7.6.1810 (Core)
Erlang/OTP	20
emqttd	2.3.6
MySQL	5.6.40

Description

Use plugins/emqx_auth_mysql to authentication and ACL verify with MySQL Database.
But username within dollar sign($) can't pass ACL verify.

Please follow testing steps below, use username within dollar sign([email protected]) and username without dollar sign($MAIL$[email protected]).

1. Insert user into mqtt_user table.

mysql> select * from mqtt_user;
+----+-------------------------+---------------------+------+--------------+---------+
| id | username                | password            | salt | is_superuser | created |
+----+-------------------------+---------------------+------+--------------+---------+
|  1 | server                  | ******************* | NULL |            1 | NULL    |
|  2 | cline                   | ******************* | NULL |            0 | NULL    |
|  3 | [email protected]    | ******************* | NULL |            0 | NULL    |
|  4 | [email protected]         | ******************* | NULL |            0 | NULL    |
+----+-------------------------+---------------------+------+--------------+---------+

2. Insert acl into mqtt_acl table. (Use username: [email protected])

mysql> select * from mqtt_acl;
+----+-------+--------+-----------------------+-----------+--------+-------------------------------------------+
| id | allow | ipaddr | username              | clientid  | access | topic                                     |
+----+-------+--------+-----------------------+-----------+--------+-------------------------------------------+
| 13 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/#                         |
| 14 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/resp                      |
| 15 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/job                       |
| 16 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/activeuser                |
| 17 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/#                         |
| 18 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/resp                      |
| 19 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/job                       |
| 20 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/activeuser                |
| 26 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/device/30258/#            |
| 27 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/device/30258/status       |
| 28 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/device/30258/#            |
| 29 |     1 | null   | [email protected]  | null      |      1 | out/ugroup/10918/device/30258/status       |
| 30 |     1 | null   | [email protected]  | null      |      2 | out/ugroup/10918/device/30258/status       |
| 31 |     1 | null   | [email protected]  | null      |      2 | out/ugroup/10918/device/30258/status       |
+----+-------+--------+-----------------------+-----------+--------+-------------------------------------------+

3. Use EMQ Dashboard tools(Websocket) to connect as [email protected] and attempt to subscribe topic out/ugroup/10918/#.
   The log /opt/ramdisk/emqttd/log/erlang.log.1 shows the client can not subscribe.

[2019-07-04 02:50:56][on_client_connected] clientid:mqttjs_96dcbb3099, username:[email protected] returncode:0
02:51:07.076 [error] Client(mqttjs_96dcbb3099@60.*.*.*:49328): Cannot SUBSCRIBE [{<<"out/ugroup/10918/#">>,[{qos,0}]}] for ACL Deny

4. Change username without dollar sign($) in mqtt_acl table. (Use username: [email protected])

mysql> update mqtt_acl set username = '[email protected]';
mysql> select * from mqtt_acl;
+----+-------+--------+-----------------------+-----------+--------+-------------------------------------------+
| id | allow | ipaddr | username              | clientid  | access | topic                                     |
+----+-------+--------+-----------------------+-----------+--------+-------------------------------------------+
| 13 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/#                         |
| 14 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/resp                      |
| 15 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/job                       |
| 16 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/activeuser                |
| 17 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/#                         |
| 18 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/resp                      |
| 19 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/job                       |
| 20 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/activeuser                |
| 26 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/device/30258/#            |
| 27 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/device/30258/status       |
| 28 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/device/30258/#            |
| 29 |     1 | null   | [email protected]       | null      |      1 | out/ugroup/10918/device/30258/status       |
| 30 |     1 | null   | [email protected]       | null      |      2 | out/ugroup/10918/device/30258/status       |
| 31 |     1 | null   | [email protected]       | null      |      2 | out/ugroup/10918/device/30258/status       |
+----+-------+--------+-----------------------+-----------+--------+-------------------------------------------+

5. Use EMQ Dashboard tools(Websocket) to re-connect as [email protected] and attempt to subscribe topic out/ugroup/10918/#. Subscribe success.
   And publish topic out/ugroup/10918/device/30258/status success.

[2019-07-04 02:49:17][on_client_connected] clientid:mqttjs_96dcbb3099, username:[email protected] returncode:0


[2019-07-04 02:49:45][on_message_publish] clientid:mqttjs_96dcbb3099, username:[email protected], topic:out/ugroup/10918/device/30258/status, payload:1, qos:0, retain:false
[2019-07-04 02:49:45][on_message_delivered] clientid:mqttjs_96dcbb3099, username:[email protected], topic:out/ugroup/10918/device/30258/status, payload:1, qos:0, retain:false

There is /etc/plugins/emq_auth_mysql.conf setting:

##--------------------------------------------------------------------
## MySQL Auth/ACL Plugin
##--------------------------------------------------------------------

auth.mysql.server = <MySQL Hostname>:3306
auth.mysql.pool = 8
auth.mysql.username = <MySQL Username>
auth.mysql.password = <MySQL Password>
auth.mysql.database = mqtt

## Authentication query.
auth.mysql.auth_query = select password from mqtt_user where username = '%u' limit 1

## Password hash.
auth.mysql.password_hash = sha256

## Superuser query.
auth.mysql.super_query = select is_superuser from mqtt_user where username = '%u' limit 1

## ACL query.
auth.mysql.acl_query = select allow, ipaddr, username, clientid, access, topic from mqtt_acl where ipaddr = '%a' or username = '%u' or username = '$all' or clientid = '%c'

---

We have no idea why, is username within dollar sign cause?
If need more information, please tell me.
Thanks.

---

2019/07/08 12:18(UTF+8)

Update the mqtt topic format in table mqtt_acl at this issue.
topic format most be out/ugroup/<UserID>/<Name> or our/ugroup/<UserID>/device/<DeviceID>/<Name>.

[churm6044]

Hi, @gilbert-wong,
Can you help me to solve this problem?

[HJianBo]

Hi, @churm6044 The emqx_auth_mysql plugin does not be check a username prefixed with the $ sign.
So, It will fallback to the default ACL rules acl.conf or mqtt.acl_nomatch option.

[holopekochan]

Hi, @churm6044 The emqx_auth_mysql plugin does not be check a username prefixed with the $ sign.
So, It will fallback to the default ACL rules acl.conf or mqtt.acl_nomatch option.

mqtt.acl_nomatch, is this option same as acl_nomatch option in /etc/emqx/emqx.config ?