配置使用 mysql 作为 be (back end)

• 使用config.mk 配置编译参数
  cp config.mk.in config.mk
• 修改

安装 mysql

sudo apt-get install mysql-server libmysqlclient-dev

# Select your backends from this list
BACKEND_CDB ?= no
BACKEND_MYSQL ?= yes   # 使用 mysql 
BACKEND_SQLITE ?= no
BACKEND_REDIS ?= no
BACKEND_POSTGRES ?= no
BACKEND_LDAP ?= no
BACKEND_HTTP ?= no
BACKEND_JWT ?= no
BACKEND_MONGO ?= no
BACKEND_FILES ?= no

# Specify the path to the Mosquitto sources here
# MOSQUITTO_SRC = /usr/local/Cellar/mosquitto/1.4.12 
MOSQUITTO_SRC =/mnt/g/cjc/workspace/mqtt/mosquitto   # 指定mosquitto源码

# Specify the path the OpenSSL here
OPENSSLDIR = /usr

# Specify optional/additional linker/compiler flags here
# On macOS, add
#       CFG_LDFLAGS = -undefined dynamic_lookup
# as described in https://github.com/eclipse/mosquitto/issues/244
#
# CFG_LDFLAGS = -undefined dynamic_lookup  -L/usr/local/Cellar/openssl/1.0.2l/lib
# CFG_CFLAGS = -I/usr/local/Cellar/openssl/1.0.2l/include -I/usr/local/Cellar/mosquitto/1.4.12/include
CFG_LDFLAGS =
CFG_CFLAGS =

编译plugin

make
得到 auth-plug.so

编译 mosquitto

修改 mosquitto-mysql.conf
参考 mosquitto-auth-plug/examples/mosquitto-mysql.conf 中的 插件附加选项，增加到 mosquitto-mysql.conf 中

# 插件so路径
auth_plugin /mnt/g/cjc/workspace/mqtt/mosquitto-auth-plug/auth-plug.so  
auth_opt_backends mysql
auth_opt_cdbname pwdb.cdb
auth_opt_host localhost
auth_opt_port 3306
auth_opt_dbname mqtttest
auth_opt_user root
auth_opt_pass root
# mysql 查询语句约定
auth_opt_userquery SELECT pw FROM users WHERE username = '%s'
auth_opt_superquery SELECT IFNULL(COUNT(*), 0) FROM users WHERE username = '%s' AND super = 1
auth_opt_aclquery SELECT topic FROM acls WHERE username = '%s'


# Usernames with this fnmatch(3) (a.k.a glob(3))  pattern are exempt from the
# module's ACL checking
AUTH_OPT_SUPERUSERS s*

mysql 建表

参考 mosquitto-auth-plug/examples/mysql.sql
测试，直接跑 mysql.sql 建测试表
mysql -uroot -p -Dmqtttest<./../mosquitto-auth-plug/examples/mysql.sql

mysql> show tables;
+--------------------+
| Tables_in_mqtttest |
+--------------------+
| acls               |
| users              |
+--------------------+

mysql> desc users;                                                 
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(25)  | NO   | UNI | NULL    |                |
| pw       | varchar(128) | NO   |     | NULL    |                |
| super    | int(1)       | NO   |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)                                           
                                                                   
mysql> desc acls;                                                  
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(25)  | NO   | MUL | NULL    |                |
| topic    | varchar(256) | NO   |     | NULL    |                |
| rw       | int(1)       | NO   |     | 1       |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)                                           

客户端依赖动态库

把 ./lib/libmosquitto.so.1 加入 /usr/lib 下
sudo cp lib/libmosquitto.so.1 /usr/lib/libmosquitto.so.1

运行测试

• 服务端
  ./src/mosquitto -c mosquitto.conf

1500565002: |-- *** auth-plug: startup
1500565002: |-- ** Configured order: mysql
1500565002: |-- }}}} MYSQL

• 客户端

it MUST return a single column only with the PBKDF2 password hash. A single '%s' in the query string is replaced by the username attempting to access the broker.

