postgres

README

Postgres backend example

A comqtt broker with comqttauth wired in coexist mode against a Postgres database. Connection auth runs through comqtt's upstream plugin/auth/postgresql hook (bcrypt). Regex ACL runs through comqttauth.Hook.

The example creates the required tables on startup (CREATE TABLE IF NOT EXISTS), so you only need a reachable Postgres with an empty database.

Prereqs

docker run --rm -d --name comqttauth-example-pg -p 5432:5432 \
    -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_DB=comqtt postgres:16

Run

go run ./examples/postgres

Environment

Variable	Default	Notes
COMQTTAUTH_POSTGRES_HOST	127.0.0.1
COMQTTAUTH_POSTGRES_PORT	5432
COMQTTAUTH_POSTGRES_USER	postgres
COMQTTAUTH_POSTGRES_PASSWORD	postgres
COMQTTAUTH_POSTGRES_DB	comqtt
COMQTTAUTH_POSTGRES_SSLMODE	disable	disable/require/verify-ca/verify-full
COMQTTAUTH_LISTEN	:1883

Seeded credentials and rules

• alice / wonderland — allowed to publish on telemetry/${username}/#.
• bob / builder — denied publish on forbidden/#.

Exercise

# allowed
mosquitto_pub -h 127.0.0.1 -p 1883 -u alice -P wonderland -t telemetry/alice/temp -m hi

# blocked by deny rule
mosquitto_pub -h 127.0.0.1 -p 1883 -u bob -P builder -t forbidden/secret -m boom

# connection rejected
mosquitto_pub -h 127.0.0.1 -p 1883 -u eve -P bad -t any/topic -m nope

Schema

The same schema upstream comqtt ships in plugin/auth/postgresql/testdata/init/init.sql, plus two tables comqttauth adds for regex rules and seed-tracking:

auth(id, username, password, allow, created, updated)
acl(id, username, topic, access, created, updated)
acl_regex(id, rule_order, permission, subject_kind, subject_pattern, action, topic_patterns, created, updated)
comqttauth_meta(meta_key, meta_value)

Documentation

Overview

Postgres backend example: comqtt broker + comqttauth, both reading the same auth/acl tables. Connection auth runs through comqtt's upstream plugin/auth/postgresql hook; regex ACL runs through comqttauth.Hook. Passwords are bcrypt-hashed end-to-end.

Prereqs: a running Postgres with an empty database. The example creates the required tables (CREATE TABLE IF NOT EXISTS) on startup.

Run:

docker run --rm -d --name comqttauth-example-pg -p 5432:5432 \
    -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_DB=comqtt postgres:16
go run ./examples/postgres