Multitenancy

Cube.js 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.js instance.

There are 7 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
  • dbType
  • externalDbType
  • driverFactory
  • repositoryFactory
  • preAggregationsSchema
  • queryRewrite

All of the above options are functions, which you provide to Cube.js 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.js API Token.

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

In cases where your Cube.js 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.

Note

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 new PostgresDriver({
        database: DB1_NAME,
        host: DB1_HOST,
        user: DB1_USER,
        password: DB1_PASS,
        port: DB1_PORT,
      });
    } else {
      return new PostgresDriver({
        database: DB2_NAME,
        host: DB2_HOST,
        user: DB2_USER,
        password: DB2_PASS,
        port: DB2_PORT,
      });
    }
  },
};

A more advanced example with additional configuration of dbType could look like:

cube.js:

const PostgresDriver = require('@cubejs-backend/postgres-driver');
const AthenaDriver = require('@cubejs-backend/athena-driver');
const BigQueryDriver = require('@cubejs-backend/bigquery-driver');

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

As a rule of thumb, SECURITY_CONTEXT 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.

cube(`Products`, {
  sql: `select * from products where ${SECURITY_CONTEXT.categoryId.filter(
    'categoryId'
  )}`,
});

On the other hand, Multitenant COMPILE_CONTEXT should be used when users in fact access different databases. For example, if you provide SaaS ecommerce hosting and each of your customers have a separate database, then each ecommerce store should be modelled as a separate tenant.

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

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

SECURITY_CONTEXT is great for use cases where you want to get explicit control over filtering of underlying data seen by users. However for use cases where you want to reuse pre-aggregation tables for different users or even tenants, using queryRewrite is a much better choice. queryRewrite is also very convenient way of enforcing row level security by means of join logic defined in your cubes instead of embedding SECURITY_CONTEXT filtering boilerplate into each cube. Together with contextToOrchestratorId, this allows defining both row-level security filtering as well as reusing the same pre-aggregation set for each tenant.

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 the following example:

We store data for different users in different databases, but on the same Postgres host. The database name is my_app_1_2, where 1 is Application ID and 2 is User ID.

To make it work with Cube.js, first we need to pass the appId and userId as context to every query. We should include that into our token generation code.

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

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

Now, we can access them as securityContext object inside the context object. Let's first use contextToAppId to create a dynamic Cube.js App ID for every combination of appId and userId.

Note

Cube.js App ID (the result of contextToAppId) is used as a caching key for various in-memory structures like schema compilation results, connection pool, etc. A missing contextToAppId definition will result in unexpected caching issues such as schema of one tenant is used for another one.

cube.js:

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

Next, we can use driverFactory to dynamically select database, based on appId and userId.

cube.js:

const PostgresDriver = require('@cubejs-backend/postgres-driver');

module.exports = {
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}_${securityContext.userId}`,
  driverFactory: ({ securityContext }) =>
    new PostgresDriver({
      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.js to connect to MongoDB in that case, instead of Postgres. For that purpose we'll use dbType option to dynamically set database type. We also need to modify our driverFactory option. You should also use securityContext to determine which tenant is requesting data.

cube.js:

const PostgresDriver = require('@cubejs-backend/postgres-driver');
const MongoBIDriver = require('@cubejs-backend/mongobi-driver');

module.exports = {
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}_${securityContext.userId}`,
  dbType: ({ securityContext }) => {
    if (securityContext.appId === 3) {
      return 'mongobi';
    } else {
      return 'postgres';
    }
  },
  driverFactory: ({ securityContext }) => {
    if (securityContext.appId === 3) {
      return new MongoBIDriver({
        database: `my_app_${securityContext.appId}_${securityContext.userId}`,
        port: 3307,
      });
    } else {
      return new PostgresDriver({
        database: `my_app_${securityContext.appId}_${securityContext.userId}`,
      });
    }
  },
};

Lastly, we want to have separate data schemas for every application. In this case we can use the repositoryFactory option to dynamically set a repository with schema files depending on the appId.

Below you can find the final setup with repositoryFactory:

cube.js:

const PostgresDriver = require('@cubejs-backend/postgres-driver');
const MongoBIDriver = require('@cubejs-backend/mongobi-driver');
const FileRepository = require('@cubejs-backend/server-core/core/FileRepository');

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

If you are deploying Cube.js 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');
const PostgresDriver = require('@cubejs-backend/postgres-driver');

module.exports = new AWSHandlers({
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.appId}`,
  driverFactory: ({ securityContext }) =>
    new PostgresDriver({
      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.js to know how to generate a context without the required input.

When configured for multitenancy, Cube.js 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.