使用MYSQL进行Mosquitto身份验证

CREATE DATABASE mosquitto_auth;
USE mosquitto_auth;

CREATE TABLE users (
    username VARCHAR(100) PRIMARY KEY,
    password VARCHAR(100)
);

INSERT INTO users (username, password) VALUES ('user1', 'password1');
INSERT INTO users (username, password) VALUES ('user2', 'password2');

auth_plugin mysql
auth_opt_host localhost
auth_opt_port 3306
auth_opt_dbname mosquitto_auth
auth_opt_user <mysql_username>
auth_opt_pass <mysql_password>
auth_opt_userquery SELECT password FROM users WHERE username = '%s' LIMIT 1
auth_opt_superquery SELECT COUNT(*) FROM users WHERE username = '%s' AND password = '%s' LIMIT 1
auth_opt_aclquery SELECT topic FROM acls WHERE (username = '%s') AND (rw >= %d)

CREATE TABLE acls (
    username VARCHAR(100),
    topic VARCHAR(100),
    rw INT
);

INSERT INTO acls (username, topic, rw) VALUES ('user1', 'sensors/+', 1);
INSERT INTO acls (username, topic, rw) VALUES ('user2', 'sensors/temperature', 2);