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., Postgres). 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.
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
- Higher risk of querying incorrect schema
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
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
- Data is shared across all tenants
- 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.
The privileges of CrateDB users have to be managed using the
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
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;
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.