search

emqx / emqx

The most scalable open-source MQTT broker for IoT, IIoT, and connected vehicles
https://www.emqx.com/
Other
13.53k stars 2.18k forks source link

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

Open win5923 opened 3 months ago

win5923 commented 3 months ago

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: image

image

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

```console $ ./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```

OS version

```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 commented 3 months ago

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

win5923 commented 3 months ago

@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? image

zmstone commented 2 months ago

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 commented 2 months ago

image 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 commented 2 months ago

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 commented 2 months ago

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