Details
-
Task
-
Status: Open (View Workflow)
-
P3
-
Resolution: Unresolved
-
None
-
-
CP: ready for planning, CP: Roadmap backlog
-
5
-
Core: Platform
Description
When a module is deployed it gets DB connection params (user/pass/db) in environment variables. In practice, this means a given module instance is likely to connect to a single DB instance only. RMB based modules use this database user/role to create a dedicated user/role for each tenant.
The main benefit of this approach is that it allows for a single connection pool (Postgres connections cost ~ 10mb) to be maintained for the module instance, across all tenants. The disadvantage, is that it provides less isolation between tenants. It's not entirely trivial, for a selected tenant to use different connection params and, in effect, connect to a physically separate database instance.
We need to consider if this approach should be used going forward or if, instead, we should switch to another approach, e.g DB connections being provisioned separately for each tenant.
Factors to account for:
- scalability (e.g ability to share connections, ability to shard, ability to load balance with tools like PGBouncer)
- security (tenant isolation)
- deployment flexibility (e.g due to data privacy requirements)
Notes: FOLIO uses schema-level tenant isolation. Postgres allows for multiple schemas per database.
For each combination of tenant and module RMB creates a separate schema and a dedicated user/role for that schema. Currently RMB has a pool for each combination of tenant and module, and a database connection is NOT reused for a different tenant. Changing the user requires re-establishing a new connection.
However, RMB might implement reusing connections across tenants. PostgreSQL schemas can be authorized to specific roles (users) but to avoid resetting connections, the connecting user needs all roles to be granted and needs to escalate/de-escalte its role when executing statements
or
(similar to sudo). This obviously does not protect against malicious modules wanting to perform cross-tenant modifications but is a good protection from unintended DB modifications.
Managing connections in Postgres: https://brandur.org/postgres-connections
Switching roles after the connection has been established: https://stackoverflow.com/questions/2998597/switch-role-after-connecting-to-database
Connection pooling in Postgres: https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/
Tuning max_connections in PostgreSQL: https://www.cybertec-postgresql.com/en/tuning-max_connections-in-postgresql/
TestRail: Results
Attachments
Issue Links
- relates to
-
FOLIO-1935 Service creating ROLE and SCHEMA on tenant initialization
-
- Draft
-
-
RMB-664 PoC: implement one connection pool for all tenants
-
- Closed
-
-
FOLIO-2031 Remove tenant name and mod_ prefix from PostgreSQL schema names, separate tenant data into diffent databases
-
- Open
-
-
RMB-663 patch idle connection release in the upstream vertx-sql-client driver
-
- Closed
-
-
RMB-680 DB connection pool utilization monitoring
-
- Open
-
-
RMB-739 patch idle connection release in the forked vertx-sql-client driver
-
- Closed
-
-
UXPROD-1819 FOLIO database architecture improvements
-
- Draft
-
-
UXPROD-3209 Implement one connection pool per RMB module for all tenants
-
- Open
-