MySQL

MySQL authentication uses an external MySQL database as the authentication data source, which can store a large amount of data and facilitate integration with external device management systems.

Plugin:

  1. emqx_auth_mysql

TIP

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

To enable MySQL authentication, you need to configure the following in etc/plugins/emqx_auth_mysql.conf :

MySQL Connection information

For MySQL basic connection information, it needs to ensure that all nodes in the cluster can access.

  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

In the default configuration of MySQL authentication, you need to ensure that the following table is in the database:

  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` tinyint(1) DEFAULT 0,
  7.   `created` datetime DEFAULT NULL,
  8.   PRIMARY KEY (`id`),
  9.   UNIQUE KEY `mqtt_username` (`username`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The sample data in the default configuration is as follows:

  1. INSERT INTO `mqtt_user` ( `username`, `password`, `salt`)
  2. VALUES
  3.     ('emqx', 'efa1f375d76194fa51a3556a97e641e61685f914d446979da50a551a4333ffd7', NULL);

After MySQL authentication is enabled, you can connect with username: emqx, password: public.

TIP

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

Salting rules and hash methods

MySQL authentication support to configure Salting rules and hash methods：

  1. # etc/plugins/emqx_auth_mysql.conf
  3. auth.mysql.password_hash = sha256

auth_query

During authentication, EMQX Broker will use the current client information to populate and execute the user-configured authentication SQL to query the client’s authentication data in the database.

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

You can use the following placeholders in the SQL authentication, and EMQX Broker will be automatically populated 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 authentication 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 authentication must meet the following conditions:

  1. The query result must include the password field, which is used by EMQX Broker to compare with the client password
  2. If the salting configuration is enabled, the query result must include the salt field, which is used by EMQX Broker as the salt value
  3. There can only be one query result. When there are multiple results, only the first one is taken as valid data.

TIP

You can use AS syntax in SQL to specify passwords for field renaming, or set the salt value to a fixed value

Advanced

In the default table structure, we set the username field as a unique index (UNIQUE), and use it with the default query statement (select password from mqtt_user where username ='%u' limit 1) to get very good query performance.

If the default query conditions do not meet your needs, for example, you need to query the corresponding Password Hash and Salt based on the Client ID, please make sure to set the Client ID as an index; Or you want to perform multi-condition queries on Username, Client ID, or other fields. It is recommended to set the correct single-column index or multiple-column index. In short, set the correct table structure and query statement, and try not to let the index fail and affect the query performance.

Special Instructions

For MySQL 8.0 and later version, it uses caching_sha2_password as the default authentication plug-in. 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';