Multitenancy

Cube supports multitenancy out of the box, both on database and data schema levels. Multiple drivers are also supported, meaning that you can have one customer’s data in MongoDB and others in Postgres with one Cube instance.

There are 6 configuration options you can leverage to make your multitenancy setup. You can use all of them or just a couple, depending on your specific case. The options are:

  • contextToAppId
  • contextToOrchestratorId
  • driverFactory
  • repositoryFactory
  • preAggregationsSchema
  • queryRewrite

All of the above options are functions, which you provide to Cube in the cube.js configuration file. The functions accept one argument - a context object, which has a securityContext property where you can provide all the necessary data to identify a user e.g., organization, app, etc. By default, the securityContext is defined by Cube API Token.

There are several multitenancy setup scenarios that can be achieved by using combinations of these configuration options.

In cases where your Cube schema is spread across multiple different databases you may consider using the dataSource cube property instead of multitenancy. Multitenancy is designed for cases where you need to serve different datasets for multiple users, or tenants which aren't related to each other.

On the other hand, multiple data sources can be used for scenarios where users need to access the same data but from different databases. The multitenancy and multiple data sources features aren't mutually exclusive and can be used together.

A default data source must exist and be configured. It is used to resolve target query data source for now. This behavior will be changed in future releases.

A simple configuration with two data sources might look like:

cube.js:

module.exports = {
  driverFactory: ({ dataSource } = {}) => {
    if (dataSource === 'db1') {
      return {
        type: 'postgres',
        database: process.env.DB1_NAME,
        host: process.env.DB1_HOST,
        user: process.env.DB1_USER,
        password: process.env.DB1_PASS,
        port: process.env.DB1_PORT,
      };
    } else {
      return {
        type: 'postgres',
        database: process.env.DB2_NAME,
        host: process.env.DB2_HOST,
        user: process.env.DB2_USER,
        password: process.env.DB2_PASS,
        port: process.env.DB2_PORT,
      };
    }
  },
};

A more advanced example that uses multiple data sources could look like:

cube.js:

module.exports = {
  driverFactory: ({ dataSource } = {}) => {
    if (dataSource === 'web') {
      return {
        type: 'athena',
        database: dataSource,
        ...,
      };
    } else if (dataSource === 'googleAnalytics') {
      return {
        type: 'bigquery',
        ...,
      };
    } else if (dataSource === 'financials') {
      return {
        type: 'postgres',
        database: 'financials',
        host: 'financials-db.acme.com',
        user: process.env.FINANCIALS_DB_USER,
        password: process.env.FINANCIALS_DB_PASS,
      };
    } else {
      return {
        type: 'postgres',
        ...,
      };
    }
  },
};

As a rule of thumb, the queryRewrite should be used in scenarios when you want to define row-level security within the same database for different users of such database. For example, to separate access of two e-commerce administrators who work on different product categories within the same e-commerce store, you could configure your project as follows:

// cube.js configuration file
module.exports = {
  queryRewrite: (query, { securityContext }) => {
    if (securityContext.categoryId) {
      query.filters.push({
        member: 'Products.categoryId',
        operator: 'equals',
        values: [securityContext.categoryId],
      });
    }
    return query;
  },
};

// Products cube
cube(`Products`, {
  sql: `select * from products`,
});

On the other hand, multi-tenant COMPILE_CONTEXT should be used when users need access to different databases. For example, if you provide SaaS ecommerce hosting and each of your customers have a separate database, then each e-commerce store should be modeled as a separate tenant.

const {
  securityContext: { userId },
} = COMPILE_CONTEXT;

cube(`Products`, {
  sql: `select * from ${userId}.products`,
});

Per tenant row-level security can be achieved by configuring queryRewrite, which adds a tenant identifier filter to the original query. It uses the securityContext to determine which tenant is requesting data. This way, every tenant starts to see their own data. However, resources such as query queue and pre-aggregations are shared between all tenants.

cube.js:

module.exports = {
  queryRewrite: (query, { securityContext }) => {
    const user = securityContext;
    if (user.id) {
      query.filters.push({
        member: 'Users.id',
        operator: 'equals',
        values: [user.id],
      });
    }
    return query;
  },
};

