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

最新推荐文章于 2025-07-04 10:32:38 发布

u011024421

最新推荐文章于 2025-07-04 10:32:38 发布

阅读量1.3k

点赞数

分类专栏： emq 物联网 mqtt

emq 同时被 3 个专栏收录

7 篇文章

订阅专栏

物联网

7 篇文章

订阅专栏

mqtt

7 篇文章

订阅专栏

本文介绍如何使用EMQ MQTT Broker与MySQL数据库结合，实现用户认证和访问控制。详细展示了MySQL用户表和访问控制表的创建过程，以及如何配置EMQ插件进行鉴权。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 mqtt_acl (id, allow, ipaddr, username, clientid, access, topic)
VALUES
(1,1,NULL,‘ $KaTeX parse error: Expected 'EOF', got '#' at position 261: \dots64,112,160);">'#̲'<span c\dots$ 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’