postgres

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

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)