Let's consider an example where we store data for different users in different databases, but on the same Postgres host. The database name format is my_app_<APP_ID>_<USER_ID>, so my_app_1_2 is a valid database name.

To make it work with Cube, first we need to pass the appId and userId as context to every query. We should first ensure our JWTs contain those properties so we can access them through the security context.

const jwt = require('jsonwebtoken');
const CUBE_API_SECRET = 'secret';

const cubejsToken = jwt.sign({ appId: '1', userId: '2' }, CUBE_API_SECRET, {
  expiresIn: '30d',
});

Now, we can access them through the securityContext property inside the context object. Let's use contextToAppId and contextToOrchestratorId to create a dynamic Cube App ID and Orchestrator ID for every combination of appId and userId, as well as defining driverFactory to dynamically select the database, based on the appId and userId:

The App ID (the result of contextToAppId) is used as a caching key for various in-memory structures like schema compilation results, connection pool. The Orchestrator ID (the result of contextToOrchestratorId) is used as a caching key for database connections, execution queues and pre-aggregation table caches. Not declaring these properties will result in unexpected caching issues such as schema or data of one tenant being used for another.

cube.js:

module.exports = {
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}_${securityContext.userId}`,
  contextToOrchestratorId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}_${securityContext.userId}`,
  driverFactory: ({ securityContext }) => ({
    type: 'postgres',
    database: `my_app_${securityContext.appId}_${securityContext.userId}`,
  }),
};

To support per-tenant pre-aggregation of data within the same database instance, you should configure the preAggregationsSchema option in your cube.js configuration file. You should use also securityContext to determine which tenant is requesting data.

cube.js:

module.exports = {
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.userId}`,
  preAggregationsSchema: ({ securityContext }) =>
    `pre_aggregations_${securityContext.userId}`,
};

What if for application with ID 3, the data is stored not in Postgres, but in MongoDB?

We can instruct Cube to connect to MongoDB in that case, instead of Postgres. To do this, we'll use the driverFactory option to dynamically set database type. We will also need to modify our securityContext to determine which tenant is requesting data. Finally, we want to have separate data schemas for every application. We can use the repositoryFactory option to dynamically set a repository with schema files depending on the appId:

cube.js:

module.exports = {
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}_${securityContext.userId}`,
  contextToOrchestratorId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}_${securityContext.userId}`,
  driverFactory: ({ securityContext }) => {
    if (securityContext.appId === 3) {
      return {
        type: 'mongobi',
        database: `my_app_${securityContext.appId}_${securityContext.userId}`,
        port: 3307,
      };
    } else {
      return {
        type: 'postgres',
        database: `my_app_${securityContext.appId}_${securityContext.userId}`,
      };
    }
  },
  repositoryFactory: ({ securityContext }) =>
    new FileRepository(`schema/${securityContext.appId}`),
};

If you are deploying Cube to AWS Lambda with the Serverless template, you need to use AWSHandlers from the @cubejs-backend/serverless-aws package.

Add the following code to your cube.js file for the serverless multitenancy setup.

cube.js:

const AWSHandlers = require('@cubejs-backend/serverless-aws');

module.exports = new AWSHandlers({
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}`,
  driverFactory: ({ securityContext }) => ({
    type: 'postgres',
    database: `my_app_${securityContext.appId}`,
  }),
});

If you need scheduled refreshes for your pre-aggregations in a multi-tenant deployment, ensure you have configured scheduledRefreshContexts correctly. You may also need to configure scheduledRefreshTimeZones.

Leaving scheduledRefreshContexts unconfigured will lead to issues where the security context will be undefined. This is because there is no way for Cube to know how to generate a context without the required input.

When configured for multitenancy, Cube uses a separate connection pool for each configured tenant. This means that the CUBEJS_REDIS_POOL_MIN and CUBEJS_REDIS_POOL_MAX environment variables specify the minimum and maximum number of Redis connections per-tenant.

Did you find this page useful?