EMQX 使用 postgresql 存储用户名密码验证

官网教程：https://www.emqx.io/docs/zh/v4.4/advanced/auth-postgresql.html#postgresql-%E8%BF%9E%E6%8E%A5%E4%BF%A1%E6%81%AF

这里使用 docker 启动 emqx 和 postgresql

下文提到的 $(DOCKER_VOLUME_DIR) 请自行指定目录

启动 postgres

    docker run \
        --restart always \
        --name postgres-14-3 \
        -v $(DOCKER_VOLUME_DIR)/postgres-14-3/data:/var/lib/postgresql/data \
        -p 5432:5432 \
        -e POSTGRES_USER=xander \
        -e POSTGRES_PASSWORD=123456 \
        -d postgres:14.3

启动 emqx

    docker run \
        -d \
        --name emqx-4-4-3 \
         --link postgres-14-3:postgres-14-3 \
        -v $(DOCKER_VOLUME_DIR)/emqx-4-4-3/data:/opt/emqx/data \
        -v $(DOCKER_VOLUME_DIR)/emqx-4-4-3/log:/opt/emqx/log \
        -v $(DOCKER_VOLUME_DIR)/emqx-4-4-3/etc/plugins/emqx_auth_pgsql.conf:/opt/emqx/etc/plugins/emqx_auth_pgsql.conf \
        -v $(DOCKER_VOLUME_DIR)/emqx-4-4-3/etc/emqx.conf:/opt/emqx/etc/emqx.conf \
        -p 1883:1883 \
        -p 18083:18083 \
        emqx/emqx:4.4.3

编辑emqx_auth_pgsql.conf 配置如下：

##--------------------------------------------------------------------
## PostgreSQL Auth/ACL Plugin
##--------------------------------------------------------------------

## PostgreSQL server address.
##
## Value: Port | IP:Port
##
## Examples: 5432, 127.0.0.1:5432, localhost:5432
auth.pgsql.server = postgres-14-3:5432

## PostgreSQL pool size.
##
## Value: Number
auth.pgsql.pool = 8

## PostgreSQL username.
##
## Value: String
auth.pgsql.username = xander

## PostgreSQL password.
##
## Value: String
auth.pgsql.password = 123456

## PostgreSQL database.
##
## Value: String
auth.pgsql.database = my_database_name

## PostgreSQL database encoding.
##
## Value: String
auth.pgsql.encoding = utf8

## Whether to enable SSL connection.
##
## Value: on | off
auth.pgsql.ssl = off

## TLS version.
##
## Available enum values:
##    tlsv1.3,tlsv1.2,tlsv1.1,tlsv1
##
## Value: String, seperated by ','
#auth.pgsql.ssl.tls_versions = tlsv1.3,tlsv1.2,tlsv1.1

## SSL keyfile.
##
## Value: File
#auth.pgsql.ssl.keyfile =

## SSL certfile.
##
## Value: File
#auth.pgsql.ssl.certfile =

## SSL cacertfile.
##
## Value: File
#auth.pgsql.ssl.cacertfile =

## In mode verify_none the default behavior is to allow all x509-path
## validation errors.
##
## Value: true | false
#auth.pgsql.ssl.verify = false

## If not specified, the server's names returned in server's certificate is validated against
## what's provided `auth.pgsql.server` config's host part.
## Setting to 'disable' will make EMQX ignore unmatched server names.
## If set with a host name, the server's names returned in server's certificate is validated
## against this value.
##
## Value: String | disable
## auth.pgsql.ssl.server_name_indication = disable

## Authentication query.
##
## Value: SQL
##
## Variables:
##  - %u: username
##  - %c: clientid
##  - %C: common name of client TLS cert
##  - %d: subject of client TLS cert
##
auth.pgsql.auth_query = select password from mqtt_user where username = '%u' limit 1

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

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

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

## bcrypt with salt prefix
## auth.pgsql.password_hash = salt,bcrypt

## pbkdf2 with macfun iterations dklen
## macfun: md4, md5, ripemd160, sha, sha224, sha256, sha384, sha512
## auth.pgsql.password_hash = pbkdf2,sha256,1000,20

## Superuser query.
##
## Value: SQL
##
## Variables:
##  - %u: username
##  - %c: clientid
##  - %C: common name of client TLS cert
##  - %d: subject of client TLS cert
##
auth.pgsql.super_query = select is_superuser from mqtt_user where username = '%u' limit 1

## ACL query. Comment this query, the ACL will be disabled.
##
## Value: SQL
##
## Variables:
##  - %a: ipaddress
##  - %u: username
##  - %c: clientid
##
## Note: You can add the 'ORDER BY' statement to control the rules match order
# auth.pgsql.acl_query = select allow, ipaddr, username, clientid, access, topic from mqtt_acl where ipaddr = '%a' or username = '%u' or username = '$all' or clientid = '%c'

若直接存储密码，不加密则 设置 auth.pgsql.password_hash = plain
若加密，则插入 postgres 时，要插入加密后的 password

编辑 emqx.conf 配置

禁止匿名认证

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

在postgres数据库创建表

CREATE TABLE mqtt_user (
  id SERIAL PRIMARY KEY,
  username CHARACTER VARYING(100),
  password CHARACTER VARYING(100),
  salt CHARACTER VARYING(40),
  is_superuser BOOLEAN,
  UNIQUE (username)
)

插入账号：用户名 emqx， 密码 123456

INSERT INTO mqtt_user (username, password, salt, is_superuser)
VALUES
    ('emqx', '123456', NULL, false);

开启 emqx_auth_pgsql 插件

访问（默认用户名密码为 admin, public） http://localhost:18083/#/plugins
开启 emqx_auth_pgsql 插件

连接 mqtt broker

使用 账号密码 emqx, 123456 连接