Install EMQ-X MQTT Broker

Install Supporting Software

If installing as a single server with integrated database authentication, install the following:

Install EMQ-X

wget https://www.emqx.com/en/downloads/broker/4.3.10/emqx-ubuntu20.04-4.3.10-amd64.deb
sudo apt install ./emqx-ubuntu20.04-4.3.10-amd64.deb

Configure EMQ-X for HAProxy

If installing behind a reverse proxy such as HAProxy you will need the following similar configuration inside your HAProxy server configuration:

Make sure to adjust accordingly for SSL and IP address

frontend mqtt_fe
        mode tcp
        option tcplog

        bind *:1883
        bind *:8883 ssl crt /etc/ssl/private

        # Reject connections that have an invalid MQTT packet
        tcp-request inspect-delay 10s
        tcp-request content reject unless { req.payload(0,0),mqtt_is_valid }
        default_backend mqtt_be

backend mqtt_be
        mode tcp
        balance roundrobin

        # Create a stick table for session persistence
        stick-table type string len 32 size 100k expire 30m

        # Use ClientID / client_identifier as persistence key
        stick on req.payload(0,0),mqtt_field_value(connect,client_identifier)

        server mqtt1 192.168.1.91:1883 check-send-proxy send-proxy-v2 check inter 10s fall 2 rise 5

Next, edit the EMQ-X configuration to accommodate the proxy:

sudo nano /etc/emqx/emqx.conf

Find the following section which will have the setting commented and uncomment the setting to look as follows:

## Enable the Proxy Protocol V1/2 if the EMQ X cluster is deployed
## behind HAProxy or Nginx.
##
## See: https://www.haproxy.com/blog/haproxy/proxy-protocol/
##
## Value: on | off
listener.tcp.external.proxy_protocol = on

Configure EMQ-X for Database Authentication using MySQL/MariaDB

Edit the EMQ-X Plugin configuration for MySQL:

sudo nano /etc/emqx/plugins/emqx_auth_mysql.conf

Edit the following sections according to your login information:

## MySQL server address.
##
## Value: Port | IP:Port
##
## Examples: 3306, 127.0.0.1:3306, localhost:3306
auth.mysql.server = 127.0.0.1:3306

## MySQL username.
##
## Value: String
auth.mysql.username = mqtt

## MySQL password.
##
## Value: String
auth.mysql.password = password

## MySQL database.
##
## Value: String
auth.mysql.database = mqtt

Change the following sections to set up passwords to be stored using salted and hashed passwords:

Make sure to comment out the existing line which only uses hashing with sha256!

## Authentication query.
##
## Note that column names should be 'password' and 'salt' (if used).
## In case column names differ in your DB - please use aliases,
## e.g. "my_column_name as password".
##
## Value: SQL
##
## Variables:
##  - %u: username
##  - %c: clientid
##  - %C: common name of client TLS cert
##  - %d: subject of client TLS cert
##
auth.mysql.auth_query = select password, salt from mqtt_user where username = '%u' limit 1
## auth.mysql.auth_query = select password_hash as password from mqtt_user where username = '%u' limit 1

## Password hash.
##
## Value: plain | md5 | sha | sha256 | bcrypt
## auth.mysql.password_hash = sha256

## sha256 with salt prefix
## auth.mysql.password_hash = salt,sha256

## bcrypt with salt only prefix
## auth.mysql.password_hash = salt,bcrypt

## sha256 with salt suffix
auth.mysql.password_hash = sha256,salt

Next, edit the EMQ-X configuration to adjust for deny by default security:

sudo nano /etc/emqx/emqx.conf

Find the following section which will have the setting commented and uncomment the setting to look as follows:

## Allow anonymous authentication by default if no auth plugins loaded.
## Notice: Disable the option in production deployment!
##
## Value: true | false
allow_anonymous = truefalse

## Allow or deny if no ACL rules matched.
##
## Value: allow | deny
acl_nomatch = deny

Using phpMyAdmin or any other SQL tool, create the User and ACL table in the mqtt database with the following SQL commands:

USE mqtt;

CREATE TABLE `mqtt_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `salt` varchar(35) DEFAULT NULL,
  `is_superuser` tinyint(1) DEFAULT 0,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mqtt_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

Generate logins using the following SQL command substituting username and password as necessary:

SET @salt=MD5(RAND());
INSERT INTO `mqtt_user` ( `username`, `password`, `salt`) VALUES ('MyUsername', SHA2(CONCAT('MyPassword123', @salt),0), @salt);

Grant ACL permissions using the following SQL command substituting the username, and topic as necessary:

ACL 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

-- All users cannot subscribe to system topics
INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (0, NULL, '$all', NULL, 1, '$SYS/#');

-- Allow clients on 10.59.1.100 to subscribe to system topics
INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (1, '10.59.1.100', NULL, NULL, 1, '$SYS/#');

-- Deny client to subscribe to the topic of /smarthome/+/temperature
INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (0, NULL, '$all', NULL, 1, '/smarthome/+/temperature');

-- Allow clients to subscribe to the topic of /smarthome/${clientid}/temperature with their own Client ID
INSERT INTO mqtt_acl (allow, ipaddr, username, clientid, access, topic) VALUES (1, NULL, '$all', NULL, 1, '/smarthome/%c/temperature');

Start EMQ-X

sudo service emqx start

Open your browser to the URL for the server: http://<IPAddr>:18083

The username is admin and the password is public

Be sure to change the password immediately!

Go to the plugins and enable the emqx_auth_mysql plugin.

Install EMQ-X MQTT Broker

Install Supporting Software

Install EMQ-X

Configure EMQ-X for HAProxy

Configure EMQ-X for Database Authentication using MySQL/MariaDB

Start EMQ-X

Login to EMQ-X