requirements.txt

fake-data-gen.py

#!/usr/bin/env python3
# Implementing this with dummy data: https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

from faker import Faker
from faker.providers import person, company
import random

fake = Faker()
fake.add_provider(person)
fake.add_provider(company)

status = ['active', 'suspended', 'disabled']
tiers  = ['gold', 'bronze', 'silver']

for _ in range(100):
    print(f"INSERT INTO tenant(name, status, tier) VALUES ('{fake.company()}', '{random.choice(status)}', '{random.choice(tiers)}');")

# pick up valid tenants id (ideally via SQL - this is a hack)
# I've instead just SELECT tenant_id from tenant and put it in a text file :)

f = open('valid_tenants.txt')
g = f.readlines()
f.close()

for _ in range(100):
    print(f"INSERT INTO tenant_user(tenant_id, email, given_name, family_name) VALUES ('{random.choice(g).strip()}'::UUID, '{fake.email()}', '{fake.first_name()}', '{fake.last_name()}');")

requirements.txt

Faker==0.7.11
psycopg2==2.8.6
python-dateutil==2.8.1
six==1.15.0

setup.sql

CREATE DATABASE "multi-tenant"
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

COMMENT ON DATABASE "multi-tenant"
    IS 'Contains sample multi-tenant data';


-- For UUIDs
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create a table for our tenants with indexes on the primary key and the tenant’s name
CREATE TABLE tenant (
    tenant_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    status VARCHAR(64) CHECK (status IN ('active', 'suspended', 'disabled')),
    tier VARCHAR(64) CHECK (tier IN ('gold', 'silver', 'bronze'))
);

-- Create a table for users of a tenant
CREATE TABLE tenant_user (
    user_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenant (tenant_id) ON DELETE RESTRICT,
    email VARCHAR(255) NOT NULL UNIQUE,
    given_name VARCHAR(255) NOT NULL CHECK (given_name <> ''),
    family_name VARCHAR(255) NOT NULL CHECK (family_name <> '')
);

-- Turn on RLS
ALTER TABLE tenant ENABLE ROW LEVEL SECURITY;

-- Use application context to determine tenant
CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Note I'm not using the below (which requires separate postgres users for each tenant)

-- -- Restrict read and write actions so tenants can only see their rows
-- -- Cast the UUID value in tenant_id to match the type current_user returns
-- -- This policy implies a WITH CHECK that matches the USING clause
-- CREATE POLICY tenant_isolation_policy ON tenant
-- USING (tenant_id::TEXT = current_user);

-- -- And do the same for the tenant users
-- ALTER TABLE tenant_user ENABLE ROW LEVEL SECURITY;

-- CREATE POLICY tenant_user_isolation_policy ON tenant_user
-- USING (tenant_id::TEXT = current_user);