Configuration problem for configuring auth with mysql

[jmbiaudis]

Hello,

I have tried to configure the plugin, in a virtual machine with Ubuntu and mosquitto version 2.0.14 ans a local MariaDB, using the example in the README for MySQL.

I have set a user "client" with a password "test" where I want to test to subscribe or publish with a basic ACL.

Furthermore, I've tried different value in DB for "rw" like 0,1,2,4,10 following some issue (like issues 225, 216) found on github but not getting working, as I get rejected by the following error.

And I'm getting these kinds of error:

root@ubuntu: mosquitto_sub -h 127.0.0.1 -p 1883 -d -t testTopic -u client -P test Client null sending CONNECT Client null received CONNACK (5) Connection error: Connection Refused: not authorised. Client null sending DISCONNECT

root@ubuntu:~ mosquitto_sub -h 127.0.0.1 -p 1883 -d -t testTopic Client null sending CONNECT Client null received CONNACK (5) Connection error: Connection Refused: not authorised. Client null sending DISCONNECT root@ubuntu:~

root@ubuntu: mosquitto_pub -h 127.0.0.1 -t testTopic -u client -P test -m 'helloWorld' Connection error: Connection Refused: not authorised. Error: The connection was refused. root@ubuntu:~

In my log file i have :

2022-06-17T14:48:51: Sending CONNACK to auto-13FE1CDE-D930-0A4E-0F63-0F21844DD5E8 (0, 5) 2022-06-17T14:48:51: Client auto-13FE1CDE-D930-0A4E-0F63-0F21844DD5E8 disconnected, not authorised. 2022-06-17T14:48:55: New connection from 127.0.0.1:48656 on port 1883. 2022-06-17T14:48:55: Sending CONNACK to auto-10795E3A-1E8A-51E8-A8F6-2C4D4ACC991D (0, 5) 2022-06-17T14:48:55: Client auto-10795E3A-1E8A-51E8-A8F6-2C4D4ACC991D disconnected, not authorised. 2022-06-17T14:49:38: New connection from 127.0.0.1:48658 on port 1883. 2022-06-17T14:49:38: Sending CONNACK to auto-2E3781C2-633D-D01C-7053-B49680EBB53F (0, 5) 2022-06-17T14:49:38: Client auto-2E3781C2-633D-D01C-7053-B49680EBB53F disconnected, not authorised.

My config file are like this :

root@ubuntu:~# cat /etc/mosquitto/mosquitto.conf

persistence true persistence_location /home/mosquitto/data/ log_dest file /var/log/mosquitto/mosquitto.log include_dir /etc/mosquitto/conf.d pid_file /home/mosquitto/mosquitto.pid

my conf in conf.d :

listener 1883

log_type debug log_type error log_type warning log_type notice log_timestamp true log_timestamp_format %Y-%m-%dT%H:%M:%S autosave_interval 1800 allow_anonymous false plugin /etc/mosquitto/conf.d/go-auth.so auth_opt_backends mysql auth_opt_retry_count 5 auth_opt_mysql_protocol unix auth_opt_mysql_socket /run/mysqld/mysqld.sock auth_opt_mysql_allow_native_passwords true auth_opt_mysql_dbname go_auth_test auth_opt_mysql_user go_auth_test auth_opt_mysql_password go_auth_test plugin /etc/mosquitto/conf.d/go-auth.so

auth_opt_mysql_userquery select password_hash from test_user where username = ? limit 1 auth_opt_mysql_superquery select count(*) from test_user where username = ? and is_admin = True auth_opt_mysql_aclquery SELECT topic FROM test_acl,test_user WHERE (username = ?) AND rw = ? auth_opt_log_level debug auth_opt_log_level stderr auth_opt_log_level stdout auth_opt_log_level file

auth_opt_log_file /var/log/mosquitto/mosquitto-auth.log

In MariaDB i have :

MariaDB [go_auth_test]> select * from test_user ; +----+----------+------------------------------------------------------------------------------------------------------------------+----------+ | id | username | password_hash | is_admin | +----+----------+------------------------------------------------------------------------------------------------------------------+----------+ | 1 | client | $7$101$vyGjmT3FDMZ/4DS2$ODSLTbBVisI165T6FiQC+S5oe7GSj/mTaTCUx6sbPfjWJyGLc/4S+6smuvn5s5CluUMCLGOqJMNC9SR8xCGyvw== | 1 | +----+----------+------------------------------------------------------------------------------------------------------------------+----------+ 1 row in set (0.001 sec)

MariaDB [go_auth_test]> select * from test_acl ; +----+--------------+-----------+----+ | id | test_user_id | topic | rw | +----+--------------+-----------+----+ | 1 | 1 | testTopic | 2 | +----+--------------+-----------+----+ 1 row in set (0.001 sec)

MariaDB [go_auth_test]>

I'm not sure if I missed or misunderstanding something on the configuration of mqtt , the plugin or the backend but couldn't figure out what's wrong, any suggestions?

Thanks in advance

//English is not my native Language

[iegomez]

I'm not sure what's the issue but at least this query seems wrong:

SELECT topic FROM test_acl,test_user WHERE (username = ?) AND rw = ?

You're referencing 2 tables but not fully qualifying the fields nor doing any joins, seems like a typo to me.

Also, auth_opt_log_level is incorrectly set, these are not valid options for it:

auth_opt_log_level stderr
auth_opt_log_level stdout
auth_opt_log_level file

Fixing that should give you more insight on to what's going on.

[iegomez]

I'm closing this issue since there was no response (for a really long time) after my suggestion.