What is Multitenancy

Even in the microservice world there's a common requirement to host data for many tenants. But first let's discuss what exactly is a tenant. When discussing multitenancy, various websites and some books give examples like this: a company that resells database access and needs to store data from multiple clients in one database. This is an extremely naïve example and it doesn't really reflect the actual reality.

For the purpose of this document, a tenant is a group of users that work within the same organization (or are somehow associated). For example, if we're making an enterprise chat application (a Slack clone, why not?) then a tenant would be an organization that subscribes for it. And the main goal would be to make sure that one tenant can't access the data from other tenants.

We likely still need to add some kind of access control within the tenant, but we absolutely need to make sure that data doesn't leak across the tenant boundary. Moreover, we should make it a goal to ensure that any bug in our code does not result in leaking data outside of the tenant. Basically, imagine how you can deal with the worst possible scenarios: arbitrary unlimited SQL injection, or a fully exploitable buffer overflow in server code.

PostgreSQL Row-Level Security

So with this in mind let's start designing the data access code. We're using PostgreSQL as our main data storage, so we'll be looking at securing it. We can't do database-per-tenant or schema-per-tenant partitioning as this will blow up the complexity of all the routine operations like database upgrade. Instead we'll be looking at row-level security.

Let's start with the schema and some sample data. I purposefully use human-readable names for tenants, in actual production code it's better to use something like uuid default uuid_generate_v4() instead (and probably for the orders table as well).

-- The application role
create role slack_app nosuperuser nocreatedb nocreaterole login password '123';

-- The tenants table
create table tenant (tenant_id varchar primary key, name varchar);
grant select on tenant to slack_app;

-- And a simple data table
create table orders (order_id int8 primary key, order_text varchar, 
    tenant_id varchar not null references tenant(tenant_id) on delete restrict);
grant select, insert, update, delete on orders to slack_app;

insert into tenant(tenant_id, name) values('HHL','Horns&Hooves Ltd.');
insert into tenant(tenant_id, name) values('CIA', 'Scary Government Agency');
insert into orders(order_id, order_text, tenant_id) values (1, 'Chairs', 'HHL');
insert into orders(order_id, order_text, tenant_id) values (2, 'Diamonds', 'HHL');
insert into orders(order_id, order_text, tenant_id) values (3, 'Killer Drones', 'CIA');

alter table tenant enable row level security;
alter table orders enable row level security;

So far so good. We can log into the database as slack_app and do the CRUD operations on tenants and orders.

Now we need to add some kind of security. This how-to guide has a nice tutorial, so we'll follow it.

create policy tenant_isolation_policy on tenant using (tenant_id = current_setting('app.current_tenant'));
create policy tenant_isolation_policy on orders using (tenant_id = current_setting('app.current_tenant'));

Now let's test it by logging in as slack_app and trying to do something:

cyberax@CybMac:/tmp$ psql --user slack_app slackapp
slackapp=> select * from orders;
ERROR:  unrecognized configuration parameter "app.current_tenant"

Good, we can't see all the data. Now let's try to change the tenant:

slackapp=> set app.current_tenant = 'HHL';
SET
slackapp=> select * from orders;
 order_id | order_text | tenant_id
----------+------------+-----------
        1 | Chairs     | HHL
        2 | Diamonds   | HHL
(2 rows)

Great! We can only see our own data. But there's one small problem, as nothing whatsoever stops an attacker that gained ability to do arbitrary SQL injection from doing this:

slackapp=> set app.current_tenant = 'CIA';
SET
slackapp=> select * from orders;
 order_id |  order_text   | tenant_id
----------+---------------+-----------
        3 | Killer Drones | CIA
(1 row)

There is no way to limit the SET operations in PostgreSQL to be one-time only. Also there are no ways to prohibit running SET commands altogether, or at least limit them to a set of whitelisted options.

Tokenizing Everything

One possible solution is to use unguessable tenant names (e.g. UUIDs), so this way the attacker likely won't know the other tenants' IDs right off the bat. But this is not a good solution, any tenant ID leak would give an attacker access to all the tenant's documents.

But this approach seems to be on the right track. What if instead of tenants we use one-time tokens? These tokens can be populated by a small highly-secure service and passed to the main application.

Let's try it! We need to create a table for the tokens and a stored procedure to check them:

create table token(token varchar primary key, tenant_id varchar not null references tenant(tenant_id) on delete restrict, valid_until timestamp);

create or replace function get_tenant()
returns varchar language plpgsql security definer
as $$ declare 
  tenant_res varchar;
begin
select tenant_id into tenant_res from token where token = current_setting('app.token', true) and now() < valid_until;
return tenant_res;
end $$;

Some explanations: security definer modifier means that the function is always invoked with the permissions of the user that defined it (the superuser in this case). This is necessary because we absolutely DO NOT want to give the slack_app user permissions to do select on our tokens table.

The rest is straightforward, we need to modify the row-level security policy on the tables and insert some test tokens.

drop policy tenant_isolation_policy on tenant;
drop policy tenant_isolation_policy on orders;

create policy tenant_isolation_policy on tenant using (tenant_id = get_tenant());
create policy tenant_isolation_policy on orders using (tenant_id = get_tenant());

-- Insert some test tokens (they MUST be unguessable cryptographically random strings in a real application)
insert into token (tenant_id, token, valid_until) values ('CIA', 'token-high', now() + interval '2 hours');
insert into token (tenant_id, token, valid_until) values ('HHL', 'token-low', now() + interval '2 hours');

And now let's test it!

cyberax@CybMac:/tmp$ psql --user slack_app slackapp
psql (13.3)
Type "help" for help.

slackapp=> select * from orders ;
 order_id | order_text | tenant_id
----------+------------+-----------
(0 rows)

slackapp=> set app.token = 'token-high';
slackapp=> select * from orders;
 order_id |  order_text   | tenant_id
----------+---------------+-----------
        3 | Killer Drones | CIA
(1 row)

slackapp=> set app.token = 'token-low';
slackapp=> select * from orders;
 order_id | order_text | tenant_id
----------+------------+-----------
        1 | Chairs     | HHL
        2 | Diamonds   | HHL
(2 rows)

And this is exactly what we want! The limited time tokens provide authorization to access the data for individual tenants. The tokens can be generated by a small service and communicated to the app over a secure channel. And there's nothing an attacker can do without knowing a token.

Performance

Row-level security is implemented as a hidden where clause, that is visible in the explain statement:

slackapp=> explain select * from orders;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on orders  (cost=0.00..222.62 rows=4 width=72)
   Filter: ((tenant_id)::text = (get_tenant())::text)
(2 rows)

This hidden clause needs to be taken into account when designing queries and indexes.

Discuss...