访问控制-客户端认证 使用MySQL数据源 服务配置问题

环境

  • EMQX 版本:5.3.2/5.5.0
  • 操作系统版本:ubutu 22.04
    使用环境为docker 版本信息如下
Client: Docker Engine - Community
 Version:           24.0.7
 API version:       1.43
 Go version:        go1.20.10
 Git commit:        afdd53b
 Built:             Thu Oct 26 09:07:41 2023
 OS/Arch:           linux/amd64
 Context:           default

Server: Docker Engine - Community
 Engine:
  Version:          24.0.7
  API version:      1.43 (minimum version 1.12)
  Go version:       go1.20.10
  Git commit:       311b9ff
  Built:            Thu Oct 26 09:07:41 2023
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.6.26
  GitCommit:        3dd1e886e55dd695541fdcd67420c2888645a495
 runc:
  Version:          1.1.10
  GitCommit:        v1.1.10-0-g18a0cb0
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

重现此问题的步骤

  1. 使用docker运行emqx
    docker run --name emqx --network host --rm emqx/emqx:5.5.0

  2. 配置客户端认证数据源,无论配置中的设置的服务 host 为多少,最终只能走本机
    例:服务配置 192.168.31.7:3306 最终日志中请求的还是本机地址,无论是使用自定义hosts配置文件中的域名或是使用具体的host主机均指向本机地址。

  3. 当配置的 MySQL 数据源为 192.168.31.7:3307 时 服务可以正常连接解析,当数据库端口换为 3306 时 host均解析为本机地址。

预期行为

正常连接配置的 host:port MySQL服务。

实际行为

数据源最终指向为本机地址,无论docker网络运行是以host方式还是bridge均为此现象,之前使用的为5.3.2的挂载数据,后续直接使用容器未挂载数据的情况下还是该现象。

只有当port为非3306时,服务所指向的 host 才是配置的地址。

  • 配置服务为 192.168.31.7:3306 的 warning log
[warning] msg: start_resource_failed, mfa: emqx_resource_manager:start_resource/2(612), id: <<"emqx_authn_mysql:3">>, reason: {start_pool_failed,<<"emqx_authn_mysql:3">>,{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}}

这个和 EMQX 软件没有关系。是你的 MYSQL的配置有问题。
Access denied for user ‘root’@‘192.168.31.5’ (using password: YES)
这个错是MYSQL 拒绝连接的返回原因:你配置了他不让 root 用户使用192.168.31.5 连接。
大概就是(你可以自己再搜索一下)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.31.5';
FLUSH PRIVILEGES;

还是没有懂我的意思,我是说配置了host的情况下,系统内最终还是会使用本机(192.168.31.5),除非使用非3306端口才会使用配置的host,上述已经描述了我在配置了服务为 192.168.31.7:3306 的情况下,emqx 还是使用 本机(192.168.31.5)的IP进行请求连接

这是emqx数据目录下的cluster.hocon配置截图,配置已经为192.168.31.7 最终日志还是显示为本机IP (192.168.31.5)

抱歉,你说的日志是这一条么?
如果是这一条的话,说明是 mysql返回的拒绝了.5 的连接。难道这个 mysql 是的.5机器也装了一mysql ?

你是不是想改用 EMQX 作为 mysql 客户端的 IP ?
我这样理解对么?

无论你配置的 MYSQL server 是什么,最终 EMQX 都是使用本地 IP去连你配置的这个 server 的。他改不了本地 IP。
EMQX 是先在本地机器和 MYSQL 之间建立 tcp 连接(这里的 IP 已经确定了,无法改),然后再使用 MYSQL 的协议进行通信。

通信原理了解的,EMQX安装在192.168.31.5上,同时192.168.31.5和192.168.31.7都安装了MySQL,现在EMQX需要使用其他服务器上的MySQL服务,但是无法修改成功,最终连接的还是本机的MySQL,我配置了服务为192.168.31.7或者是域名(hosts文件本地路由其他主机)等其他非本机的情况下,最终日志都是显示去连接本机的MySQL服务,(直接使用外部域名解析最终会连接本机对外的公网IP,也没有解析为配置的外部的服务地址)

上面的配置信息也可以看出,配置的是192.168.31.7的,但是日志显示最终连接到192.168.31.5,故能看出配置信息并未生效

