search

emqx / emqx-auth-mysql

Authentication, ACL with MySQL Database
https://www.emqx.com
Apache License 2.0
56 stars 56 forks source link

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

Open churm6044 opened 5 years ago

churm6044 commented 5 years ago

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(qa001@gmail.com) and username without dollar sign($MAIL$qa01@gmail.com).

  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 | $MAIL$qa01@gmail.com    | ******************* | NULL |            0 | NULL    |
|  4 | qa001@gmail.com         | ******************* | NULL |            0 | NULL    |
+----+-------------------------+---------------------+------+--------------+---------+

  2. Insert acl into mqtt_acl table. (Use username: $MAIL$qa01@gmail.com)

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

  3. Use EMQ Dashboard tools(Websocket) to connect as $MAIL$qa01@gmail.com 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:$MAIL$qa01@gmail.com 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: qa001@gmail.com)

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

  5. Use EMQ Dashboard tools(Websocket) to re-connect as qa001@gmail.com 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:qa001@gmail.com returncode:0

[2019-07-04 02:49:45][on_message_publish] clientid:mqttjs_96dcbb3099, username:qa001@gmail.com, 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:qa001@gmail.com, 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 = :3306 auth.mysql.pool = 8 auth.mysql.username = auth.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 commented 5 years ago

Hi, @Gilbert-Wong, Can you help me to solve this problem?

HJianBo commented 5 years ago

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 commented 5 years ago

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 ?