Multitenancy support

[mikezaschka]

SAP Cloud Platform CF offers the possibility to develop multitenancy applications and CAP has built in support.

• https://cap.cloud.sap/docs/guides/multitenancy#how-it-works
• https://help.sap.com/viewer/65de2977205c403bbc107264b8eccf4b/Cloud/en-US/5e8a2b74e4f2442b8257c850ed912f48.html
• https://blogs.sap.com/2020/08/20/getting-your-head-into-cloud-application-programming-model-multitenancy/

When running on SAP CF, the PostgreSQL adapter should be able to handle the multitenancy.
I am currently not sure about all the requirements, but one is to change the schema based on the requests tenant. A possible solution could like this:

class PostgresDatabase extends cds.DatabaseService {
...

  async acquire(arg) {

    // fetch the tenant if available
    const tenant = (typeof arg === 'string' ? arg : arg.user.tenant) || 'anonymous'

    // define the used schema, maybe add some more advanced naming logic
    const schema = tenant === 'anonymous' ? 'public' : tenant;

    // change the PostgreSQL schema for the client
    this._pool.on('connect', (client) => {

      // search_path also supports naming multiple schemas so things can be split across schemas (provider, subscribers)
      client.query(`SET search_path TO ${schema};`)
    })

    const dbc = await this._pool.connect()
    return dbc
  }

...
}

For a future implementation it would be necessary to identify all the internal requirements by analyzing the cds core and come up with a concept for PostgreSQL (that maybe works for other DB adapters as well).

[gregorwolf]

Additional information: Multitenancy with Postgres schemas: key concepts explained

[austinkloske22]

@gregorwolf are you able to to implement a user variable to allow for me to switch schemas on event handler? Suggested code change block below

cds-pg/lib/pg/Service.js

Line 131 in 48a3554

// const tenant = (typeof arg === 'string' ? arg : arg.user.tenant) || 'anonymous'

async acquire(arg) {
    // const tenant = (typeof arg === 'string' ? arg : arg.user.tenant) || 'anonymous'
    const schema = (typeof arg === 'string' ? arg : arg.user.schema) || undefined;
    const dbc = await this._pool.connect()
    
    if (this.options.credentials && this.options.credentials.schema && schema) {
      dbc.query(`SET search_path TO '${schema}';`)
    } else if(this.options.credentials && this.options.credentials.schema) {
      dbc.query(`SET search_path TO '${this.options.credentials.schema}';`)
    }
    return dbc
  }

From the web hooks:

module.exports = cds.service.impl(function () {

    this.before('READ', '*', async (req) => {
        req.user.schema = 'local';
});

[vobu]

why don't you put this in a PR?
all contributions are welcome!

[austinkloske22]

why don't you put this in a PR?
all contributions are welcome!

1st open source PR created! #113 - Please let me know if I need to do anything differently next time.

[alperdedeoglu]

Hi @gregorwolf, @mikezaschka, @vobu

As you know, CDS Core uses another library called cds-mtx for multitenancy.
CDS MTX module is responsible of following;

1. Tenant onboarding
2. Tenant offboarding
3. Tenant model upgrade
4. Tenant based extensions (SaaS Extensions)

cds-mtx injects relevant endpoints for mentioned features above under /mtx/*

Tenant Onboarding
When tenant clicked on subscribe at SAP BTP or called the onboarding endpoint,
cds-mtx is first creating the schema for subscribing tenant and deploys the DB artifacts.

Tenant Offboarding
When a tenant clicked on unsubscribe at SAP BTP or called the offboarding endpoint,
schema relevant to that tenant is removed and all the data is lost by default.. ( But user can implement their own logic here .. )

Tenant Model Upgrade
Let's say we have a Mentors table and it has three columns: ID, name, surname.
Let's also assume that we have already created this table and deployed that to our tenants schemas as a
part of our SaaS solution.
When we would like to add a new column to this table, let's say age column, and deploy it, it is not distributed
to the all tenants automatically. We need to call this tenant model upgrade to add the column in the table created
on tenants specific schema. ( There are two endpoints for this, one for sync one for async if I am not wrong ).

Tenant based extensions
Each tenant is able to extend the data model as they wish via using CDS syntax, but I am not really confident about this part
since the project I am working not supporting SaaS extensions.

Of course there is a lot more to tell but this is the basic idea of CAP Multitenancy with schema separation.

My question here is, should multitenancy be supported in this cds-pg module? or Should we create another module
something like cds-pg-mtx? What is the plan?

Best,

Alper

[austinkloske22]

I think that multitenant support will need to be implemented in the cds-dbm module.

I would like to add a multitenant flag and tenant array in the migration options. If the multitenant flag = true, then we also deploy to tenant schemas. Problem is I don't know how to proberly implement & test this feature yet.