emq_auth_mysql: MySQL 认证/访问控制插件

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/wangshuminjava/article/details/80579883

emq_auth_mysql: MySQL 认证/访问控制插件

MySQL 认证/访问控制插件，基于 MySQL 库表认证鉴权: https://github.com/emqtt/emq-auth-mysql

MQTT 用户表

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=MyISAM DEFAULT CHARSET=utf8;

注解

MySQL 插件可使用系统自有的用户表，通过 ‘authquery’ 配置查询语句。

MQTT 访问控制表

CREATE TABLE `mqtt_acl` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `allow` int(1) DEFAULT NULL COMMENT '0: deny, 1: allow',
  `ipaddr` varchar(60) DEFAULT NULL COMMENT 'IpAddress',
  `username` varchar(100) DEFAULT NULL COMMENT 'Username',
  `clientid` varchar(100) 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=utf8;

INSERT INTO </span><span class="n">mqtt_acl</span><span class="o" style="color:rgb(102,102,102);"> (</span><span class="n">id</span><span class="o" style="color:rgb(102,102,102);">, </span><span class="n">allow</span><span class="o" style="color:rgb(102,102,102);">, </span><span class="n">ipaddr</span><span class="o" style="color:rgb(102,102,102);">, </span><span class="n">username</span><span class="o" style="color:rgb(102,102,102);">, </span><span class="n">clientid</span><span class="o" style="color:rgb(102,102,102);">, </span><span class="k" style="color:rgb(0,112,32);font-weight:bold;">access</span><span class="o" style="color:rgb(102,102,102);">, </span><span class="n">topic</span><span class="o" style="color:rgb(102,102,102);">)
VALUES
(1,1,NULL,‘KaTeX parse error: Expected 'EOF', got '#' at position 261: …64,112,160);">'#̲'</span><span c…all’,NULL,1,‘KaTeX parse error: Expected 'EOF', got '#' at position 5: SYS/#̲'</span><span c…all’,NULL,1,‘eq #’),
(5,1,‘127.0.0.1’,NULL,NULL,2,‘KaTeX parse error: Expected 'EOF', got '#' at position 5: SYS/#̲'</span><span c…SYS/#’);

配置 MySQL 认证鉴权插件

etc/plugins/emq_auth_mysql.conf:

## Mysql Server

auth.mysql.server = 127.0.0.1:3306

## Mysql Pool Size
auth.mysql.pool = 8

## Mysql Username
## auth.mysql.username =

## Mysql Password
## auth.mysql.password =

## Mysql Database
auth.mysql.database = mqtt

## Variables: %u = username, %c = clientid

## Authentication Query: select password only
auth.mysql.auth_query = select password from mqtt_user where username = ‘%u’ limit 1

## Password hash: plain, md5, sha, sha256, pbkdf2
auth.mysql.password_hash = sha256

## %% Superuser Query
auth.mysql.super_query = select is_superuser from mqtt_user where username = ‘%u’ limit 1

## ACL Query Command
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’

加载 MySQL 认证鉴权插件

./bin/emqttd_ctl plugins load emq_auth_mysql