Documentation for generating salted password lacking

[nnilsson123]

The documentation describes the process of how to generate a salted password. However, the description on point 2 really makes it very difficult to follow. It is described that "same salting rules and hash method as MySQL authentication" should be used. Presumably, this is the method referenced. This method however utilizes SHA1, whereas emqx_auth_pgsql also supports other cryptographic algorithms. In addition, there is no description about how the salt enters the equation (presumably in place of the randomized 20-bit sequence).

This really needs to be improved. I still cannot wrap my head around how to generate hashed passwords and salts in order to make the plugin able to authenticate.

IMHO, the plugin should use the methods available for cryptography in PostgreSQL, i.e. the pgcrypto crypto method, specifically designed for managing cryptography for passwords.

[nnilsson123]

The issue has to do with the hexstring method generating a lower-case string of the hash in hexadecimal. The hash in my database was upper-case, and the comparison is case-sensitive. This should really either be documented more carefully, or fixed!

Storing hashes in my database in lower-case solved my issue.

[danieledwardgeorgehitchcock]

Did you manage to resolve this?

I have created the tables from the EMQX documentation and also enabled the pgcrypto extension in my PostgreSQL database and have created the following Stored Procedure for creating users:

CREATE OR REPLACE PROCEDURE add_mqtt_user (username TEXT, password TEXT, is_superuser BOOLEAN DEFAULT FALSE)
LANGUAGE plpgsql
AS $$
DECLARE
salt TEXT := GEN_SALT('md5');
BEGIN
INSERT INTO mqtt_user(username, password_hash, is_superuser, salt)
SELECT username
      ,ENCODE(DIGEST(CONCAT(password, salt), 'sha256'), 'hex')
      ,is_superuser
      ,salt;
END;
$$;

I am able to generate a sha256 hash which is the same as what I can generate using the following terminal command: echo -n sha256salt | sha256sum

I have configured the PostgreSQL authentication settings in the web UI with password hash: sha256 and salt: suffix however, when I try and authenticate with my client, I get the following error(s):

[error] tag: AUTHN, clientid: 7d7K78wicxaMPI4UL0a5Eb, msg: postgresql_query_failed, peername: 172.19.0.3:38131, username: sha256, reason: {unrecoverable_error,#{severity => error,error_code => <<"42501">>,error_codename => insufficient_privilege}}, params: [<<"sha256">>], resource: <<"emqx_authn_postgresql:780">>, provider: emqx_authn_postgresql

[warning] tag: AUTHN, clientid: 7d7K78wicxaMPI4UL0a5Eb, msg: authentication_failure, peername: 172.19.0.3:38131, username: sha256, reason: not_authorized

I must note, that that the "salted hash" that in the tests folder of this repo appears to actually be unsalted so, maybe it is something I am doing incorrectly..?

[danieledwardgeorgehitchcock]

Edit: Fixed the above issue by executing the following on the PostgreSQL database:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO emqx