emqx 添加 mysql 插件进行acl验证

 

1、新建表

CREATE TABLE `mqtt_user` (
　　`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
　　`username` varchar(100) DEFAULT NULL,
　　`password` varchar(100) DEFAULT NULL,
　　`salt` varchar(35) DEFAULT NULL,
　　`is_superuser` tinyint(1) DEFAULT 0,
　　`created` datetime DEFAULT NULL,
　　PRIMARY KEY (`id`),
　　UNIQUE KEY `mqtt_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

2、插入用户密码

INSERT INTO `mqtt_user` ( `username`, `password`, `salt`, `is_superuser`) VALUES ('emqx_iot_user', '00000000', NULL, 0);

 

3、创建acl表

CREATE TABLE `mqtt_acl` (
　　`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
　　`allow` int(1) DEFAULT 1 COMMENT '0: deny, 1: allow',
　　`ipaddr` varchar(60) DEFAULT NULL COMMENT 'IpAddress',
　　`username` varchar(100) DEFAULT NULL COMMENT 'Username',
　　`clientid` varchar(200) DEFAULT NULL COMMENT 'ClientId',
　　`access` int(2) NOT NULL COMMENT '1: subscribe, 2: publish, 3: pubsub',
　　`topic` varchar(100) NOT NULL DEFAULT '' COMMENT 'Topic Filter',
　　PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

4、插入acl信息

这里插入 '$SYS/#' 是系统下所有topic，单独的 # 是不包含系统topic的。

INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES
    (1, NULL, 'emqx_iot_user', 'emqx_001_PE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_001_PE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_002_PE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_002_PE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_003_PE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_003_PE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_004_PE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_004_PE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_001_SE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_001_SE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_002_SE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_002_SE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_003_SE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_003_SE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_004_SE', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_004_SE', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_01', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_01', 3, '#'),
    (1, NULL, 'emqx_iot_user', 'emqx_02', 3, '$SYS/#'),
    (1, NULL, 'emqx_iot_user', 'emqx_02', 3, '#')
;

 

5、配置认证鉴权插件

# vi etc/plugins/emqx_auth_mysql.conf

 

修改：

auth.mysql.server = 127.0.0.1:3333
auth.mysql.username = root
auth.mysql.password = emq@iot1201
auth.mysql.database = iot 【修改默认数据库
auth.mysql.password_hash = plain 【这里修改成明文，默认是 sha245，咱们的密码是已经加密后的。】
## auth.mysql.super_query = select is_superuser from mqtt_user where username = '%u' limit 1 【屏蔽超管】

 

 

6、启动emqx_mysql 插件

# ./bin/emqx restart
# ./bin/emqx_ctl plugins load emqx_auth_mysql

 

7、查看开启默认加载

# vi data/loaded_plugins

结尾是：

{emqx_auth_redis,true}.
{emqx_auth_mysql,true}.