Missing feature multitenancy

[BastiInnovation]

Migration to postgres

I read the tutorial Multitenant Business Application with PostgreSQL, to get multitenant support in my SaaS-Application. The package used in the tutorial, is the deprecated cds-pg package.

After that I found out that there is no support in cds-js/postgres yet.
Is the support available soon? are there other ways to achieve multitenancy with postgres?

Thanks in advance for your help.

[BobdenOs]

@BastiInnovation I had a quick look over the linked blog post and there seems to have been quite a bit that changed in the area of multitenancy as well as the Postgres support.

Therefor I want to give some more insight into the topic.
Probably the most important change is that the fundamental implementation of multitenancy support in CAP has been put into the @sap/mtxs package (npm). Which currently has native support for SQLite and HANA as documentation here. In principle it should be quite straight forward to add Postgres support as well.

There is a big difference between Postgres and the already supported databases. Where currently a Postgres service instance is provided by the cloud platform directly and relies on the application owners to manage the database fully. Where HANA comes with additional services that provide multitenancy specific functionalities to ensure secure isolation between the tenants.

Here is a simple illustration of how it work in the case of HANA. Where the credentials are handled in a central place in the platform. This broker service comes with a lot of security features. Which either cannot be supported for Postgres or would come with a lot of added complexity for CAP to support.

sequenceDiagram
    CAP->>HANA Broker: create tenant
    HANA Broker-->>CAP: tenant credentials
    CAP->>HANA tenant: connect
    Note right of HANA tenant: credential rotation
    CAP->>HANA tenant: connect fails
    CAP->>HANA Broker: read tenant
    HANA Broker-->>CAP: tenant credentials
    CAP->>HANA tenant: connect

Loading

So how it would look like for Postgres is as follows:

sequenceDiagram
    CAP->>Postgres: connect with credentials
    CAP->>Postgres: create tenant schema
    Postgres-->>CAP: 
    CAP->>Postgres: deploy tenant contents
    Postgres-->>CAP: 
    CAP->>Postgres: switch tenant schema
    Postgres-->>CAP: 
    CAP->>Postgres: switch tenant schema
    Postgres-->>CAP: ...

Loading

So the following security relevant topics should be kept in mind:

1. A single user owns all data
2. All tenant isolation is done on schema level
3. All data is stored in a single physical database instance
4. No tenant level encryption possible

[dev-sap]

Is there any update on the support for multitenancy and extensibility in PostgreSQL? Thanks

[maxi1555]

is there any plan to support this?, thanks.

[maxi1555]

@BastiInnovation I had a quick look over the linked blog post and there seems to have been quite a bit that changed in the area of multitenancy as well as the Postgres support.

Therefor I want to give some more insight into the topic. Probably the most important change is that the fundamental implementation of multitenancy support in CAP has been put into the @sap/mtxs package (npm). Which currently has native support for SQLite and HANA as documentation here. In principle it should be quite straight forward to add Postgres support as well.

There is a big difference between Postgres and the already supported databases. Where currently a Postgres service instance is provided by the cloud platform directly and relies on the application owners to manage the database fully. Where HANA comes with additional services that provide multitenancy specific functionalities to ensure secure isolation between the tenants.

Here is a simple illustration of how it work in the case of HANA. Where the credentials are handled in a central place in the platform. This broker service comes with a lot of security features. Which either cannot be supported for Postgres or would come with a lot of added complexity for CAP to support.

sequenceDiagram
    CAP->>HANA Broker: create tenant
    HANA Broker-->>CAP: tenant credentials
    CAP->>HANA tenant: connect
    Note right of HANA tenant: credential rotation
    CAP->>HANA tenant: connect fails
    CAP->>HANA Broker: read tenant
    HANA Broker-->>CAP: tenant credentials
    CAP->>HANA tenant: connect

Loading

So how it would look like for Postgres is as follows:

sequenceDiagram
    CAP->>Postgres: connect with credentials
    CAP->>Postgres: create tenant schema
    Postgres-->>CAP: 
    CAP->>Postgres: deploy tenant contents
    Postgres-->>CAP: 
    CAP->>Postgres: switch tenant schema
    Postgres-->>CAP: 
    CAP->>Postgres: switch tenant schema
    Postgres-->>CAP: ...

Loading

So the following security relevant topics should be kept in mind:

1. A single user owns all data
2. All tenant isolation is done on schema level
3. All data is stored in a single physical database instance
4. No tenant level encryption possible

Hi all,
Just for curiosity I’ve spent a few hours last night creating the Postgres lib for the mtx package, and it works like a charm ;) ( t0 created, and also new subscriptions ), there are few comments:

• one db is created for mtx
• Every tenant has their own user and schema
• The db encryption is for the entire db
• You can have the multi AZ at db level

The night was not long enough to see how this package behaves with this approach, but I hope to not find any blocker.

+Update: With a few lines of code and "executives" decisions on security aspects on the PostgresService I was able to make it work, the apps can just connect to the corresponding tenants/schemas:

I would like to contribute on this package & the mtx, any idea how can we make this happen?, thanks.