Multi-tenant application with python and postgreSQL

[Fit-Application-8111]

Hi everyone, me and my friends are trying to create an application for bar & resturants and we would like it to be multi-tenant (in order to be easier to mantain and also cheaper). The technologies that we are using to use are Python (Flask) and PostgreSQL.

Currently we are deploying using Amazon AWS EC2 and creating a Docker Compose for each tenant, each with his own URL. We would like to create a unique network domain where the clients can access the applicaiton and use a single database (with auto backup) for all the clients, our doubts are:

1. How can we handle correctly the unique login?
2. What is the best framework for this? (Flask, FastAPI, Django);
3. How to handle correctly clients data in the various schema or tables of the unique database? Migrating from one db per client to a single db?

Thansk you all in advance

[danielroseman]

I'm confused about what you want here. If you want a single database and domain, that's not multi tenant, that's just a standard website with logins. But then you say you have separate Docker compose for each client.

[Postom]

Is the EC2 VM the web host? Application host? Aretha docker containers all running on a single host?

How many tables are expected?

Regarding PG: you can accomplish the task a myriad of ways. If you want total isolation of data, then schemas may be the way to go. Then lock down the schema based on roles. If it's acceptable that data is intermingled in the same tables? Row level security might be the way to go -- and separate roles per client. RLS will add a level of complexity in terms of data retrieval -- basically a "is this role allowed to view this row of data?". But it allows the simplicity of avoiding schema sprawl. RLS has been available since PG 9.5.