anthonyolazabal/ese-companion-postgresql

By anthonyolazabal

Updated 10 months ago

ESE Companion for HiveMQ Enterprise Security extension (PostgreSQL version). Provide API and UI.

Image
API Management
Security
Web Servers

89

ESE Companion PostgreSQL version

Introduction

This project was born out of a simple need: to have an API to manipulate the database of the HiveMQ Enterprise brokerEnterprise Security Extension. The UI provided is the chery on the cake.

The project is release has Open Source and is currently still in development. It is fully working but there is still work in progress, see Roadmap for more details.

Repository

All the code and stuff around the application are available on the following repository : ESE Companion

Database compatibility

Thanks to Prisma, we can connect to several types of database engine with the same code. Here is the status of tests and validations carried out on the various engines compatible with the security extension. Full list of supported version

DBTestedValidatedConnection string format
PostgreSQLYESYESpostgresql://username:password@server:port/database_name?schema=public
MariaDBYESYESmysql://USER:PASSWORD@HOST:PORT/DATABASE
MySQLYESYESmysql://USER:PASSWORD@HOST:PORT/DATABASE
SQL ServerNONOsqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;encrypt=true
Azure SQLNONOsqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;encrypt=true
Amazon AuroraNONOmysql://USER:PASSWORD@HOST:PORT/DATABASE

More details on configuring Prisma connection string

Database preparation

The schema of the Enterprise Security Extension remain untouched. The authentication on the API and the UI is done with the accounts that are in the resp_api_users table. To be able to access the user needs to have the eseapi_admin role assigned which has himself the eseapiadmin permission.

For more information on the structure of the database, please refer to the official documentation : Link

To create all the needed entries in the database, just execute the following commands on top of the database (also available in Prisma folder > updateDb.sql). By default the user created is eseapiadmin with the password supersecurepassword.

-- Create special permission in ESE DB for additionnal API
insert into rest_api_permissions (permission_string, description)
values ('HIVEMQ_ESEAPI_ADMIN', 'special rest_api_permission, that allows access to ESE API');

-- Create special role in ESE DB for additionnal API
insert into rest_api_roles (name, description)
values ('eseapi_admin', 'has the HIVEMQ_ESEAPI_ADMIN permission');

-- Map role and permission
insert into rest_api_role_permissions (role, permission)
select rest_api_roles.id, rest_api_permissions.id
from rest_api_roles,
     rest_api_permissions
where rest_api_roles.name = 'eseapi_admin'
  AND rest_api_permissions.permission_string = 'HIVEMQ_ESEAPI_ADMIN';

-- Create the first user with eseapi admin permission
insert into rest_api_users (username, password, password_iterations, password_salt, algorithm)
values ('eseapiadmin', 'nOgr9xVnkt51Lr68KS/rAKm/LqxAt8oEki7vCerRod3qDbyMFfDBGT8obnkw+AGygxCQDWdaA2sQnXXoAbVK6Q==', 100, 'wxw+3diCV4bWXQHb6LLniA==', 'SHA512');

-- Map user to eseapi_admin role
INSERT INTO rest_api_user_roles (user_id, role_id)
select rest_api_users.id, rest_api_roles.id
from rest_api_users,
     rest_api_roles
where rest_api_users.username = 'eseapiadmin'
  AND rest_api_roles.name = 'eseapi_admin'
;

Run Docker image

docker run --env=TOKEN_KEY=@JwTT0k3nK3y!!!@JwTT0k3nK3y!!! --env=DATABASE_URL=postgresql://username:password@server:port/database_name?schema=public -p 3001:3001 -d ese-companion-postgresql:latest

Two important environment variables are needed :

  1. DATABASE_URL which give the connection string to the database (Using Prisma format)
DATABASE_URL="postgresql://username:password@server:port/database_name?schema=public"
  1. TOKEN_KEY any token with multiple characters that will we used by the API to create JWT tokens
TOKEN_KEY="@lsirfgjoirnkk!!"

Open a bug

To declare a bug, use the GitHub Issue tracking and fill the template with all the informations needed to reproduce it. Here is the link

Open a feature request

To ask for a new feature, use the GitHub Issue tracking and fill the template for the feature request with all the informations needed. Here is the link

Docker Pull Command

docker pull anthonyolazabal/ese-companion-postgresql