如果配置的服务地址为192.168.31.7:3307,也就是端口不是3306的情况下,可以正常解析并连接到192.168.31.7的服务器。
如果是配置的192.168.31.7:3306,端口是3306的情况下,日志显示解析的为192.168.31.5服务器,而不是配置的.7服务器

这有相关的日志么,可以发来看看。

然后再执行一下这个命令,看看配置最终生效的是什么:

./bin/emqx ctl conf show authentication

配置截图:

docker运行日志:

2024-02-18T03:05:55.245955+00:00 [error] Supervisor: {<0.32319.3>,ecpool_worker_sup}. Context: start_error. Reason: {1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}. Offender: id={worker,1},pid=undefined.
2024-02-18T03:05:55.245959+00:00 [error] crasher: initial call: mysql_conn:init/1, pid: <0.32321.3>, registered_name: [], exit: {{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>},[{gen_server,init_it,6,[{file,"gen_server.erl"},{line,835}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}, ancestors: [<0.32320.3>,<0.32319.3>,<0.32317.3>,ecpool_sup,<0.2766.0>], message_queue_len: 0, messages: [], links: [<0.32320.3>], dictionary: [], trap_exit: false, status: running, heap_size: 233, stack_size: 28, reductions: 290; neighbours:
2024-02-18T03:05:55.246235+00:00 [error] Supervisor: {<0.32317.3>,ecpool_pool_sup}. Context: start_error. Reason: {shutdown,{failed_to_start_child,{worker,1},{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}}}. Offender: id=worker_sup,pid=undefined.
2024-02-18T03:05:55.246357+00:00 [error] crasher: initial call: ecpool_worker:init/1, pid: <0.32320.3>, registered_name: [], exit: {{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>},[{gen_server,init_it,6,[{file,"gen_server.erl"},{line,835}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}, ancestors: [<0.32319.3>,<0.32317.3>,ecpool_sup,<0.2766.0>], message_queue_len: 0, messages: [], links: [<0.32319.3>,<0.32321.3>], dictionary: [], trap_exit: true, status: running, heap_size: 610, stack_size: 28, reductions: 258; neighbours:
2024-02-18T03:05:55.246683+00:00 [error] msg: start_ecpool_error, mfa: emqx_resource_pool:start/3(51), pool_name: <<"emqx_authn_mysql:1">>, reason: {1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}
2024-02-18T03:05:55.246817+00:00 [warning] msg: start_resource_failed, mfa: emqx_resource_manager:start_resource/2(612), id: <<"emqx_authn_mysql:1">>, reason: {start_pool_failed,<<"emqx_authn_mysql:1">>,{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}}
2024-02-18T03:05:55.247012+00:00 [warning] msg: alarm_is_activated, mfa: emqx_alarm:do_actions/3(418), message: <<"resource down: {start_pool_failed,<<\"emqx_authn_mysql:1\">>,{1045,<<\"28000\">>,<<\"Access denied for user 'root'@'192.168.31.5' (using password: YES)\">>}}">>, name: <<"emqx_authn_mysql:1">>
2024-02-18T03:06:10.253213+00:00 [error] Supervisor: {<0.32328.3>,ecpool_worker_sup}. Context: start_error. Reason: {1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}. Offender: id={worker,1},pid=undefined.
2024-02-18T03:06:10.253209+00:00 [error] crasher: initial call: mysql_conn:init/1, pid: <0.32330.3>, registered_name: [], exit: {{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>},[{gen_server,init_it,6,[{file,"gen_server.erl"},{line,835}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}, ancestors: [<0.32329.3>,<0.32328.3>,<0.32326.3>,ecpool_sup,<0.2766.0>], message_queue_len: 0, messages: [], links: [<0.32329.3>], dictionary: [], trap_exit: false, status: running, heap_size: 233, stack_size: 28, reductions: 290; neighbours:
2024-02-18T03:06:10.253476+00:00 [error] Supervisor: {<0.32326.3>,ecpool_pool_sup}. Context: start_error. Reason: {shutdown,{failed_to_start_child,{worker,1},{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}}}. Offender: id=worker_sup,pid=undefined.
2024-02-18T03:06:10.253545+00:00 [error] crasher: initial call: ecpool_worker:init/1, pid: <0.32329.3>, registered_name: [], exit: {{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>},[{gen_server,init_it,6,[{file,"gen_server.erl"},{line,835}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}, ancestors: [<0.32328.3>,<0.32326.3>,ecpool_sup,<0.2766.0>], message_queue_len: 0, messages: [], links: [<0.32328.3>,<0.32330.3>], dictionary: [], trap_exit: true, status: running, heap_size: 610, stack_size: 28, reductions: 261; neighbours:
2024-02-18T03:06:10.254061+00:00 [error] msg: start_ecpool_error, mfa: emqx_resource_pool:start/3(51), pool_name: <<"emqx_authn_mysql:1">>, reason: {1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}
2024-02-18T03:06:10.254219+00:00 [warning] msg: start_resource_failed, mfa: emqx_resource_manager:start_resource/2(612), id: <<"emqx_authn_mysql:1">>, reason: {start_pool_failed,<<"emqx_authn_mysql:1">>,{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}}

这个应该是 docker 的 DNS 设置有问题:

  1. 确保在Docker宿主机上的/etc/resolv.conf文件中配置了正确的DNS服务器。

  2. Docker网络设置:Docker使用不同的网络模式(如桥接模式、主机模式等),这些模式会影响Docker容器内部的网络访问方式。如果你使用的是桥接模式,Docker容器将有自己的IP地址,并通过宿主机的网络进行通信,DNS解析应该正常工作。但如果你使用的是主机模式,容器将共享宿主机的网络栈,这也可能导致DNS解析的问题。

host模式和桥接模式都尝试过了,host模式直接解析本地路由主机的这种不应该存在这个问题的

兄弟,这个日志里面的 .5 IP: 真的是 mysql 返回来拒绝他的,没骗你。
他明确的表达了这个 MYSQL 拒绝192.168.31.5 以 root 用户来连接。
但是他没有说他连接的是192.168.31.5的 mysql,不信你就把192.168.31.5上的 mysql 停了,他連的就是:

./bin/emqx ctl conf show authentication

里面显示的.7

再有一个辅助的验证的方法,就是:用 netstats 命令看 3306 端口(要不停的刷新看,因为tcp 连上后就断开了…)。能看到他连的是.7 的。

.7和.5服务器是可以正常使用root用户访问登录的,已经开了root@%的权限
把.5服务器MySQL停止之后,emqx还是保持外部数据源是192.168.31.7的情况,日志同样还是报错



2024-02-18T03:18:38.617607+00:00 [info] msg: starting_mysql_connector, mfa: emqx_mysql:on_start/2(108), config: #{backend => mysql,database => <<"main-cloud">>,enable => true,mechanism => password_based,password => "******",password_hash_algorithm => #{name => plain,salt_position => disable},pool_size => 8,prepare_statement => #{emqx_authn_mysql => [<<"SELECT access_password as password_hash FROM plat_device where device_id = ">>,"?",<<" LIMIT 1">>]},query => "SELECT access_password as password_hash FROM plat_device where device_id = ${clientid} LIMIT 1",query_timeout => 5000,server => "192.168.31.7:3306",ssl => #{ciphers => [],depth => 10,enable => false,hibernate_after => 5000,log_level => notice,reuse_sessions => true,secure_renegotiate => true,user_lookup_fun => {fun emqx_tls_psk:lookup/3,undefined},verify => verify_peer,versions => ['tlsv1.3','tlsv1.2']},username => <<"root">>}, connector: <<"emqx_authn_mysql:1">>
2024-02-18T03:18:38.622988+00:00 [error] crasher: initial call: mysql_conn:init/1, pid: <0.3383.0>, registered_name: [], exit: {{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>},[{gen_server,init_it,6,[{file,"gen_server.erl"},{line,835}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}, ancestors: [<0.3382.0>,<0.3381.0>,<0.3379.0>,ecpool_sup,<0.2757.0>], message_queue_len: 0, messages: [], links: [<0.3382.0>], dictionary: [], trap_exit: false, status: running, heap_size: 376, stack_size: 28, reductions: 292; neighbours:
2024-02-18T03:18:38.623234+00:00 [error] Supervisor: {<0.3381.0>,ecpool_worker_sup}. Context: start_error. Reason: {1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}. Offender: id={worker,1},pid=undefined.
2024-02-18T03:18:38.623629+00:00 [error] Supervisor: {<0.3379.0>,ecpool_pool_sup}. Context: start_error. Reason: {shutdown,{failed_to_start_child,{worker,1},{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}}}. Offender: id=worker_sup,pid=undefined.
2024-02-18T03:18:38.623614+00:00 [error] crasher: initial call: ecpool_worker:init/1, pid: <0.3382.0>, registered_name: [], exit: {{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>},[{gen_server,init_it,6,[{file,"gen_server.erl"},{line,835}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}, ancestors: [<0.3381.0>,<0.3379.0>,ecpool_sup,<0.2757.0>], message_queue_len: 0, messages: [], links: [<0.3381.0>,<0.3383.0>], dictionary: [], trap_exit: true, status: running, heap_size: 376, stack_size: 28, reductions: 241; neighbours: neighbour: pid: <0.3383.0>, registered_name: [], initial call: mysql_conn:init/1, current_function: {re,grun,3}, ancestors: [<0.3382.0>,<0.3381.0>,<0.3379.0>,ecpool_sup,<0.2757.0>], message_queue_len: 0, links: [<0.3382.0>], trap_exit: false, status: runnable, heap_size: 4185, stack_size: 55, reductions: 17074, current_stacktrace: [{re,do_replace,5,[{file,"re.erl"},{line,417}]},{re,replace,4,[{file,"re.erl"},{line,387}]},{logger_formatter,format,2,[{file,"logger_formatter.erl"},{line,82}]},{logger_h_common,try_format,3,[{file,"logger_h_common.erl"},{line,407}]},{logger_h_common,do_log_to_binary,2,[{file,"logger_h_common.erl"},{line,395}]},{logger_h_common,log,2,[{file,"logger_h_common.erl"},{line,177}]},{logger_backend,call_handlers,3,[{file,"logger_backend.erl"},{line,51}]},{proc_lib,exit_p,3,[{file,"proc_lib.erl"},{line,260}]}]
2024-02-18T03:18:38.624349+00:00 [error] msg: start_ecpool_error, mfa: emqx_resource_pool:start/3(51), pool_name: <<"emqx_authn_mysql:1">>, reason: {1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}
2024-02-18T03:18:38.624560+00:00 [warning] msg: start_resource_failed, mfa: emqx_resource_manager:start_resource/2(612), id: <<"emqx_authn_mysql:1">>, reason: {start_pool_failed,<<"emqx_authn_mysql:1">>,{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}}
2024-02-18T03:18:53.625572+00:00 [info] msg: starting_mysql_connector, mfa: emqx_mysql:on_start/2(108), config: #{backend => mysql,database => <<"main-cloud">>,enable => true,mechanism => password_based,password => "******",password_hash_algorithm => #{name => plain,salt_position => disable},pool_size => 8,prepare_statement => #{emqx_authn_mysql => [<<"SELECT access_password as password_hash FROM plat_device where device_id = ">>,"?",<<" LIMIT 1">>]},query => "SELECT access_password as password_hash FROM plat_device where device_id = ${clientid} LIMIT 1",query_timeout => 5000,server => "192.168.31.7:3306",ssl => #{ciphers => [],depth => 10,enable => false,hibernate_after => 5000,log_level => notice,reuse_sessions => true,secure_renegotiate => true,user_lookup_fun => {fun emqx_tls_psk:lookup/3,undefined},verify => verify_peer,versions => ['tlsv1.3','tlsv1.2']},username => <<"root">>}, connector: <<"emqx_authn_mysql:1">>
2024-02-18T03:18:53.631044+00:00 [error] Supervisor: {<0.3397.0>,ecpool_worker_sup}. Context: start_error. Reason: {1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31.5' (using password: YES)">>}. Offender: id={worker,1},pid=undefined.
2024-02-18T03:18:53.631061+00:00 [error] crasher: initial call: mysql_conn:init/1, pid: <0.3399.0>, registered_name: [], exit: {{1045,<<"28000">>,<<"Access denied for user 'root'@'192.168.31

目前测试通过的只有连接 192.168.31.7:3307,怀疑和 3306 端口有关系,有没有可能 EMQX 某个错误逻辑导致配置 3306 端口的最终都会产生该现象呢,麻烦您这边看是否能够复现该异常,我这边也在其他服务器上测试一下
客户端连接服务端,客户端的端口是随机的,所以不存在端口占用的情况,其他服务都可正常连接到192.168.31.7的3306和3307MySQL

日志这样,那就对了。

能用本地的 navicat 连上 2 个数据库,应该说明不了什么问题。
你得在 docker 里面用他的 IP来连。试试这个:
https://github.com/Logiqx/mysql-client

不知为何产生这个问题,别的机器上正常,就这台会连接到本机IP,这台到时候重置了