Migrate from mosquitto-go-auth to EMQX-auth-pgsql

[win5923]

What happened?

I plan to migrate from Mosquitto to EMQX, but I'm facing some issues with the auth configuration. Here's my setup part.

mosquitto.conf:

auth_opt_backends postgres
auth_opt_hasher pbkdf2
auth_opt_hasher_salt_size 12
auth_opt_hasher_iterations 901
auth_opt_hasher_keylen 24
auth_opt_hasher_algorithm sha256
auth_opt_hasher_salt_encoding utf-8

auth_opt_pg_userquery SELECT password FROM account WHERE name = $1 limit 1
auth_opt_pg_superquery SELECT COALESCE(COUNT(*),0) FROM account WHERE name = $1 AND super = 1
auth_opt_aclquery SELECT topic FROM acls WHERE (name = $1) AND (rw & $2) > 0

EMQX-dashboard pgsql auth:

The EMQX dashboard indicates a successful connection to PostgreSQL, but I'm encountering issues with client authentication. Using the same username and password for authentication in Mosquitto works fine. Here's the EMQX log.

EMQX log:

EMQX 5.2.1 is running now!
2024-04-01T05:40:56.486882+00:00 [warning] msg: authorization_permission_denied, mfa: emqx_authz:authorize_non_superuser/5, line: 420, peername: 10.224.0.164:57140, clientid: mqtt-explorer-587b0fc9, topic: #, ipaddr: {10,224,0,164}, source: file, username: <<"1">>
2024-04-01T05:40:56.487109+00:00 [warning] msg: authorization_permission_denied, mfa: emqx_authz:authorize_non_superuser/5, line: 420, peername: 10.224.0.164:57140, clientid: mqtt-explorer-587b0fc9, topic: $SYS/#, ipaddr: {10,224,0,164}, source: file, username: <<"1">>
2024-04-01T05:41:28.132292+00:00 [warning] msg: alarm_is_activated, mfa: emqx_alarm:do_actions/3, line: 418, message: <<"VM is overloaded on node: 'emqx@emqx-core-68dbc754-0.emqx-headless.emqx.svc.cluster.local': 279">>, name: runq_overload
2024-04-01T05:42:05.160157+00:00 [warning] msg: alarm_is_deactivated, mfa: emqx_alarm:do_actions/3, line: 424, name: runq_overload
2024-04-01T05:43:18.891839+00:00 [warning] msg: authorization_permission_denied, mfa: emqx_authz:authorize_non_superuser/5, line: 420, peername: 10.224.3.107:60922, clientid: mqtt-explorer-587b0fc9, topic: #, ipaddr: {10,224,3,107}, source: file, username: <<"1">>
2024-04-01T05:43:18.892122+00:00 [warning] msg: authorization_permission_denied, mfa: emqx_authz:authorize_non_superuser/5, line: 420, peername: 10.224.3.107:60922, clientid: mqtt-explorer-587b0fc9, topic: $SYS/#, ipaddr: {10,224,3,107}, source: file, username: <<"1">>
2024-04-01T05:58:59.117724+00:00 [error] Generic server <0.1748.1> terminating. Reason: {function_clause,[{lists,zip,[[text],[]],[{file,"lists.erl"},{line,419}]},{epgsql_cmd_prepared_query2,execute,2,[{file,"epgsql_cmd_prepared_query2.erl"},{line,47}]},{epgsql_sock,command_exec,4,[{file,"epgsql_sock.erl"},{line,393}]},{gen_server,try_handle_call,4,[{file,"gen_server.erl"},{line,1149}]},{gen_server,handle_msg,6,[{file,"gen_server.erl"},{line,1178}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}. Last message: {command,epgsql_cmd_prepared_query2,{<<"emqx_authn_pgsql:8482">>,[]}}. State: {state,gen_tcp,#Port<0.4530>,<<>>,{24548,1789288213},on_message,{codec,#{},[null,undefined],{oid_db,#{16 => {type,16,bool,false,undefined,epgsql_codec_boolean,[]},1041 => {type,1041,inet,true,869,epgsql_codec_net,[]},18 => {type,18,char,false,undefined,epgsql_codec_bpchar,[]},1115 => {type,1115,timestamp,true,1114,epgsql_codec_datetime,epgsql_idatetime},651 => {type,651,cidr,true,650,epgsql_codec_net,[]},114 => {type,114,json,false,undefined,epgsql_codec_json,[]},1022 => {type,1022,float8,true,701,epgsql_codec_float,[]},3912 => {type,3912,daterange,false,undefined,epgsql_codec_timerange,epgsql_idatetime},3909 => {type,3909,tsrange,true,3908,epgsql_codec_timerange,epgsql_idatetime},1184 => {type,1184,timestamptz,false,undefined,epgsql_codec_datetime,epgsql_idatetime},1114 => {type,1114,timestamp,false,undefined,epgsql_codec_datetime,epgsql_idatetime},1270 => {type,1270,timetz,true,1266,epgsql_codec_datetime,epgsql_idatetime},1042 => {type,1042,bpchar,false,undefined,epgsql_codec_bpchar,[]},600 => {type,600,point,false,undefined,epgsql_codec_geometric,[]},3904 => {type,3904,int4range,false,undefined,epgsql_codec_intrange,[]},700 => {type,700,float4,false,undefined,epgsql_codec_float,[]},1185 => {type,1185,timestamptz,true,1184,epgsql_codec_datetime,epgsql_idatetime},1266 => {type,1266,timetz,false,undefined,epgsql_codec_datetime,epgsql_idatetime},650 => {type,650,cidr,false,undefined,epgsql_codec_net,[]},1016 => {type,1016,int8,true,20,epgsql_codec_integer,[]},1021 => {type,1021,float4,true,700,epgsql_codec_float,[]},21 => {type,21,int2,false,undefined,epgsql_codec_integer,[]},1017 => {type,1017,point,true,600,epgsql_codec_geometric,[]},1182 => {type,1182,date,true,1082,epgsql_codec_datetime,epgsql_idatetime},3908 => {type,3908,tsrange,false,undefined,epgsql_codec_timerange,epgsql_idatetime},1005 => {type,1005,int2,true,21,epgsql_codec_integer,[]},17 => {type,17,bytea,false,undefined,epgsql_codec_text,[]},1002 => {type,1002,char,true,18,epgsql_codec_bpchar,[]},3910 => {type,3910,tstzrange,false,undefined,epgsql_codec_timerange,epgsql_idatetime},3927 => {type,3927,int8range,true,3926,epgsql_codec_intrange,[]},3807 => {type,3807,jsonb,true,3802,epgsql_codec_json,[]},1082 => {type,1082,date,false,undefined,epgsql_codec_datetime,epgsql_idatetime},3913 => {type,3913,daterange,true,3912,epgsql_codec_timerange,epgsql_idatetime},1183 => {type,1183,time,true,1083,epgsql_codec_datetime,epgsql_idatetime},20 => {type,20,int8,false,undefined,epgsql_codec_integer,[]},1007 => {type,1007,int4,true,23,epgsql_codec_integer,[]},1083 => {type,1083,time,false,undefined,epgsql_codec_datetime,epgsql_idatetime},2951 => {type,2951,uuid,true,2950,epgsql_codec_uuid,[]},2950 => {type,2950,uuid,false,undefined,epgsql_codec_uuid,[]},3911 => {type,3911,tstzrange,true,3910,epgsql_codec_timerange,epgsql_idatetime},1043 => {type,1043,varchar,false,undefined,epgsql_codec_text,[]},1014 => {type,1014,bpchar,true,1042,epgsql_codec_bpchar,[]},199 => {type,199,json,true,114,epgsql_codec_json,[]},25 => {type,25,text,false,undefined,epgsql_codec_text,[]},1009 => {type,1009,text,true,25,epgsql_codec_text,[]},1000 => {type,1000,bool,true,16,epgsql_codec_boolean,[]},701 => {type,701,float8,false,undefined,epgsql_codec_float,[]},3905 => {type,3905,int4range,true,3904,epgsql_codec_intrange,[]},1015 => {type,1015,varchar,true,1043,epgsql_codec_text,[]},3802 => {type,3802,jsonb,false,undefined,epgsql_codec_json,[]},829 => {type,829,macaddr,false,undefined,epgsql_codec_net,[]},1187 => {type,1187,interval,true,1186,epgsql_codec_datetime,epgsql_idatetime},774 => {type,774,macaddr8,false,undefined,epgsql_codec_net,[]},1186 => {type,1186,interval,false,undefined,epgsql_codec_datetime,epgsql_idatetime},1040 => {type,1040,macaddr,true,829,epgsql_codec_net,[]},3926 => {type,3926,int8range,false,undefined,epgsql_codec_intrange,[]},775 => {type,775,macaddr8,true,774,epgsql_codec_net,[]},23 => {type,23,int4,false,undefined,epgsql_codec_integer,[]},1001 => {type,1001,bytea,true,17,epgsql_codec_text,[]},869 => {type,869,inet,false,undefined,epgsql_codec_net,[]}},#{{point,false} => 600,{interval,false} => 1186,{int8range,true} => 3927,{tstzrange,true} => 3911,{int8range,false} => 3926,{int4range,false} => 3904,{uuid,true} => 2951,{timestamptz,true} => 1185,{bpchar,false} => 1042,{int2,true} => 1005,{float8,false} => 701,{bytea,true} => 1001,{bytea,false} => 17,{int4,true} => 1007,{char,true} => 1002,{cidr,false} => 650,{inet,false} => 869,{timestamptz,false} => 1184,{macaddr,false} => 829,{daterange,true} => 3913,{cidr,true} => 651,{inet,true} => 1041,{json,true} => 199,{point,true} => 1017,{time,true} => 1183,{jsonb,true} => 3807,{varchar,false} => 1043,{float8,true} => 1022,{timestamp,true} => 1115,{json,false} => 114,{date,false} => 1082,{tsrange,false} => 3908,{timetz,true} => 1270,{macaddr8,true} => 775,{int4,false} => 23,{interval,true} => 1187,{tstzrange,false} => 3910,{float4,true} => 1021,{date,true} => 1182,{int4range,true} => 3905,{daterange,false} => 3912,{char,false} => 18,{uuid,false} => 2950,{tsrange,true} => 3909,{timestamp,false} => 1114,{int8,true} => 1016,{jsonb,false} => 3802,{text,true} => 1009,{bpchar,true} => 1014,{float4,false} => 700,{text,false} => 25,{time,false} => 1083,{bool,true} => 1000,{timetz,false} => 1266,{bool,false} => 16,{int2,false} => 21,{macaddr,true} => 1040,{int8,false} => 20,{varchar,true} => 1015,{macaddr8,false} => 774}}},{[],[]},undefined,undefined,undefined,undefined,[{<<"in_hot_standby">>,<<"off">>},{<<"integer_datetimes">>,<<"on">>},{<<"TimeZone">>,<<"UTC">>},{<<"IntervalStyle">>,<<"postgres">>},{<<"is_superuser">>,<<"off">>},{<<"application_name">>,<<>>},{<<"default_transaction_read_only">>,<<"off">>},{<<"scram_iterations">>,<<"4096">>},{<<"DateStyle">>,<<"ISO, MDY">>},{<<"standard_conforming_strings">>,<<"on">>},{<<"session_authorization">>,<<"getac">>},{<<"client_encoding">>,<<"UTF8">>},{<<"server_version">>,<<"16.0">>},{<<"server_encoding">>,<<"UTF8">>}],information_redacted,[],undefined,73,select,undefined,#{database => <<"mqtt">>,host => "test.postgres.database.azure.com",port => 5432,ssl => false},#{<<"emqx_authn_pgsql:8482">> => {statement,<<"emqx_authn_pgsql:8482">>,[{column,<<"password">>,varchar,1043,-1,132,1,24802,3}],[text],[{25,text,false}]}}}. Client <0.1773.1> stacktrace: [{gen,do_call,4,[{file,"gen.erl"},{line,237}]},{gen_server,call,3,[{file,"gen_server.erl"},{line,381}]},{emqx_connector_pgsql,prepared_query,3,[{file,"emqx_connector_pgsql.erl"},{line,383}]},{emqx_connector_pgsql,on_sql_query,5,[{file,"emqx_connector_pgsql.erl"},{line,243}]}].
2024-04-01T05:58:59.119303+00:00 [error] crasher: initial call: epgsql_sock:init/1, pid: <0.1748.1>, registered_name: [], error: {function_clause,[{lists,zip,[[text],[]],[{file,"lists.erl"},{line,419}]},{epgsql_cmd_prepared_query2,execute,2,[{file,"epgsql_cmd_prepared_query2.erl"},{line,47}]},{epgsql_sock,command_exec,4,[{file,"epgsql_sock.erl"},{line,393}]},{gen_server,try_handle_call,4,[{file,"gen_server.erl"},{line,1149}]},{gen_server,handle_msg,6,[{file,"gen_server.erl"},{line,1178}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}, ancestors: [<0.1747.1>,<0.1736.1>,<0.1734.1>,ecpool_sup,<0.2668.0>], message_queue_len: 0, messages: [], links: [<0.1747.1>], dictionary: [], trap_exit: false, status: running, heap_size: 46422, stack_size: 28, reductions: 75390; neighbours:
2024-04-01T05:58:59.119795+00:00 [error] msg: resource_exception, mfa: emqx_resource_buffer_worker:handle_query_result_pure/3, line: 859, peername: 10.224.0.187:35386, clientid: mqtt-explorer-587b0fc9, info: #{error => {exit,{{function_clause,[{lists,zip,[[text],[]],[{file,"lists.erl"},{line,419}]},{epgsql_cmd_prepared_query2,execute,2,[{file,"epgsql_cmd_prepared_query2.erl"},{line,47}]},{epgsql_sock,command_exec,4,[{file,"epgsql_sock.erl"},{line,393}]},{gen_server,try_handle_call,4,[{file,"gen_server.erl"},{line,1149}]},{gen_server,handle_msg,6,[{file,"gen_server.erl"},{line,1178}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]},{gen_server,call,[<0.1748.1>,{command,epgsql_cmd_prepared_query2,{<<"emqx_authn_pgsql:8482">>,[]}},infinity]}}},id => <<"emqx_authn_pgsql:8482">>,name => call_query,request => {prepared_query,<<"emqx_authn_pgsql:8482">>,[]},stacktrace => [{gen_server,call,3,[{file,"gen_server.erl"},{line,385}]},{emqx_connector_pgsql,prepared_query,3,[{file,"emqx_connector_pgsql.erl"},{line,383}]},{emqx_connector_pgsql,on_sql_query,5,[{file,"emqx_connector_pgsql.erl"},{line,243}]},{emqx_connector_pgsql,on_query,3,[{file,"emqx_connector_pgsql.erl"},{line,176}]},{emqx_resource_buffer_worker,apply_query_fun,8,[{file,"emqx_resource_buffer_worker.erl"},{line,1097}]},{emqx_resource_buffer_worker,simple_sync_query,3,[{file,"emqx_resource_buffer_worker.erl"},{line,153}]},{emqx_authn_pgsql,authenticate,2,[{file,"emqx_authn_pgsql.erl"},{line,127}]},{emqx_authentication,authenticate_with_provider,2,[{file,"emqx_authentication.erl"},{line,693}]},{emqx_authentication,do_authenticate,3,[{file,"emqx_authentication.erl"},{line,661}]},{emqx_authentication,authenticate,2,[{file,"emqx_authentication.erl"},{line,240}]},{emqx_hooks,safe_execute,2,[{file,"emqx_hooks.erl"},{line,200}]},{emqx_hooks,do_run_fold,3,[{file,"emqx_hooks.erl"},{line,180}]},{emqx_access_control,authenticate,1,[{file,"emqx_access_control.erl"},{line,176}]},{emqx_channel,do_authenticate,2,[{file,"emqx_channel.erl"},{line,1689}]},{emqx_channel,handle_in,2,[{file,"emqx_channel.erl"},{line,340}]},{emqx_ws_connection,with_channel,3,[{file,"emqx_ws_connection.erl"},{line,754}]},{cowboy_websocket,handler_call,6,[{file,"cowboy_websocket.erl"},{line,487}]},{proc_lib,wake_up,3,[{file,"proc_lib.erl"},{line,250}]}]}
2024-04-01T05:58:59.120309+00:00 [error] msg: postgresql_query_failed, mfa: emqx_authn_pgsql:authenticate/2, line: 144, peername: 10.224.0.187:35386, clientid: mqtt-explorer-587b0fc9, params: [], provider: emqx_authn_pgsql, reason: {resource_error,#{msg => #{error => {exit,{{function_clause,[{lists,zip,[[text],[]],[{file,"lists.erl"},{line,419}]},{epgsql_cmd_prepared_query2,execute,2,[{file,"epgsql_cmd_prepared_query2.erl"},{line,47}]},{epgsql_sock,command_exec,4,[{file,"epgsql_sock.erl"},{line,393}]},{gen_server,try_handle_call,4,[{file,"gen_server.erl"},{line,1149}]},{gen_server,handle_msg,6,[{file,"gen_server.erl"},{line,1178}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]},{gen_server,call,[<0.1748.1>,{command,epgsql_cmd_prepared_query2,{<<"emqx_authn_pgsql:8482">>,[]}},infinity]}}},id => <<"emqx_authn_pgsql:8482">>,name => call_query,request => {prepared_query,<<"emqx_authn_pgsql:8482">>,[]},stacktrace => [{gen_server,call,3,[{file,"gen_server.erl"},{line,385}]},{emqx_connector_pgsql,prepared_query,3,[{file,"emqx_connector_pgsql.erl"},{line,383}]},{emqx_connector_pgsql,on_sql_query,5,[{file,"emqx_connector_pgsql.erl"},{line,243}]},{emqx_connector_pgsql,on_query,3,[{file,"emqx_connector_pgsql.erl"},{line,176}]},{emqx_resource_buffer_worker,apply_query_fun,8,[{file,"emqx_resource_buffer_worker.erl"},{line,1097}]},{emqx_resource_buffer_worker,simple_sync_query,3,[{file,"emqx_resource_buffer_worker.erl"},{line,153}]},{emqx_authn_pgsql,authenticate,2,[{file,"emqx_authn_pgsql.erl"},{line,127}]},{emqx_authentication,authenticate_with_provider,2,[{file,"emqx_authentication.erl"},{line,693}]},{emqx_authentication,do_authenticate,3,[{file,"emqx_authentication.erl"},{line,661}]},{emqx_authentication,authenticate,2,[{file,"emqx_authentication.erl"},{line,240}]},{emqx_hooks,safe_execute,2,[{file,"emqx_hooks.erl"},{line,200}]},{emqx_hooks,do_run_fold,3,[{file,"emqx_hooks.erl"},{line,180}]},{emqx_access_control,authenticate,1,[{file,"emqx_access_control.erl"},{line,176}]},{emqx_channel,do_authenticate,2,[{file,"emqx_channel.erl"},{line,1689}]},{emqx_channel,handle_in,2,[{file,"emqx_channel.erl"},{line,340}]},{emqx_ws_connection,with_channel,3,[{file,"emqx_ws_connection.erl"},{line,754}]},{cowboy_websocket,handler_call,6,[{file,"cowboy_websocket.erl"},{line,487}]},{proc_lib,wake_up,3,[{file,"proc_lib.erl"},{line,250}]}]},reason => exception}}, resource: <<"emqx_authn_pgsql:8482">>, tag: AUTHN

What did you expect to happen?

The client authentication was successful.

How can we reproduce it (as minimally and precisely as possible)?

No response

Anything else we need to know?

No response

EMQX version

$ ./bin/emqx_ctl broker
sysdescr  : EMQX
version   : 5.5.1
datetime  : 2024-04-01T02:35:10.551877443+00:00
uptime    : 1 hours, 9 minutes, 33 seconds```

</details>


### OS version

<details>

```console
# On Linux:
$ cat /etc/os-release
PRETTY_NAME="Ubuntu 22.04.4 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.4 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy
$ uname -a
Linux aks-memory-41202852-vmss000000 5.15.0-1058-azure #66-Ubuntu SMP Fri Feb 16 00:40:24 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

Log files

[id]

@win5923 try to use ${username} instead of $1.

[win5923]

@win5923 try to use ${username} instead of $1.

Hi @id, I transitioned from no match to Deny status, and there are no error messages in the EMQX logs. Currently, I suspect that the encoding methods may differ, as my mosquitto-auth is using UTF-8. Do you have any ideas?

[zmstone]

Hi @win5923
Might be due to password hash parameter or salt mismatch.
Maybe try to test with a different hash algorithm to verify ?

btw. the field is better to be named password_hash instead of password (both works, but password is considered deprecated).

[win5923]

Hi @zmstone, I try to use password_hash but got error <<"column \"password_hash\" does not exist">>:

2024-04-10T01:58:10.384330+00:00 [error] msg: PostgreSQL parse failed, mfa: emqx_connector_pgsql:prepare_sql_to_conn/3, line: 506, error: {error,error,<<"42703">>,undefined_column,<<"column \"password_hash\" does not exist">>,[{file,<<"parse_relation.c">>},{line,<<"3718">>},{position,<<"8">>},{routine,<<"errorMissingColumn">>},{severity,<<"ERROR">>}]}, name: <<"emqx_authn_pgsql:130">>, prepare_sql: <<"SELECT password_hash FROM account where name = $1 LIMIT 1">>
2024-04-10T01:58:10.384557+00:00 [error] msg: <<"PostgreSQL init prepare statement failed">>, mfa: emqx_connector_pgsql:init_prepare/1, line: 458, error: {error,{error,error,<<"42703">>,undefined_column,<<"column \"password_hash\" does not exist">>,[{file,<<"parse_relation.c">>},{line,<<"3718">>},{position,<<"8">>},{routine,<<"errorMissingColumn">>},{severity,<<"ERROR">>}]}}
2024-04-10T01:58:10.391319+00:00 [error] msg: PostgreSQL parse failed, mfa: emqx_connector_pgsql:prepare_sql_to_conn/3, line: 506, error: {error,error,<<"42703">>,undefined_column,<<"column \"password_hash\" does not exist">>,[{file,<<"parse_relation.c">>},{line,<<"3718">>},{position,<<"8">>},{routine,<<"errorMissingColumn">>},{severity,<<"ERROR">>}]}, name: <<"emqx_authn_pgsql:130">>, prepare_sql: <<"SELECT password_hash FROM account where name = $1 LIMIT 1">>
2024-04-10T01:58:10.391516+00:00 [warning] msg: alarm_is_activated, mfa: emqx_alarm:do_actions/3, line: 418, message: <<"resource down: Unknown reason">>, name: <<"emqx_authn_pgsql:130">>

I have tried different Password Hash methods, but the situation remains the same. The correct username and password are still being denied.

I'm not creating a new table; instead, I intend to use the table previously utilized by Mosquitto auth.

[zmstone]

I understand that your database has a column named password but not password_hash.
What I meant as a "btw", was that EMQX can handle password ok, but it's an alias, the formal query result should return passord_hash.
This is likely not related to the issue, but if you do want to match EMQX's formal query result, you can use
SELECT password AS password_hash ...

[zmstone]

If you share an EXAMPLE password with us, we might be able to help you compare the hash value.
It would require:

• plaintext password sent by the client
• hash stored in postgres
• although the hash algorithm and parameters can be observed from the screenshot above, it would be nice if you can share how the password is hashed before stored in postgres
• the salt for the password hash (if any)