Multi-tenancy with CrateDB

Tutorials
1

The multi-tenancy is an architecture in which different tenants share a single software instance. CrateDB does not support the creation of multiple databases and catalogs as some other solutions (e.g., PostgreSQL). However, there are several ways to implement multi-tenancy in CrateDB, and, as is often the case, which one works the best depends on a variety of options and trade-offs. In this article, we will illustrate two methods for sharing a single CrateDB instance between multiple tenants.

Schema-based multi-tenancy

In schema-based multi-tenancy, every tenant has its own database schema. CrateDB supports the creation of tables in different schemas (Schemas - CrateDB Reference). The following statements illustrate the creation of two tables with different schemas:

CREATE TABLE "tenantA"."table1" (
    id int,
    name text,
);
CREATE TABLE "tenantB"."table2" (
    id int,
    address text,
);

In this example, we created the first table inside schema tenantA and the second table inside schema tenantB. Furthermore, access privileges can be administrated on the SCHEMA level to restrict access for tenant users only on their schema.

The schema-based multi-tenancy has a couple of benefits:

  • Schema changes are independent of other tenants inside CrateDB
  • Less risk of data leakage due to data isolation
  • Application code does not have to be tenant-aware

However, there are some drawbacks:

  • More complexity, as this approach requires the creation of different schemas for different tenants
  • Performance considerations, such as sharding and partitioning, need to be done for every tenant individually (depending on the expected data volume)
  • Higher risk of querying incorrect schema
  • Risk of getting close to the maximum number of shards (cluster.max_shards_per_node) if there is a significant number of tenants

Table-based multi-tenancy

In table-based multi-tenancy, all data resides in the same table, but it’s separated by a discriminator column. In this case, each query needs a WHERE statement to select data based on the tenant context. The following example illustrates table creation with a separate tenant column.

CREATE TABLE "doc"."name" (
    id int,
    name text,
    price int,
    tenant name
);

Record-based access control is not possible in this scenario. However, you can create a VIEW that is restricted to a single tenant. Without the usage of views, data isolation must be guaranteed on the application level.

Table-based multi-tenancy has some benefits:

  • The application doesn’t need to worry about which schema it is connecting to
  • There is only one schema to maintain
  • Performance considerations are easier to make, as you don’t need to differentiate between tenants with high and low data volume in your sharding and partitioning strategy
  • Data is shared across all tenants

Drawbacks are:

  • Application code needs to be tenant-aware
  • Schema changes affect all tenants
  • Possible data leaks as record-based access control are not possible

Finally, if you need full data isolation between different tenants, then you must run a separate CrateDB cluster for each tenant.

Configuring access privileges with CrateDB

The privileges of CrateDB users have to be managed using the GRANT, DENY or REVOKE statements (Privileges). CrateDB supports four different privilege types:

  • Data Query Language (DQL)
  • Data Manipulation Language (DML)
  • Data Definition Language (DDL)
  • Administration Language (AL)

These privileges can be granted on CLUSTER, SCHEMA, TABLE, and VIEW levels. In schema-based multi-tenancy, you can grant a user full privileges on schema tenantA using the following statement:

GRANT ALL PRIVILEGES ON SCHEMA tenantA TO tenantA_user1;

Similarly, in table-based multi-tenancy you can grant DQL privilege for a specific tenant view:

GRANT DQL ON VIEW tenantA_view TO tenantA_user1;

Summary

This short article covers the main approaches to multi-tenancy with CrateDB: schema-based and table-based multi-tenancy. We also outlined the benefits and drawbacks of each approach, and which one works best for you depends on your use case and goals. If you find this article interesting and want to learn more about CrateDB, visit our official documentation and check our tutorials on CrateDB Community.

5 Likes