EMQ X MySQL 8.0 topic publish / subscribe authentication

brief introduction

Publish subscribe ACL refers to the permission control of publish / subscribe operations. EMQ X can configure authenticated data sources (built-in data sources and external databases) in the form of plug-ins. When the client subscribes to a Topic and publishes a message, the plug-in realizes the publishing and subscription permission management of the client by checking whether the target Topic is in the allowed / prohibited list of the specified data source.

ACL authentication chain

When multiple ACL conditions are enabled at the same time, EMQ X will perform chain authentication according to the opening sequence of plug-ins:

  • Once authorized, terminate the chain and allow the client to pass authentication

  • Once authorization fails, terminate the chain and prohibit the client from passing authentication

  • Until the last ACL plug-in fails, according to

    Default authorization

    Configuration decision

    • When the default authorization is allow, the client is allowed to pass the authentication
    • When the default authorization is prohibited, the client is prohibited from passing authentication

The client can have the identity of "super user", and the super user has the highest permission and is not restricted ACL Restrictions.
	After the super user function is enabled in the authentication plug-in, when publishing and subscribing EMQ X The client superuser identity will be checked first
	When the client is a superuser, authorize and skip subsequent operations ACL inspect

Authorization results

Any ACL authorization will eventually return a result:

  • Allow: after checking, the client is allowed to operate
  • Prohibit: after checking, the client operation is prohibited
  • ignore: the ACL permission information is not found. It is impossible to explicitly judge whether the result is allowed or prohibited. It is up to the next ACL plug-in or the default ACL rule to judge

ACL rule table (data table)

stay EMQ X MySQL 8 certification Based on

