SPIKE: db connection provision and pooling

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

• SET ROLE

or

• SET SESSION AUTHORIZATION

(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/