DEV Community

abbazs
abbazs

Posted on • Edited on

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
Enter fullscreen mode Exit fullscreen mode
  1. Install Required Dependencies
   sudo apt install -y apt-transport-https ca-certificates curl software-properties-common
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode
  1. Install Docker Engine
   sudo apt update
   sudo apt install -y docker-ce docker-ce-cli containerd.io
Enter fullscreen mode Exit fullscreen mode
  1. Verify Docker Installation
   docker --version
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode
  1. Verify Docker-Compose Installation
   docker-compose --version
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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"]
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode

4. Start the Services

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

docker-compose up --build
Enter fullscreen mode Exit fullscreen mode

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.

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay