MySQL Authentication/ACL

    Open the official MySQL website: , select the version you need, here we choose the MySQL version as macos-5.7.31

    After the installation is complete, start MySQL.

    Open , click the “Modules” tab on the left, and choose to add:

    Select “Add” and then click the “Module” menu, select “Add”, and then select the MySQL Authentication/ACL module

    image-20200928094942950

    Need to configure MySQL address, user name, password (optional) and other basic connection parameter tables

    Finally click the “Add” button, the module can be added successfully

    image-20200928095050885

    -username: The username for connecting to the client. If the value here is set to , it means that the rule applies to all users -password: password parameter for connecting to the client -salt: password and salt string -is_superuser: Is it a superuser

    When performing identity authentication, EMQX will use the current client information to fill and execute the authentication SQL configured by the user, and query the authentication data of the client in the database.

    1. select password from mqtt_user where username ='%u' limit 1

    Field description

    -%u: username -%c: clientid -%P: Plain text password -%C: TLS certificate common name (domain name or subdomain name of the certificate), valid only when TLS connection -%d: TLS certificate subject, only valid when TLS connection

    The authentication SQL can be adjusted according to business needs, such as adding multiple query conditions and using database preprocessing functions to implement more business-related functions. But in any case, the authentication SQL needs to meet the following conditions:

    1. The query result must contain the password field, which EMQX uses to compare with the client password
    2. If the salting configuration is enabled, the salt field must be included in the query result, and EMQX uses this field as the salt value
    3. There can only be one query result, if there are multiple results, only the first one is taken as valid data

    The sample data in the default configuration is as follows:

    1. INSERT INTO `mqtt_user` (ʻusername`, `password`, `salt`)
    2. VALUES

    After enabling MySQL authentication, you can connect via username: emqx and password: public.

    TIP

    You can use AS syntax in SQL to specify a password 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.

    Access Control List

    -allow: prohibit (0), allow (1) -ipaddr: set IP address -username: The username for connecting to the client. If the value here is set to $all, it means that the rule applies to all users -clientid: clientid of the connected client -access: Allowed operations: subscribe (1), publish (2), subscribe to publish (3) -topic: The controlled topic, wildcards can be used, and placeholders can be added to the topic to match the client information, for example, t/%c will replace the topic with the clientid of the current client when matching

    The principle of access control is to find entries related to the client from MySQL, and then perform authentication. The default query SQL is as follows:

    The following placeholders can be used in the authentication SQL, and EMQX will be automatically filled with client information when executed:

    -%u: username -%c: clientid -%a: Client IP address -%P: Plain text password -%C: TLS certificate common name (domain name or subdomain name of the certificate), valid only when TLS connection -%d: TLS certificate subject, only valid when TLS connection

    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. - Prohibit clients from subscribing to the /smarthome/+/temperature topic
    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 /smarthome/${clientid}/temperature containing their Client ID
    11. INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (1, NULL, '$all', NULL, 1,'/smarthome/%c/temperature');

    Super users can subscribe and publish any topic. The default SQL is as follows:

    You can use the following placeholders in SQL, and EMQX will be automatically filled with client information during execution:

    -%u: username -%c: clientid -%C: TLS certificate common name (domain name or subdomain name of the certificate), valid only when TLS connection -%d: TLS certificate subject, only valid when TLS connection

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

    1. The query result must include the is_superuser field, and is_superuser should be explicitly true
    2. There can only be one query result, if there are multiple results, only the first one is taken as valid data
    1. plain
    3. ## No salt, only hash processing
    4. sha256
    6. ## salt prefix: use sha256 to encrypt salt + password concatenated string
    7. salt,sha256
    9. ## salt suffix: use sha256 encrypted password + salt spliced ​​string
    10. sha256,salt
    12. ## pbkdf2 with macfun iterations dklen
    13. ## macfun: md4, md5, ripemd160, sha, sha224, sha256, sha384, sha512
    14. pbkdf2,sha256,1000,20

    TIP

    Refer to: [Salt rules and hash methods]( rules and hash methods).