未往mysql正确插入数据情况(即未授权），连接将被backend拒绝
Connection Refused: not authorised.
Error: The connection was refused.

密码使用 PBKDF2 存储
A user's password is stored as a PBKDF2 hash in the back-end. An example "password" is a string with five pieces in it, delimited by $
, inspired by this.

Note that the salt
by default will be taken as-is (thus it will not be base64 decoded before the validation). In case your own implementation uses the raw bytes when hashing the password and base64 is only used for display purpose, compile this project with the -DRAW_SALT
flag (you could add this in the config.mk
file to CFG_CFLAGS
).

• pw 格式:

  
  
  image.png

• 使用auth plugin 提供的 np 工具生成密码
  np 工具使用加密算法，明文把组合随机生成的salt，用 sha256作为hash函数， 迭代次数901 次的 PBKDF2 生成了 hashed password, 返回拼接格式的字符串
  mysql数据库pw存储拼接后的密码， auth-plugin 从根据 username从表里查询得到拼接后的密码（包括了 salt，interations, hashfunction），并提取出salt，用用户 password 计算 hashed password 进行比对鉴权。

$ ./np
Enter password:12345
Re-enter same password:12345
PBKDF2$sha256$901$IV/rAqUxT519iO+K$4pe0utPHFZnKpJTASyP0Ann5Nwx5yqZY

• 往 mysql mqtttest 表中添加 user, pw="PBKDF2$sha256$901$IV/rAqUxT519iO+K$4pe0utPHFZnKpJTASyP0Ann5Nwx5yqZY"
  update users set pw="PBKDF2$sha256$901$ubLO1LjWJ0+Gpedp$lpPza0X4dDntdrc5qTqyuRVtIvpLx1N2" where id=7;

• 添加 acl 记录
  insert into acls values(13,'cjc','cjc/rw',2);
  | 13 | cjc | cjc/rw | 2 |

• 测试 订阅
  ./mosquitto_sub -t "cjc/rw" -u "cjc" -P "12345"

• 测试发布
  ./mosquitto_pub -t "cjc/rw" -m "hello" -u "cjc" -P "12345"

• 服务端输出

1500569102: Sending CONNACK to mosqsub|1240-ra1z (0, 0)
1500569102: Received SUBSCRIBE from mosqsub|1240-ra1z
1500569102: cjc/rw (QoS 0)
1500569102: Sending SUBACK to mosqsub|1240-ra1z
1500569112: |-- mosquitto_auth_unpwd_check(cjc)
1500569112: |-- ** checking backend mysql
1500569112: |-- getuser(cjc) AUTHENTICATED=1 by mysql
1500569112: Sending CONNACK to mosqpub|1241-ra1z (0, 0)
1500569112: |-- mosquitto_auth_acl_check(..., mosqpub|1241-ra1z, cjc, cjc/rw, MOSQ_ACL_WRITE)
1500569112: |-- mysql: topic_matches(cjc/rw, cjc/rw) == 1
1500569112: |-- aclcheck(cjc, cjc/rw, 2) trying to acl with mysql
1500569112: |-- aclcheck(cjc, cjc/rw, 2) AUTHORIZED=1 by mysql
1500569112: |-- Cached [1C2BBC255AB58D79DE677B3078E31D74C900A74D] for (mosqpub|1241-ra1z,cjc,2)
1500569112: Received PUBLISH from mosqpub|1241-ra1z (d0, q0, r0, m0, 'cjc/rw', ... (5 bytes))
1500569112: |-- mosquitto_auth_acl_check(..., mosqsub|1240-ra1z, cjc, cjc/rw, MOSQ_ACL_READ)
1500569112: |-- mysql: topic_matches(cjc/rw, cjc/rw) == 1
1500569112: |-- aclcheck(cjc, cjc/rw, 1) trying to acl with mysql
1500569112: |-- aclcheck(cjc, cjc/rw, 1) AUTHORIZED=1 by mysql
1500569112: |-- Cached [AD78C641352E6B2001205F385A3D612C609D6739] for (mosqsub|1240-ra1z,cjc,1)
1500569112: Sending PUBLISH to mosqsub|1240-ra1z (d0, q0, r0, m0, 'cjc/rw', ... (5 bytes))

np 密码生成算法与实现

https://github.com/manolodd/pbkdf2-mosquitto
https://github.com/jpmens/mosquitto-auth-plug/tree/master/contrib/python3
https://github.com/jpmens/mosquitto-auth-plug/issues/44

• Create a salt (byte array with random chars)
• Convert the salt to Base64
• cast this base64 string as a byte array.
• Take the password
• Do the hashing with the password and the converted -> casted salt