mqtt_acl table field description:

  • Allow: disable (0), allow (1)
  • ipaddr: set the IP address. If it is NULL, it means that all addresses are matched
  • username: the user name of the connecting client. If the value here is set to $all or NULL, it means that the rule applies to all users
  • clientid: the Client ID of the connected client. If NULL, it means that all clientids are matched
  • access: allowed operations: subscription (1), publication (2), subscription publication (3)
  • topic: topics to control include System theme , wildcards can be used, and placeholders can be added to the topic to match the client information. For example, / topic/%c will replace the topic with the Client ID of the current client when matching
    • %u: User name
    • %c: Client ID
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(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',
  `create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT 'Creation time',
  `modified_time` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT 'Modification time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

If you use Navicat, you can create the table in the following ways. After creating the table, you can directly ACL test without configuring restart.

Modify configuration

The configuration is consistent with the EMQ X MySQL 8 authentication configuration. query statement fields can be added according to business needs.

# etc/plugins/emqx_auth_mysql.conf
## Superuser query.
##
## Value: SQL
##
## Variables:
##  -% u: username username
##  -% c: clientid user id
##  -% C: common name of client TLS cert TLS certificate name
##  -% d: subject of client TLS cert TLS subcertificate name
##
auth.mysql.super_query = select is_superuser from mqtt_user where username = '%u' limit 1                                                                               
## ACL query.
##
## Value: SQL
##
## Variables:
##  -% a: ipaddr ip address
##  -% u: username username
##  -% c: clientid user id
##                                                                                       
## Note: You can add the 'ORDER BY' statement to control the rules match order
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 = '

There can only be one super user query result. When there are multiple results, only the first one is taken as valid data

Other users can have multiple query results. Multiple results are matched from top to bottom

The query result must contain allow, access, topic, clientid, username and ipaddr fields. If the fields do not want to participate in the comparison, the $all string or database NULL value will be used

system configuration

ACL cache

ACL caching allows the client to cache an ACL rule into memory after it hits, so that it can be used directly next time. When the client publishes and subscribes frequently, opening ACL caching can improve ACL check performance.

ACL cache size and cache time can be configured in etc/emqx.conf:

# etc/emqx.conf

## Enable
enable_acl_cache = on

## Maximum number of cache rules per client
acl_cache_max_size = 32

## Cache expiration time. After timeout, the cache will be cleared
acl_cache_ttl = 1m

test

get ready

Create multiple users

-- Create a superuser
INSERT INTO `emqx`.`mqtt_user`(`id`, `username`, `password`, `salt`, `is_superuser`, `created`) VALUES (1, 'emqx', 'a053f6a9786c2eb1bf5b7278a6beab2c2409a41855cc2da68e784b853e5bbe3f', 'maker_knz', 1, NULL);
-- establish client_1 user
INSERT INTO `emqx`.`mqtt_user`(`id`, `username`, `password`, `salt`, `is_superuser`, `created`) VALUES (2, 'client_1', 'a053f6a9786c2eb1bf5b7278a6beab2c2409a41855cc2da68e784b853e5bbe3f', 'maker_knz', 0, NULL);
-- establish client_2 user
INSERT INTO `emqx`.`mqtt_user`(`id`, `username`, `password`, `salt`, `is_superuser`, `created`) VALUES (3, 'client_2', 'a053f6a9786c2eb1bf5b7278a6beab2c2409a41855cc2da68e784b853e5bbe3f', 'maker_knz', 0, NULL);

Test superuser

-- Test disable superuser
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (1, 0, NULL, 'emqx', NULL, 3, '/emqx/#', '2021-10-21 14:15:36', '2021-10-21 14:15:36');
-- Disable client client_2 subscribe/release/emqx/All topics under
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (2, 0, NULL, 'client_1', NULL, 3, '/emqx/#', '2021-10-21 14:25:13', '2021-10-21 14:25:13');

Super users can subscribe / publish directly

Non super users cannot subscribe / publish

Test IP address

The local IP is 192.168.31.97

-- prohibit client_1 192.168.31.97 Client publishing on/subscribe/emqx All topics under
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (2, 0, '192.168.31.97', 'client_1', NULL, 3, '/emqx/#', '2021-10-21 14:25:13', '2021-10-21 14:38:53');

use

Using username as client_ The client of 1 cannot publish / subscribe to / emqx/test topic

After modifying allow from 0 to 1, it can be accessed

Test publish / subscribe

client_2 prohibit subscriptions to / emqx/sub topics

client_2 prohibit publishing of / emqx/pub topic

-- Disable client client_2 subscribe/emqx/sub theme
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (3, 0, NULL, 'client_2', NULL, 1, '/emqx/sub', '2021-10-21 14:50:02', '2021-10-21 14:50:36');
-- Disable client client_2 stay/emqx/pub Publish on topics
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (4, 0, NULL, 'client_2', NULL, 2, '/emqx/pub', '2021-10-21 14:50:34', '2021-10-21 14:50:38');

Test ACL chain

-- prohibit client_1 Client publishing on/subscribe/emqx All topics under
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (2, 0, NULL, 'client_1', NULL, 3, '/emqx/#', '2021-10-21 14:25:13', '2021-10-21 15:15:26');
-- allow client_1 Client publishing on/subscribe/emqx All topics under
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (3, 1, NULL, 'client_1', NULL, 1, '/emqx/#', '2021-10-21 14:50:02', '2021-10-21 15:15:28');

The test can publish / subscribe to all topics under / emqx

-- allow client_1 Client publishing on/subscribe/emqx All topics under
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (2, 1, NULL, 'client_1', NULL, 3, '/emqx/#', '2021-10-21 14:25:13', '2021-10-21 15:15:26');
-- prohibit client_1 Client publishing on/subscribe/emqx All topics under
INSERT INTO `emqx`.`mqtt_acl`(`id`, `allow`, `ipaddr`, `username`, `clientid`, `access`, `topic`, `create_time`, `modified_time`) VALUES (3, 0, NULL, 'client_1', NULL, 1, '/emqx/#', '2021-10-21 14:50:02', '2021-10-21 15:15:28');

The test cannot publish / subscribe to all topics under / emqx

conclusion

ACL is an authentication chain. The rules in the authentication chain are found from the database. The found results are sorted from large to small according to the ID number. When the first permission that meets the rules is encountered, it can be returned.

summary

This article introduces and uses ACLS for publishing and subscribing. You can learn from the previous article. They use the same configuration, etc/plugins/emqx_auth_mysql.conf, in the authentication table mqtt_ Is in user_ Superuser can ignore mqtt_ Any rule in the ACL table should be carefully set as superuser when applying. ACL is an authentication chain. According to the query statement in the configuration, a series of rules can be found and executed successively according to the order of the rules until the rules are met.

Tags: Database MySQL

Posted on Thu, 21 Oct 2021 02:44:06 -0400 by BPWheeler