DEV Community

abbazs
abbazs

Posted on • Edited on

Managing MQTT User Information with PostgreSQL in a Separate Container

#docker #mqtt #postgres #mosquitto

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.

Top comments (0)