emqx5.4版 docker配置env 数据库授权认证问题

EMQX
,
1

问题描述：基于docker-compose配置文件 environment 下配置外部pgsql数据库的认证和授权配置，提示错误信息

EMQX_AUTHENTICATION [authentication.1.query]: SELECT password_hash, salt FROM mqtt_user where is_del=0 and is_enable=1 and username = m1528 LIMIT 1
EMQX_AUTHENTICATION [authentication.1.password_hash_algorithm]: {...}
EMQX_AUTHENTICATION [authentication.1.password_hash_algorithm.salt_position]: suffix
EMQX_AUTHENTICATION [authentication.1.password_hash_algorithm.name]: sha256
EMQX_AUTHENTICATION [authentication.1.backend]: postgresql
EMQX_AUTHENTICATION [authentication.1.mechanism]: password_based
Listener ssl:default on 0.0.0.0:8883 started.
Listener tcp:default on 0.0.0.0:1883 started.
Listener ws:default on 0.0.0.0:8083 started.
Listener wss:default on 0.0.0.0:8084 started.
Listener http:dashboard on :18083 started.
2024-02-02T01:44:16.451589+00:00 [error] msg: postgresql_parse_failed, mfa: emqx_postgresql:prepare_sql_to_conn/3(667), driver_error_code: <<"42703">>, driver_error_codename: undefined_column, driver_error_extra: [{file,<<"parse_relation.c">>},{line,<<"3589">>},{position,<<"96">>},{routine,<<"errorMissingColumn">>},{severity,<<"ERROR">>}], driver_error_message: column "m1528" does not exist, driver_severity: error, name: <<"emqx_postgresql:1">>, sql: [<<"SELECT action, permission, topic FROM mqtt_acl where is_del=0 and is_enable=1 and   username = m1528 ">>]
2024-02-02T01:44:16.451803+00:00 [error] msg: <<"postgresql_init_prepare_statement_failed">>, mfa: emqx_postgresql:init_prepare/1(606), reason: #{error_code => <<"42703">>,error_codename => undefined_column,severity => error}
2024-02-02T01:44:16.456386+00:00 [error] msg: postgresql_parse_failed, mfa: emqx_postgresql:prepare_sql_to_conn/3(667), driver_error_code: <<"42703">>, driver_error_codename: undefined_column, driver_error_extra: [{file,<<"parse_relation.c">>},{line,<<"3589">>},{position,<<"96">>},{routine,<<"errorMissingColumn">>},{severity,<<"ERROR">>}], driver_error_message: column "m1528" does not exist, driver_severity: error, name: <<"emqx_postgresql:1">>, sql: [<<"SELECT action, permission, topic FROM mqtt_acl where is_del=0 and is_enable=1 and   username = m1528 ">>]

服务环境：emqx单机部署 基于docker-compose配置文件

    environment:
      - "EMQX_NODE_NAME=emqx@node1.emqx.io"
      - "EMQX_CLUSTER__DISCOVERY_STRATEGY=static"
      - "EMQX_CLUSTER__STATIC__SEEDS=[emqx@node1.emqx.io]"
      # 配置授权数据库
      - "EMQX_AUTHORIZATION__CACHE__ENABLE=true"
      - "EMQX_AUTHORIZATION__NO_MATCH=deny"
      - "EMQX_AUTHORIZATION__DENY_ACTION=ignore"
      - "EMQX_AUTHORIZATION__SOURCES=[{
                                          enable = true,
                                          type = postgresql,
                                          database = \"mqtt_user\",
        username = \"mqtt_user\",
        password = \"mqtt_user\",
        server = \"xxxxxx:15432\",
        query = \"SELECT action, permission, topic FROM mqtt_acl where is_del=0 and is_enable=1 and username = ${username} \"
        }]"

尝试操作，映射内部配置文件 emqx.conf 配置正常。
基于docker文件下的 environment出现类似的错误问题。
请问 emqx不支持，还是我的配置操作出现问题

2

2024-02-02T01:44:16.451589+00:00 [error] msg: postgresql_parse_failed, mfa: emqx_postgresql:prepare_sql_to_conn/3(667), driver_error_code: <<“42703”>>, driver_error_codename: undefined_column, driver_error_extra: [{file,<<“parse_relation.c”>>},{line,<<“3589”>>},{position,<<“96”>>},{routine,<<“errorMissingColumn”>>},{severity,<<“ERROR”>>}], driver_error_message: column “m1528” does not exist, driver_severity: error, name: <<“emqx_postgresql:1”>>, sql: [<<"SELECT action, permission, topic FROM mqtt_acl where is_del=0 and is_enable=1 and username = m1528 ">>]

从这个错误日志看，应该是有列的名称不存在，或者是 sql 的语法错误了

3

已解决，不是emqx问题。