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

[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)