Uploaded image for project: 'FOLIO'
  1. FOLIO
  2. FOLIO-1794

SPIKE: db connection provision and pooling

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      CP: ready for planning, CP: Roadmap backlog
    • Story Points:
      5
    • Development Team:
      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

              Activity

                People

                Assignee:
                Unassigned Unassigned
                Reporter:
                jakub Jakub Skoczen
                Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                  Dates

                  Created:
                  Updated:

                    TestRail: Runs

                      TestRail: Cases