Managing MQTT User Information with PostgreSQL in a Separate Container

This guide will walk you through setting up an MQTT broker with PostgreSQL for managing users. By using Docker, you'll create a modular and scalable environment that integrates well with other systems.

Prerequisites

Installing Docker and Docker-Compose on Ubuntu

To install Docker and Docker-Compose from their official Git repositories on Ubuntu, follow these steps:

1. Update the System Packages

   sudo apt update
   sudo apt upgrade -y

1. Install Required Dependencies

   sudo apt install -y apt-transport-https ca-certificates curl software-properties-common

1. Add Docker's Official GPG Key

   curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg

1. Add Docker's Stable Repository

   echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

1. Install Docker Engine

   sudo apt update
   sudo apt install -y docker-ce docker-ce-cli containerd.io

1. Verify Docker Installation

   docker --version

1. Install Docker-Compose

   sudo curl -L "https://github.com/docker/compose/releases/latest/download/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
   sudo chmod +x /usr/local/bin/docker-compose

1. Verify Docker-Compose Installation

   docker-compose --version

You are now ready to proceed with the setup.

Before starting, ensure you have the following installed:

• Docker
• docker-compose

If you're new to Docker, consider reading Docker's official documentation to familiarize yourself with its basics.

Step-by-Step Guide

1. Create a docker-compose.yml File

Begin by creating a docker-compose.yml file in a new project directory. This file will define the services for the MQTT broker, PostgreSQL, and a FastAPI application to manage users.

Here’s the content of the docker-compose.yml file:

version: '3.8'
networks:
  mqtt-net:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 172.100.10.0/24
services:
  mqtt-broker:
    image: eclipse-mosquitto:latest
    user: mosquitto
    volumes:
      - type: bind
        source: ./config/
        target: /mosquitto/config/
        read_only: false
      - type: bind
        source: ./log/
        target: /mosquitto/log/
        read_only: false
      - type: volume
        source: data
        target: /mosquitto/data/
    ports:
      - target: 1883
        published: 1883
        protocol: tcp
        mode: host
      - target: 9001
        published: 9001
        protocol: tcp
        mode: host
    networks:
      mqtt-net:
        ipv4_address: 172.100.10.10
    environment:
      - MOSQUITTO_AUTH_PLUGIN=auth_plugin_pgsql.so
      - PGSQL_HOST=pgsql
      - PGSQL_USER=mqtt_user
      - PGSQL_PASSWORD=mqtt_password
      - PGSQL_DATABASE=mqtt_users

  pgsql:
    image: postgres:latest
    container_name: postgres-container
    environment:
      POSTGRES_USER: mqtt_user
      POSTGRES_PASSWORD: mqtt_password
      POSTGRES_DB: mqtt_users
    volumes:
      - pg_data:/var/lib/postgresql/data
    networks:
      mqtt-net:
        ipv4_address: 172.100.10.20

  fastapi-service:
    build:
      context: ./fastapi
    container_name: fastapi-container
    ports:
      - "8000:8000"
    networks:
      mqtt-net:
        ipv4_address: 172.100.10.30
    environment:
      - DATABASE_URL=postgresql+psycopg2://mqtt_user:mqtt_password@pgsql:5432/mqtt_users
volumes:
  data:
    name: "mqtt-broker-data"
  pg_data:
    name: "pg-data"

2. Create a FastAPI Service

a. Create a Directory for FastAPI

In your project directory, create a folder named fastapi. Inside this folder, you'll define the FastAPI application and its Docker configuration.

b. Create a Dockerfile for FastAPI

In the fastapi directory, create a Dockerfile with the following content:

FROM python:3.10-slim

# Set the working directory
WORKDIR /app

# Copy FastAPI app files
COPY . /app

# Install dependencies
RUN pip install --no-cache-dir fastapi uvicorn sqlalchemy psycopg2-binary pydantic

# Expose FastAPI default port
EXPOSE 8000

# Command to run FastAPI
CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]

3. Write the FastAPI Application

Create a file named main.py in the fastapi directory. This file will define the FastAPI application. Here’s an example:

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import create_engine, Column, String, Table, MetaData
from sqlalchemy.orm import sessionmaker

app = FastAPI()

# Database configuration
DATABASE_URL = "postgresql+psycopg2://mqtt_user:mqtt_password@pgsql:5432/mqtt_users"
engine = create_engine(DATABASE_URL)
metadata = MetaData()

mqtt_users = Table(
    'mqtt_users', metadata,
    Column('username', String, primary_key=True),
    Column('password', String, nullable=False)
)

metadata.create_all(engine)
Session = sessionmaker(bind=engine)

# Pydantic model for user input
class User(BaseModel):
    username: str
    password: str

@app.get("/users")
def list_users():
    with Session() as session:
        result = session.query(mqtt_users).all()
        users = [{"username": row.username} for row in result]
        return {"users": users}

@app.post("/users")
def add_user(user: User):
    with Session() as session:
        existing_user = session.query(mqtt_users).filter_by(username=user.username).first()
        if existing_user:
            raise HTTPException(status_code=400, detail="User already exists")
        session.execute(mqtt_users.insert().values(username=user.username, password=user.password))
        session.commit()
        return {"message": "User added successfully"}

@app.delete("/users/{username}")
def remove_user(username: str):
    with Session() as session:
        result = session.query(mqtt_users).filter_by(username=username).delete()
        session.commit()
        if not result:
            raise HTTPException(status_code=404, detail="User not found")
        return {"message": "User removed successfully"}

@app.put("/users/{username}/reset-password")
def reset_password(username: str, new_password: str):
    with Session() as session:
        result = session.query(mqtt_users).filter_by(username=username).update({"password": new_password})
        session.commit()
        if not result:
            raise HTTPException(status_code=404, detail="User not found")
        return {"message": "Password reset successfully"}

4. Start the Services

Run the following command in your project directory to build and start the containers:

docker-compose up --build

5. Test the FastAPI Service

Open your browser or a tool like Postman and navigate to http://localhost:8000. Use the provided endpoints to manage users:

• GET /users: List all users.
• POST /users: Add a new user.
• DELETE /users/{username}: Remove a user.
• PUT /users/{username}/reset-password: Reset a user’s password.