How to Implement Schema Separation for Multitenancy w...

[#1]

Hi everyone,

I am currently following the SAP Discovery Center mission "Develop a multitenant SaaS application on SAP BTP using CAP", and I have a question regarding the use of PostgreSQL on SAP BTP (hyperscaler option) as a shared database in a SaaS environment.

The architecture guidelines for data separation in multitenant SaaS applications using CAP recommend the schema separation approach because of its flexibility and cost-effectiveness.

With SAP HANA Cloud, this can be achieved using HDI containers. What technique or recommended approach exists in this case to implement tenant-based schema separation with PostgreSQL on SAP BTP? 

Thanks in advance

[patricebender · Accepted solution]

CAP does not provide ootb support for multi tenancy for apps running with postgres. It is also not planned on our side to support this. You can read through this to get some insights → The community is always welcome to provide there own plugins.

[valentincadart · Accepted solution]

Hi @patricebender,

Thanks for the clarification. Given that CAP does not provide schema-based multitenancy support for PostgreSQL, would you recommend trying to implement tenant separation manually or using a simpler approach such as a column discriminator in shared tables?

My main reason for choosing PostgreSQL is that our SaaS applications have relatively low storage requirements, so using HANA would be disproportionate.

Best regards

[patricebender · Accepted solution]

@valentincadart I think the discussion in the github issue(https://github.com/cap-js/cds-dbs/issues/400#issuecomment-1889333651) explains in great detail how it could be done. Basically yes, there is no plattform support for a container based separation.