Multi-Tenancy Notes

multi_tenancy_notes.md

Multi-Tenancy Notes

SaaS services are a typical example of multi-tenant applications. With multi-tenancy, it's possible to build scalable and cost-effective services by sharing resources accross several customers (tenants). The multi-tenant architecture assumes data isolation and fine-grained security controls to ensure that one tenant's data stays private or only accessible by other tenants under certain conditions.

Let's take an example of a Shopify-like company where merchants can easily launch their own eCommerce website and start selling products/services online.

Postgres and Multi-Tenancy

To build a multi-tenant application, you need to decide about your data isolation and security requirements. Depending on those requirements, many select one of the following option to implement a multi-tenant app on Postgres:

1. Single table, multiple tenants. Tenants data is stored together in the same table with a column such as tenant_id identifying what tenant a particular row belongs to.

   

   • Pros: easy to implement and access data of all the tenants (multi-tenant queries)
   • Cons: poor data isolation, complex security safeguards and permissions, limited scalability (a big tenant can saturate the table with the data impacting the requests for other tenants).

2. Schema per tenant. Each tenant has his own database schema.

   

   • Pros: better data isolation and security, easy to query data across multiple tenats
   • Cons: maintenance and scalability beyond a signle database server.

3. Database per tenant. Each tenant stores data in his own database object.

   

   • Pros: advanced data isolation (one tenant can't query other tenants data directly, 3rd party solutions are required), easy to roll out schema-related changes (one tenant at a time).
   • Cons: scalability beyond a single database server, maintenance of a database server (all tenants are impacted)

4. Database sharding/distribution. To address the limitations of the previous approach, each or some of the tenants can be stored on dedicated Postgres database instances/servers/machines. But that approach comes with its own limitations - vertical scalability only and HA. With database sharding or distributed databases, tenants can be automatically distributed across the cluster and if necessary their data can be stored on dedicated database instances with copies of the data on other nodes.

   

   • Pros: data isolation on the server-level (if needed), scalability, HA. Easy to roll out changes and do upgrades one tenant at a time.
   • Cons: a fleet of database nodes to keep an eye on.

Example, Database Per Tenant

Let's run this example to see how to implement the "database per tenant" approach. All database objects will be associated with a single Postgres instance.

Suppose we have two companies using our Shopify-like SaaS service. The first one is Morning Brew Co. that sells coffee and related goods. Another one is Wild River Brewery that sells beer.

As long as both tenants are independent companies and there is no need to share data between them, we'll use the "database per tenant" approach.

First, create the databases for both companies:

create database morning_brew_co;
create database wild_river_brewery;

The next step is to create database roles for each company so that a company can connect to and work with its own database object.

First, create roles for each company:

CREATE ROLE wild_river_role WITH LOGIN PASSWORD 'password';
CREATE ROLE morning_brew_role WITH LOGIN PASSWORD 'password';

Second, grant ownership and priviliges on the databases:

-- Grant ownership of wild_river_brewery to wild_river_role
ALTER DATABASE wild_river_brewery OWNER TO wild_river_role;

-- Grant ownership of morning_brew_co to morning_brew_role
ALTER DATABASE morning_brew_co OWNER TO morning_brew_role;

-- Revoke public connect privileges from all databases to ensure only the owners can connect
REVOKE CONNECT ON DATABASE wild_river_brewery FROM PUBLIC;
REVOKE CONNECT ON DATABASE morning_brew_co FROM PUBLIC;

-- Grant connect privileges to the specific roles
GRANT CONNECT ON DATABASE wild_river_brewery TO wild_river_role;
GRANT CONNECT ON DATABASE morning_brew_co TO morning_brew_role;

Finally, make sure the folks from Wild Rivew Brewery can't access the database of the coffee company:

\c morning_brew_co wild_river_role

connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied for database "morning_brew_co"
DETAIL:  User does not have CONNECT privilege.

But they connect to their own database, add products and perform other data manipulations:

\c wild_river_brewery wild_river_role

create table product(
    id serial primary key,
    title text,
    description text,
    price numeric(10,2),
    quantity int
);

INSERT INTO product (title, description, price, quantity)
VALUES ('River Pale Ale', 'A smooth and hoppy pale ale with citrus notes, brewed by Wild River Brewery.', 6.50, 200);