MySQL ACL

An external MySQL database is used to store ACL rules for MySQL ACL, which can store a large amount of data and dynamically manage ACLs for easy integration with external device management systems.

Plugin:

  1. emqx_auth_mysql

TIP

The emqx_auth_mysql plugin also includes authentication feature, which can be disabled via comments.

MySQL Connection information

MySQL basic connection information needs to be accessible to all nodes in the cluster.

  1. # etc/plugins/emqx_auth_mysql.conf
  3. ## server address
  4. auth.mysql.server = 127.0.0.1:3306
  6. ## Connection pool size
  7. auth.mysql.pool = 8
  9. auth.mysql.username = emqx
  11. auth.mysql.password = public
  13. auth.mysql.database = mqtt
  15. auth.mysql.query_timeout = 5s

Default table structure

Under the default configuration of the MySQL authentication plugin, you need to ensure that the database has the following two data tables for storing authentication rule information:

Authentication / Superuser Table

  1. CREATE TABLE `mqtt_user` (
  2.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  3.   `username` varchar(100) DEFAULT NULL,
  4.   `password` varchar(100) DEFAULT NULL,
  5.   `salt` varchar(35) DEFAULT NULL,
  6.   `is_superuser` (1) DEFAULT 0,
  7.   `created` datetime DEFAULT NULL,
  8.   PRIMARY KEY (`id`),
  9.   UNIQUE KEY `mqtt_username` (`username`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Sample data:

  1. -- Client information
  2. INSERT INTO `mqtt_user` ( `username`, `password`, `salt`, `is_superuser`)
  3. VALUES
  4.     ('emqx', 'efa1f375d76194fa51a3556a97e641e61685f914d446979da50a551a4333ffd7', NULL, 0);

ACL rule table

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

Rule table field description:

  • allow: Deny（0），Allow（1）
  • ipaddr: Set IP address
  • username: User name for connecting to the client. If the value is set to $ all, the rule applies to all users.
  • clientid: Client ID of the connected client
  • access: Allowed operations: subscribe (1), publish (2), both subscribe and publish (3)
  • topic: Topics to be controlled, which can use wildcards, and placeholders can be added to the topic to match client information. For example, the topic will be replaced with the client ID of the current client when matching t/%c
    • %u: Username
    • %c：Client ID

Sample data in the default configuration:

  1. -- All users cannot subscribe to system topics
  2. INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (0, NULL, '$all', NULL, 1, '$SYS/#');
  4. -- Allow clients on 10.59.1.100 to subscribe to system topics
  5. INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (1, '10.59.1.100', NULL, NULL, 1, '$SYS/#');
  7. -- Deny client to subscribe to the topic of /smarthome/+/temperature
  8. INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (0, NULL, '$all', NULL, 1, '/smarthome/+/temperature');
  10. -- Allow clients to subscribe to the topic of /smarthome/${clientid}/temperature with their own Client ID
  11. INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (1, NULL, '$all', NULL, 1, '/smarthome/%c/temperature');

After enabling MySQL ACL and successfully connecting with the username emqx, the client should have permissions on the topics it wants to subscribe to/publish.

TIP

This is the table structure used by default configuration. After being familiar with the use of this plugin, you can use any data table that meets the conditions for ACL rule storage.

Superuser SQL（super_query）

When performing ACL authentication, EMQ X Broker will use the current client information to execute the user-configured superuser SQL to query whether the client is a superuser. ACL SQL is skipped when the client is superuser.

  1. # etc/plugins/emqx_auth_mysql.conf
  3. auth.mysql.super_query = select is_superuser from mqtt_user where username = '%u' limit 1

You can use the following placeholders in SQL and EMQ X Broker will automatically populate with client information when executed:

  • %u：Username
  • %c：Client ID
  • %C：TLS certificate common name (the domain name or subdomain name of the certificate), valid only for TLS connections
  • %d：TLS certificate subject, valid only for TLS connections

You can adjust the super user SQL according to business to achieve more business-related functions, such as adding multiple query conditions and using database preprocessing functions. However, in any case, the superuser SQL needs to meet the following conditions:

  1. The query result must include the is_superuser field, which should be explicitly true
  2. There can be only one query result. When there are multiple results, only the first one is taken as valid data.

TIP

If superuser functionality is not needed, it can be more efficient when commenting and disabling this option

ACL SQL（acl_query）

When performing ACL authentication, EMQ X Broker will use the current client information to populate and execute the user-configured superuser SQL. If superuser SQL is not enabled or the client is not a superuser, ACL SQL is used to query the client’s ACL rules in the database.

  1. # etc/plugins/emqx_auth_mysql.conf
  3. 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'

You can use the following placeholders in SQL and EMQ X Broker will automatically populate with client information when executed:

  • %u：Username
  • %c：Client ID
  • %C：TLS certificate common name (the domain name or subdomain name of the certificate), valid only for TLS connections
  • %d：TLS certificate subject, valid only for TLS connections

You can adjust the ACL SQL according to business to achieve more business-related functions, such as adding multiple query conditions and using database preprocessing functions. However, in any case, the ACL SQL needs to meet the following conditions:

  1. The query result must include the fields of allow, access, topic, clientid, username, ipaddr. If the fields is not involved in the comparison, the $ all string or the databaseNULL value should be used.
  2. There can be multiple query results. When multiple results are matched, they are matched from top to bottom.

TIP

You can adjust query conditions and specify sorting methods in SQL to achieve more efficient queries

Special Instructions

For MySQL 8.0 and later version, caching_sha2_password is used as the default authentication plugin. Due to the limit of client driver, you must change it to the mysql_native_password plugin:

  1. ALTER USER 'your_username'@'your_host' IDENTIFIED WITH mysql_native_password BY 'your_password';