Feature #4399
support session level "schema" switching
Status:
New
Priority:
Normal
Assignee:
-
Target version:
-
Start date:
Due date:
% Done:
0%
billable:
No
vendor_id:
GCD
version_reported:
version_resolved:
History
#1 Updated by Greg Shah over 4 years ago
When thinking about a multi-tenant approach, there are 3 possible approaches:
- database instance per tenant
- a separate physical database for each tenant
- this is the current approach for FWD
- no development is needed for this
- extra management of instances
- more flexible for dump/load
- more secure
- one database instance with a different schema for each tenant
- one physical database contains multiple tenants
- each tenant has its own schema
- a schema is a kind of namespace that separates tables from one tenant from all other tenants
- less secure, rights must be carefully managed
- a "search path" can be set as part of a connection which will automatically select the right schema for all other usage of that connection
- may perform better in some use cases
- partitions by tenant ID
- add explicit tenant ID column in every table
- all query processing/access/edits must honor the current tenant ID
- this is a kind of multiplexing of a common set of tables
- least secure option
- for very large tables, this may perform poorly since all indexing and access will have to scale to handle a table based on all customer data
This task is to implement the schema level support in FWD.
One possible approach is to implement the SET-TENANT()
function to select the proper schema/set the search path properly in the connection processing. There is a question of how the connection pooling software handles these search paths.