How to set up mosquitto-go-auth for postgresql in docker

Setup and configuration
1

Hi ,
I am trying to set up mosquitto-go-auth for postgresql and want to test end-to-end so mentioned below all the step what i have tried .
Gone through https://github.com/iegomez/mosquitto-go-auth/ and tried to build docker image using docker-compose.yml which is there in repo.
In docker-compose.yml:

version: "3"

services:

  postgres:

    image: postgres:9.0

    volumes:

      - psql:/var/lib/postgresql/data

    environment:

      - 'POSTGRES_DB:appserver'

      - 'POSTGRES_USER:appserver'

      - 'POSTGRES_PASSWORD:appserver'

    ports:

      - "5435:5432"

  mosquitto:

    image: iegomez/mosquitto-go-auth:0.5.0

    volumes:

      - ./conf:/etc/mosquitto

    ports:

      - 1883:1883

volumes:

  psql:

And I have change in conf/go-auth.conf also.

auth_opt_log_level debug
auth_opt_backends files
auth_opt_check_prefix false

auth_opt_password_path /etc/mosquitto/auth/passwords
auth_opt_acl_path /etc/mosquitto/auth/acls

auth_opt_pg_host localhost
auth_opt_pg_port 5432
auth_opt_pg_dbname appserver
auth_opt_pg_user appserver
auth_opt_pg_password appserver
auth_opt_pg_userquery select password_hash from "user" where username = $1 and is_active = true limit 1

In password:

test:PBKDF2$sha512$100000$os24lcPr9cJt2QDVWssblQ==$BK1BQ2wbwU1zNxv3Ml3wLuu5//hPop3/LvaPYjjCwdBvnpwusnukJPpcXQzyyjOlZdieXTx6sXAcX4WnZRZZnw==

I have mqtt_publish file which generally do the authentication , connect to the broker and publish on a topic .
In publish.py file:

client.username_pw_set("test","testpw")

But getting error like not able to authenticate .

time="2020-01-27T10:19:25Z" level=debug msg="checking auth cache for test"
time="2020-01-27T10:19:25Z" level=debug msg="checking user test with backend Files"
time="2020-01-27T10:19:25Z" level=warning msg="wrong password for user test\n"

Please help .

2

Hey, I think you may be mixing things up. From your post I take it that you generated the PBKDF2 password using the pw utility and stored it in a password for the files backend, but how does have to do with Postgres? Also, where’s that client.username_pw_set call coming from?

3

Hi iegomez,
Actually i want to set up mqtt broker with authentication layer where each client have to validate with their password and topics . This authentication things want to set up through postgresql DB not file based .
Regarding ‘client.username_pw_set’:
In mqtt client connection script this line will be there to check auth.
example :

client = paho.Client()
client.username_pw_set(“username”, “password”)
client.connect(“broker.mqttdashboard.com”)
4

Ok, but what’s the issue here? How did you generate the password?

5

I have taken user as ‘test’ and password as ‘testpw’ . After the PBKDF2 conversion ‘PBKDF2$sha512$100000$os24lcPr9cJt2QDVWssblQ==$BK1BQ2wbwU1zNxv3Ml3wLuu5//hPop3/LvaPYjjCwdBvnpwusnukJPpcXQzyyjOlZdieXTx6sXAcX4WnZRZZnw==’ . I got this username and password from backends/postgres_test.go. Just dumping manual in postgres db with this details .
I have mentioned below the steps for postgres, what i have done so far:

- create database mqtt_auth;
- create user tc_admin with encrypted password 'tcpassword';
- grant all privileges on database mqtt_auth to tc_admin;

- \c mqtt_auth

- create table test_user(
id bigserial primary key,
username character varying (100) not null,
password_hash character varying (200) not null,
is_admin boolean not null);

- create table test_acl(
id bigserial primary key,
test_user_id bigint not null references test_user on delete cascade,
topic character varying (200) not null,
rw int not null);


 
- insert into test_user values(1,'test','PBKDF2$sha512$100000$os24lcPr9cJt2QDVWssblQ==$BK1BQ2wbwU1zNxv3Ml3wLuu5//hPop3/LvaPYjjCwdBvnpwusnukJPpcXQzyyjOlZdieXTx6sXAcX4WnZRZZnw==',true);
- insert into test_acl values(1,1,'test_user/#',2);

- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tc_admin;
- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO tc_admin;

With these changes i can authenticate username and password but unable to find out how to give access topic basis authentication .
example:
for username : test , can only publish on ‘first_floor/room1’ . Should give auth error when usename:test try to publish on other topics . This kind of authentication i want to configure . Can you help me out ?

6

Is this to use with a ChirpStack deployment? Because in that case you should look at the docs on how to set it up.