I installed mosquito broker and the postgres database. Downloaded and configured the mosquitto-go-auth plugin.
mosquitto status:
Loading config file /etc/mosquitto/conf.d/go-auth.conf
time="2024-02-02T21:00:08+03:00" level=info msg="backend registered: Postgres"
time="2024-02-02T21:00:08+03:00" level=info msg="registered acl checker: postgres"
time="2024-02-02T21:00:08+03:00" level=info msg="registered user checker: postgres"
time="2024-02-02T21:00:08+03:00" level=info msg="registered superuser checker: postgres"
фtime="2024-02-02T21:00:08+03:00" level=info msg="No cache set."
time="2024-02-02T21:01:00+03:00" level=debug msg="checking user Frogger with backend Postgres"
time="2024-02-02T21:09:57+03:00" level=debug msg="checking user Frogger with backend Postgres"
mosquitto.conf
persistence true
persistence_location /var/lib/mosquitto/
log_dest file /var/log/mosquitto/mosquitto.log
include_dir /etc/mosquitto/conf.d
conf.d:
listener 1883
auth_plugin /etc/mosquitto/conf.d/go-auth.so
auth_opt_log_level debug
require_certificate false
# postgress options:
auth_opt_backends postgres
auth_opt_pg_host 127.0.0.1
auth_opt_pg_port 5432
auth_opt_pg_dbname go_auth_test
auth_opt_pg_user postgres
auth_opt_pg_password postgres
# hash options:
auth_opt_pg_hasher bcrypt
#auth_opt_pg_hasher argon2id
auth_opt_pg_hasher_salt_size 16 # salt bytes length
#auth_opt_pg_hasher_iterations 3 # number of iterations
#auth_opt_pg_hasher_keylen 64 # key length
#auth_opt_pg_hasher_memory 4096 # amount of memory (in kibibytes) to use
#auth_opt_pg_hasher_parallelism # degree of parallelism (i.e. number of threads)
#ssl
auth_opt_pg_sslmode disable
#auth_opt_pg_sslcert /etc/mosquitto/certs/mosquitto.crt
#auth_opt_pg_sslkey /etc/mosquitto/certs/mosquitto.key
#auth_opt_pg_sslrootcert /etc/mosquitto/certs/mosquittoroot.crt
auth_opt_pg_connect_tries 5
auth_opt_pg_userquery select password_hash from "test_user" where username = $1 limit 1;
#auth_opt_pg_userquery select password_hash from "test_user" where username = "$1" limit 1;
auth_opt_pg_superquery select count(*) from "user" where username = $1 and is_admin = true
mosquitto.log:
1706896808: mosquitto version 2.0.18 starting
1706896808: Config loaded from /etc/mosquitto/mosquitto.conf.
1706896808: Loading plugin: /etc/mosquitto/conf.d/go-auth.so
1706896808: ├── Username/password checking enabled.
1706896808: ├── TLS-PSK checking enabled.
1706896808: └── Extended authentication not enabled.
1706896808: Opening ipv4 listen socket on port 1883.
1706896808: Opening ipv6 listen socket on port 1883.
1706896808: mosquitto version 2.0.18 running
1706896860: New connection from 127.0.0.1:58676 on port 1883.
1706896860: Client auto-9013899F-0297-0F48-2C18-26A3DF7734F8 disconnected, not authorised.
1706897397: New connection from 127.0.0.1:46014 on port 1883.
1706897397: Client auto-8BCBE732-65A6-ADD8-EE81-6120DFAD448E disconnected, not authorised.
1706897597: New connection from 127.0.0.1:54366 on port 1883.
1706897597: Client auto-2DFF5060-31E2-C126-1573-42CA3529E290 disconnected, not authorised.
1706898022: New connection from 127.0.0.1:59730 on port 1883.
1706898022: Client auto-1527B628-3033-9B8D-3044-1F131D8D24E9 disconnected, not authorised.
postgres.log:
2024-02-02 21:20:22.786 MSK [73857] postgres@go_auth_test DEBUG: parse <unnamed>: select password_hash from "test_user" where username = $1 limit 1;
2024-02-02 21:20:22.786 MSK [73857] postgres@go_auth_test STATEMENT: select password_hash from "test_user" where username = $1 limit 1;
2024-02-02 21:20:22.787 MSK [73857] postgres@go_auth_test LOG: duration: 0.132 ms parse <unnamed>: select password_hash from "test_user" where username = $1 limit 1;
2024-02-02 21:20:22.787 MSK [73857] postgres@go_auth_test DEBUG: bind <unnamed> to <unnamed>
2024-02-02 21:20:22.787 MSK [73857] postgres@go_auth_test LOG: duration: 0.125 ms bind <unnamed>: select password_hash from "test_user" where username = $1 limit 1;
2024-02-02 21:20:22.787 MSK [73857] postgres@go_auth_test DETAIL: parameters: $1 = 'Frogger'
2024-02-02 21:20:22.787 MSK [73857] postgres@go_auth_test LOG: duration: 0.020 ms execute <unnamed>: select password_hash from "test_user" where username = $1 limit 1;
2024-02-02 21:20:22.787 MSK [73857] postgres@go_auth_test DETAIL: parameters: $1 = 'Frogger'
2024-02-02 21:20:28.452 MSK [73839] DEBUG: received inquiry for database 0
2024-02-02 21:20:28.452 MSK [73839] DEBUG: writing stats file "/var/run/postgresql/14-main.pg_stat_tmp/global.stat"
2024-02-02 21:20:28.452 MSK [73839] DEBUG: writing stats file "/var/run/postgresql/14-main.pg_stat_tmp/db_0.stat"
2024-02-02 21:20:28.469 MSK [73923] DEBUG: InitPostgres
2024-02-02 21:20:28.472 MSK [73923] DEBUG: autovacuum: processing database "go_auth_test"
2024-02-02 21:20:28.473 MSK [73839] DEBUG: received inquiry for database 16385
2024-02-02 21:20:28.473 MSK [73839] DEBUG: writing stats file "/var/run/postgresql/14-main.pg_stat_tmp/global.stat"
2024-02-02 21:20:28.473 MSK [73839] DEBUG: writing stats file "/var/run/postgresql/14-main.pg_stat_tmp/db_16385.stat"
2024-02-02 21:20:28.473 MSK [73839] DEBUG: writing stats file "/var/run/postgresql/14-main.pg_stat_tmp/db_0.stat"
2024-02-02 21:20:28.484 MSK [73923] DEBUG: test_acl: vac: 0 (threshold 50), ins: 0 (threshold 1000), anl: 0 (threshold 50)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: test_user: vac: 0 (threshold 50), ins: 3 (threshold 1000), anl: 3 (threshold 50)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_statistic: vac: 0 (threshold 131), ins: 0 (threshold 1081), anl: 0 (threshold 91)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_type: vac: 0 (threshold 170), ins: 4 (threshold 1120), anl: 4 (threshold 110)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_authid: vac: 1 (threshold 52), ins: 1 (threshold 1002), anl: 2 (threshold 51)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_attribute: vac: 2 (threshold 644), ins: 40 (threshold 1594), anl: 42 (threshold 347)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_proc: vac: 0 (threshold 690), ins: 36 (threshold 1640), anl: 36 (threshold 370)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_class: vac: 3 (threshold 129), ins: 6 (threshold 1079), anl: 9 (threshold 90)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_attrdef: vac: 0 (threshold 50), ins: 2 (threshold 1000), anl: 2 (threshold 50)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_constraint: vac: 0 (threshold 71), ins: 3 (threshold 1021), anl: 3 (threshold 61)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_index: vac: 0 (threshold 81), ins: 2 (threshold 1031), anl: 2 (threshold 66)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_operator: vac: 0 (threshold 211), ins: 0 (threshold 1161), anl: 0 (threshold 130)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_opclass: vac: 0 (threshold 85), ins: 0 (threshold 1035), anl: 0 (threshold 68)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_am: vac: 0 (threshold 51), ins: 0 (threshold 1001), anl: 0 (threshold 51)
2024-02-02 21:20:28.484 MSK [73923] DEBUG: pg_amop: vac: 0 (threshold 239), ins: 0 (threshold 1189), anl: 0 (threshold 144)
go_user_test.db:
id | username | password_hash | is_admin
----+----------+--------------------------------------------------------------+----------
1 | Frog | 1234 | f
2 | Frogger | $2a$06$trR7wxzdqvdbm/hFOxYChuXoyy2SXkbOBexssLQRbEpjaKP10QmLO | f
3 | Frogger | $2a$16$TKu7U8IBkMuYg0KVH6/EzOheUA53D6izmHhArQMCFD8qJ9gEXVXDO | f
4 | Frogger | $2b$12$skzMpzs80BukRP5DEQsaUOfRU5vZuwpMIS.WuASuXM7ApOP6kbhAi | f
how I insert data in database:
INSERT INTO test_user (id,username, password_hash, is_admin)
VALUES (3,'Frogger', crypt('1234', gen_salt('bf', 16)), false);
command to connect:
mosquitto_sub -h localhost -t test -u "Frogger" -P "1234"
Every time I try to connect, I get an error:
Connection error: Connection Refused: not authorised.
and after i use command sudo systemctl status mosquitto:
error: received null username or password for unpwd check
However, I am sure that the password and login is correct (I showed how I enter the password into the database). I can't understand why I'm getting an error, please help me
As per the comments, the cause of your initial issue is unclear (having an underscore in the field name should not be an issue). To help you present an MRE (and possibly point you towards a solution) here is the docker compose setup I created to (roughly!) replicate your setup (this is not fit for production use!).
docker-compose.yml
version: '3.6'
services:
mosquitto:
depends_on:
db:
condition: service_healthy
image: iegomez/mosquitto-go-auth
volumes:
- type: bind
source: ./mosquittocfg
target: /etc/mosquitto
ports:
- target: 1883
published: 1883
protocol: tcp
mode: host
logging:
driver: json-file
options:
max-size: 5m
db:
image: postgres:15-alpine
restart: unless-stopped
healthcheck:
test: ["CMD-SHELL", "pg_isready -d authdb -U authuser"]
interval: 5s
timeout: 5s
retries: 20
start_period: 30s
volumes:
- type: bind
source: ./dbinit
target: /docker-entrypoint-initdb.d
environment:
POSTGRES_DB: authdb
POSTGRES_USER: authuser
POSTGRES_PASSWORD: authpassword
ports:
- target: 5432
published: 5432
protocol: tcp
mode: host
logging:
driver: json-file
options:
max-size: 5m
./dbinit/goauthtest.sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
create table mqtt_user (
username TEXT NOT NULL,
password_hash TEXT NOT NULL,
is_admin BOOLEAN NOT NULL
);
insert into mqtt_user(username, password_hash, is_admin) VALUES ('Frogger', crypt('1234', gen_salt('bf', 16)), false)
./mosquittocfg/mosquitto.conf
log_type all
listener 1883
auth_plugin /mosquitto/go-auth.so
auth_opt_log_level debug
# postgress options:
auth_opt_backends postgres
auth_opt_pg_host db
auth_opt_pg_port 5432
auth_opt_pg_dbname authdb
auth_opt_pg_user authuser
auth_opt_pg_password authpassword
auth_opt_pg_hasher bcrypt
auth_opt_pg_sslmode disable
auth_opt_pg_connect_tries 5
auth_opt_pg_userquery select password_hash from mqtt_user where username = $1 limit 1;