CS Fundamentals Trainer · Guias

Projetando um sistema multi-tenant

Tenancy silo, bridge e pool comparados com medições no PostgreSQL: Row-Level Security, chaves estrangeiras compostas, um schema e um role por tenant, o vazamento em conexões de pool, timeouts por tenant, migrações e dumps com 1.000 tenants, resolução de tenant e vizinhos barulhentos.

39 min de leitura

Um sistema multi-tenant é uma aplicação em execução que atende muitos clientes separados (os tenants) mantendo à parte os dados, as configurações e a experiência de cada um. Quase todo produto de software como serviço é construído assim, de um gerenciador de projetos a um sistema de gestão de conteúdo que hospeda cem sites de universidades. A ideia é simples de enunciar e fácil de errar de forma sutil: compartilhar o código e as máquinas é o que torna tudo barato, e separar os dados é o que torna tudo seguro, e cada escolha de projeto é uma posição entre esses dois extremos. Este guia cobre as três formas padrão de separar tenants, como impor a separação para que um WHERE esquecido não vaze dados, como descobrir a que tenant uma requisição pertence, como impedir que um tenant ocupado deixe os outros lentos e como operar centenas de tenants sem perder o fim de semana. Executei os experimentos de banco de dados no PostgreSQL 16 e o código está no apêndice.

1. O que estamos construindo

Um tenant é uma organização cliente (uma empresa, uma escola, um departamento). Não é a mesma coisa que um usuário: um tenant tem muitos usuários, e um usuário pode pertencer a vários tenants (um consultor que trabalha para três clientes). Manter essas duas ideias separadas evita toda uma família de erros de projeto.

Requisitos funcionais

Requisitos não funcionais

Quantos tenants?

O número de tenants muda a resposta, então anote o que você espera:

EscalaForma típicaO que sugere
10 a 100 tenants, cada um grande ou exigenteclientes corporativos, contratos, questões de residência de dadosbancos de dados ou implantações separados são acessíveis e fáceis de explicar
100 a 10.000 tenants, tamanhos variadosa maioria dos produtos SaaSinfraestrutura compartilhada com um mecanismo de isolamento forte
Mais de 100.000 tenants minúsculosprodutos de estilo consumidor, planos gratuitosum schema compartilhado com uma coluna de tenant; objetos por tenant seriam demais

A maioria dos produtos começa na linha do meio e tem alguns tenants que a superam, e é por isso que o projeto deve permitir mover um tenant para recursos próprios mais tarde (seção 11).

2. Os três modelos

Diagram: Three ways to separate tenants in a database

Silo (banco de dados por tenant)Bridge (schema por tenant)Pool (tabelas compartilhadas, tenant_id)
Isolamentoo mais forte: credenciais e armazenamento separadosforte, com roles e grantstão forte quanto os seus filtros de linha
Custo por tenanto mais altobaixoo mais baixo
Vizinho barulhentoo melhor (recursos próprios)servidor compartilhadoservidor e tabelas compartilhados
Backup e restauração por tenanttrivialfácil (dump de um schema)difícil (extrair linhas)
Mudança de schema por tenantpossívelpossívelimpossível: um schema para todos
Migrar todos os tenantsN operaçõesN operações em um banco de dadosuma operação
Número prático de tenantsdezenas a centenascentenas a alguns milharesmilhões
Relatórios entre tenantsdifícilpossívelfácil

Os nomes (silo, bridge, pool) são os comumente usados na orientação de fornecedores de nuvem sobre tenancy em SaaS [1]. Projetos mistos são normais: as tabelas da própria plataforma (tenants, usuários, memberships, cobrança) são compartilhadas, e o conteúdo de cada tenant é separado.

3. O modelo pool: tabelas compartilhadas e Row-Level Security

No modelo pool, toda tabela que pertence a um tenant tem um tenant_id, e toda consulta deve filtrar por ele. O perigo é a consulta que esquece. Se você depender de disciplina, mais cedo ou mais tarde um relatório, um job em segundo plano ou o primeiro pull request de um desenvolvedor novo vaza dados. A Row-Level Security (RLS) do PostgreSQL move o filtro para dentro do banco de dados, onde a aplicação não pode esquecê-lo [2]:

ALTER TABLE docs ENABLE ROW LEVEL SECURITY;
CREATE POLICY by_tenant ON docs
  USING      (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

-- per request, inside the transaction:
SELECT set_config('app.tenant_id', '7', true);   -- true = local to this transaction

Construí uma tabela de 500.000 linhas em 100 tenants, conectei como um role comum e testei o que a RLS faz e o que não faz:

TesteResultado
Tenant 7, SELECT count(*) FROM docs sem WHERE5.000 linhas, todas do tenant 7 (de 500.000)
Nenhum tenant definidoum erro, não "todas as linhas" (a consulta falha de forma fechada)
Inserir uma linha do tenant 8 agindo como tenant 7recusado por WITH CHECK
O dono da tabela, sem FORCE ROW LEVEL SECURITYvê todas as 500.000 linhas (a RLS é ignorada)
O dono com FORCE e nenhum tenant definidoum erro
Um superusuáriovê todas as 500.000 linhas, sempre
Tempo de uma página de 20 linhas de um tenant, medido do cliente (mediana de 200 execuções, inclui o round trip)0,096 ms só com RLS, 0,094 ms com a RLS desligada
A mesma página, tempo de execução no servidor (EXPLAIN ANALYZE, mediana de 300), cinco execuções0,014 ms sem RLS; 0,016 a 0,028 ms com RLS: 0,002 a 0,014 ms a mais
Um count(*) sobre todas as 5.000 linhas de um tenant, no servidor (mediana de 300), cinco execuções3% a 27% a mais com RLS (por exemplo 0,52 contra 0,41 ms em uma execução, 3,9 contra 3,7 ms em outra; o tempo absoluto depende de as linhas estarem em cache, a diferença fica em torno de 0,1 a 0,3 ms)

O que tirar disso:

-- naive: the comment references the post by id alone
FOREIGN KEY (post_id) REFERENCES posts (id)
-- safe: the reference includes the tenant, so it cannot cross
FOREIGN KEY (tenant_id, post_id) REFERENCES posts (tenant_id, id)

Com a chave ingênua, um comentário do tenant 1 em um post do tenant 2 foi aceito. Com a chave composta, o mesmo insert foi recusado com uma violação de chave estrangeira, e um comentário em um post do próprio tenant funcionou. Do mesmo modo, a unicidade precisa ser por tenant: UNIQUE (tenant_id, slug) deixou dois tenants terem uma página chamada about e ainda recusou uma duplicata dentro de um mesmo tenant.

Os custos do modelo pool são outros: um schema para todos (nenhuma coluna por tenant sem que todos a recebam), a restauração de um tenant significa extrair linhas, e uma única tabela enorme (com um tenant quente dentro dela) é compartilhada por todos, e é aí que entram o particionamento por tenant e uma indexação cuidadosa.

4. O modelo bridge: um schema e um role por tenant

Aqui cada tenant tem o seu próprio schema (um namespace de tabelas dentro de um banco de dados) e o seu próprio role, que só enxerga esse schema. As consultas são escritas sem nenhum filtro de tenant, porque as próprias tabelas pertencem a um único tenant. O isolamento não depende de cada consulta estar certa; depende de um pequeno trecho de código que abre o escopo do tenant, mais grants que tornam impossível o escopo errado.

CREATE ROLE app LOGIN NOINHERIT;           -- what the application connects as: no rights of its own
CREATE ROLE r_alpha NOLOGIN;               -- one role per tenant
GRANT r_alpha TO app;                      -- app may SET ROLE to it
CREATE SCHEMA t_alpha;
GRANT USAGE ON SCHEMA t_alpha TO r_alpha;
GRANT SELECT ON ALL TABLES IN SCHEMA t_alpha TO r_alpha;   -- (plus the writes the app needs)
import re, contextlib
import psycopg
from psycopg import sql

SLUG = re.compile(r"^[a-z][a-z0-9_]{1,30}$")

@contextlib.contextmanager
def in_tenant(conn, slug, timeout_ms=5000):
    """The ONE place that opens a tenant scope. Everything the caller runs inside it can only see this tenant."""
    if not SLUG.match(slug):                                   # the slug becomes an identifier: validate it anyway
        raise ValueError("bad tenant slug")
    with conn.transaction():                                   # BEGIN ... COMMIT (or ROLLBACK on error)
        conn.execute(sql.SQL("SET LOCAL ROLE {}").format(sql.Identifier("r_" + slug)))
        conn.execute(sql.SQL("SET LOCAL search_path TO {}").format(sql.Identifier("t_" + slug)))
        conn.execute(sql.SQL("SET LOCAL statement_timeout = {}").format(sql.Literal(f"{int(timeout_ms)}ms")))
        yield conn                                             # unqualified queries now resolve inside t_<slug>

in_tenant é a única função que abre um escopo de tenant. Três regras a tornam segura:

  1. Valide o slug com um padrão estrito, mesmo que ele tenha vindo da sua própria tabela de tenants, porque ele é inserido no SQL como um identificador (identificadores não podem ser parâmetros de bind); use também o quoting de identificadores do driver.
  2. Use SET LOCAL para que o role e o search path terminem com a transação.
  3. Todas as consultas de tenant passam por ela; nada mais chama SET ROLE nem altera o search_path.

O que testei com dois tenants reais:

TesteResultado
O role app sem SET ROLE, lendo dados de tenantpermission denied for schema t_alpha
Dentro de in_tenant(alpha), um SELECT … FROM pages sem qualificaçãoapenas a página de alpha
Dentro de in_tenant(alpha), lendo t_beta.pagespermission denied
Os slugs ruins alpha; DROP SCHEMA t_beta, Alpha, a, alpha", ../xtodos rejeitados antes de qualquer SQL rodar
Depois do bloco, current_user e search_path na mesma conexãode volta a app e ao padrão

A armadilha da conexão de pool

Os pools de conexões entregam a mesma conexão de banco de dados a requisições consecutivas. Uma configuração feita com um SET simples permanece na conexão depois que a sua requisição termina e pertence a quem receber a conexão em seguida. Simulei um pool com uma única conexão: a requisição um mudou para alpha com um SET ROLE e um SET search_path simples; a requisição dois era um caminho de código que esqueceu de escolher um tenant.

Como a requisição um definiu o tenantO que a requisição dois, esquecida, viu
SET simplesalpha secret page: os dados de alpha, entregues a uma requisição que não tinha direito a eles
SET LOCAL dentro de uma transaçãoUndefinedTable: nada

Esta é a forma mais comum de vazamento nos sistemas multi-tenant com pool, e é por isso que todos os exemplos acima usam SET LOCAL dentro de uma transação explícita. (No primeiro experimento, o SET simples do search path sozinho não vazou, porque o role app não tinha direitos sobre o schema; a camada extra de grants foi o que salvou. Juntos, SET LOCAL e grants dão duas salvaguardas independentes.)

Como escala e como falha

Criei schemas de tenant com cinco tabelas e alguns índices cada, e depois olhei os custos:

TenantsTempo para criar todosTamanho do banco de dadosEntradas de catálogo (linhas de pg_class)Adicionar uma coluna a cada tenant (uma transação cada)
100,1 s10 MB653menos de 0,01 s
1001,4 s28 MB2.8130,03 s
1.00014,0 s214 MB24.4130,37 s

Cada tenant custa cerca de 0,2 MB e 14 ms para criar neste formato (os números vão diferir com as suas tabelas), o catálogo cresce com o número de tabelas (dezenas de milhares de entradas com mil tenants, o que é aceitável), e um ALTER TABLE … ADD COLUMN em todo o schema é uma mudança só de metadados que levou 0,37 s para 1.000 tenants. Uma conexão nova tocando um tenant executou a primeira consulta em cerca de 3 a 5 ms, independentemente do número de schemas. Um pg_dump completo dos 1.000 tenants (5.000 tabelas) levou 2,6 s (9,1 MB), e o dump de um tenant levou 0,36 s (9,9 KB).

Um limite que vale conhecer: o pg_dump pega um lock em cada tabela que despeja, e o PostgreSQL guarda todos esses locks em uma tabela compartilhada de cerca de max_locks_per_transaction × max_connections entradas (padrão 64 × 100 = 6.400) [4]. Com as configurações padrão, o pg_dump do meu banco de teste teve sucesso com 6.200 tabelas e falhou com 6.400 e 6.800 com out of shared memory (a dica cita max_locks_per_transaction). Isso dá cerca de 1.250 tenants de cinco tabelas cada. Depois de subir max_locks_per_transaction para 256 e reiniciar, o mesmo dump de 6.800 tabelas funcionou (3,5 s, 12,3 MB). Então, se você escolher um schema por tenant, ponha isto no seu plano de capacidade: mais tabelas por tenant significam menos tenants por tabela de locks de tamanho padrão, e a correção é uma configuração, não um novo projeto.

5. O modelo silo: um banco de dados ou uma implantação por tenant

No extremo forte, cada tenant ganha o seu próprio banco de dados, ou a sua própria cópia inteira da aplicação (seus próprios servidores e armazenamento). Você ganha o isolamento mais limpo (credenciais separadas, arquivos separados, limites de recursos separados), uma resposta fácil para clientes com exigências regulatórias ou de residência de dados (o banco de dados deles pode ficar no país deles) e o backup, a restauração e a exclusão por tenant mais simples. Você paga na quantidade: cada tenant é um banco de dados para migrar, monitorar e conectar, e cada um precisa do seu próprio pool de conexões (um pool por banco de dados, o que soma rápido).

Medi o custo do banco de dados por tenant em um servidor descartável: o CREATE DATABASE levou 47 ms e cada banco de dados vazio usou 7,5 MB em disco (contra cerca de 0,2 MB de um schema na seção anterior, 35 vezes menos), e um servidor PostgreSQL mínimo e ocioso (16 MB de shared buffers) usou cerca de 15 MB de memória nos seus seis processos. Então a memória não é o problema; a quantidade operacional é. Centenas de bancos de dados são gerenciáveis com automação; centenas de servidores separados são uma frota para operar.

Use silo para os tenants que precisam (os grandes, os regulados, os que pagam por isso) e um modelo mais barato para os demais. Muitos produtos acabam com pool ou bridge como padrão e silo como opção premium.

6. Achando o tenant: resolvendo a requisição

Toda requisição deve ser ligada a um tenant antes que qualquer dado de tenant seja tocado. Sinais comuns:

SinalExemploNotas
Hostnameacme.example.com, ou um domínio próprio www.acme.orgFácil para os usuários, funciona com cache estático, exige wildcard e TLS por domínio
Caminho/t/acme/...Simples, sem trabalho de DNS, tenant visível nas URLs
Cabeçalho ou claim de tokenX-Tenant: acme, uma claim em um token assinadoBom para APIs; nunca confie em um cabeçalho puro vindo do cliente
Sessãoo tenant selecionado pelo usuárioPara aplicações administrativas em que um usuário pertence a vários

Princípios que evitam os piores erros:

7. Vizinhos barulhentos e justiça

Os recursos compartilhados são divididos de forma injusta, a menos que você os divida. Um tenant importando um milhão de linhas, um relatório descontrolado ou uma rajada de chamadas de API não pode derrubar os outros. Os controles usuais:

8. Operando muitos tenants

A operação é onde os sistemas multi-tenant são ganhos ou perdidos, porque cada passo manual é multiplicado pelo número de tenants.

Criar um tenant deve ser um único comando ou chamada de API idempotente: inserir a linha, criar o schema e o role (ou o banco de dados), executar todas as migrações, popular os padrões. No modelo bridge é uma transação rápida e automática (cerca de 14 ms de trabalho de banco de dados por tenant no meu teste) e uma tentativa que falha não deixa nada pela metade. Crie cada tenant em sua própria transação, e nunca provisione muitos tenants em uma só. Uma transação mantém todos os locks que pega até terminar, então criar tenants em um laço dentro de uma única transação esbarra na mesma tabela de locks do limite do pg_dump acima: com as configurações padrão, o meu laço falhou depois de 377 tenants com out of shared memory e a dica de aumentar max_locks_per_transaction (cada tenant criava cerca de 17 objetos: quatro tabelas, sete índices, quatro sequences e o schema; 377 × 17 fica perto das 6.400 entradas disponíveis). Com uma transação por tenant, mais 1.000 foram criados sem problemas. A mesma regra vale para as migrações: execute-as por tenant, não como uma transação gigante.

Migrações. Uma mudança precisa funcionar em todos os tenants e ser segura enquanto o código antigo ainda roda:

Backups e restauração. A pergunta que importa é "podemos restaurar um tenant para ontem sem tocar no resto?" No modelo bridge é um dump e restauração em nível de schema (0,36 s para um tenant pequeno no meu teste, contra 2,6 s para todo o banco de dados de 1.000). No modelo pool significa extrair linhas por tenant e mesclá-las de volta, o que é lento e sujeito a erros, então planeje isso antes de precisar. Teste as restaurações regularmente: um backup que você nunca restaurou é uma esperança.

Apagar um tenant. Defina isso desde o início, porque os dois lados têm obrigações: apagar dados a pedido (lei de privacidade), mas manter backups e logs de auditoria conforme a política. Bridge ou silo: descarte o schema ou o banco de dados, apague a mídia, remova as entradas de roteamento e anote quando os backups que contêm os dados expiram. Pool: apague linhas por tenant em todas as tabelas, em ordem de dependência, em um job que possa ser retomado. Faça primeiro uma exclusão lógica (desative o tenant) para que um erro possa ser desfeito durante um período de carência.

9. Usuários, papéis e acesso de suporte

10. Personalização sem fork

Os tenants vão pedir diferenças. Mantenha-as como dados, não como ramificações de código:

11. Crescendo: mover um tenant, sharding, células

Mais cedo ou mais tarde um tenant fica grande demais, ou exigente demais, ou muda de país. Projete desde o começo para mover um tenant:

12. Erros comuns

13. Perguntas que os entrevistadores gostam de acrescentar

"Como você garante que um tenant nunca possa ver os dados de outro?"

Não dependa só do código da aplicação. Ponha um mecanismo no banco de dados que falhe de forma fechada: schemas e roles separados com grants (bridge), Row-Level Security (pool) ou bancos de dados separados (silo). Abra o escopo do tenant em exatamente uma função, usando configurações locais à transação. Execute a aplicação como um role sem direitos de dono da tabela nem de superusuário. E teste com dois tenants, tentando ler de um no outro.

"Qual modelo você escolheria?"

Depende do número e do tamanho dos tenants e do isolamento que eles exigem. Um padrão defensável para algumas centenas a alguns milhares de tenants variados é um banco de dados compartilhado com um schema por tenant; para milhões de tenants minúsculos, um schema compartilhado com RLS; para um punhado de tenants regulados ou muito grandes, bancos de dados separados. Diga que tornaria os grandes móveis.

"Como você lida com um cliente muito maior que o resto?"

Mova-o para o seu próprio banco de dados ou célula atrás da tabela de roteamento, sem mudar o código da aplicação, e dê a ele os seus próprios limites.

"Como você executa migrações de schema em 1.000 tenants?"

Migrações automatizadas, retomáveis e por tenant, escritas para funcionar em qualquer tenant, acompanhadas em uma tabela de estado, com expandir e contrair para que o código antigo e o novo coexistam. No meu teste, adicionar uma coluna a 1.000 schemas levou menos de meio segundo; as migrações lentas ou que travam são o risco de verdade.

"Como você impede que um tenant deixe os outros lentos?"

Limites de taxa e cotas, pools de conexões limitados, timeouts de statement definidos por transação, filas justas por tenant para o trabalho em segundo plano, cache ciente do tenant e métricas por tenant para achar o culpado.

"Por que não simplesmente adicionar uma coluna tenant_id em todo lugar?"

É o modelo mais barato e serve bem para muitos tenants minúsculos, mas o isolamento depende de cada consulta (então adicione RLS e chaves estrangeiras compostas), a restauração por tenant e as mudanças de schema por tenant ficam difíceis, e um tenant grande compartilha tabelas e índices com todo mundo.

14. Coisas a mencionar em uma entrevista

Resumo rápido

Apêndice: o código dos experimentos

Python 3 com psycopg (versão 3) e um PostgreSQL 16 no qual você possa criar e apagar bancos de dados. Edite as connection strings (os experimentos usaram um servidor local na porta 5499 com um superusuário, e apagam os bancos de dados que criam). Os tempos vêm de uma máquina pequena; o padrão, não os números, é o que vale comparar.

mt_scale.py: criar tenants como schemas, e o que isso custa:

import psycopg, time
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=postgres",autocommit=True)
c.execute("DROP DATABASE IF EXISTS mt"); c.execute("CREATE DATABASE mt")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt",autocommit=True)
DDL="""CREATE TABLE {s}.pages (id bigserial PRIMARY KEY, slug text NOT NULL UNIQUE, title text, body text, updated_at timestamptz DEFAULT now());
CREATE TABLE {s}.people (id bigserial PRIMARY KEY, name text, email text);
CREATE INDEX ON {s}.people (name);
CREATE TABLE {s}.posts (id bigserial PRIMARY KEY, title text, published_at timestamptz);
CREATE INDEX ON {s}.posts (published_at);
CREATE TABLE {s}.media (id bigserial PRIMARY KEY, key text UNIQUE, size int);"""
def size(): return c.execute("select pg_database_size('mt')").fetchone()[0]/1e6
def cat(): return c.execute("select count(*) from pg_class").fetchone()[0]
made=0; print("tenants | create time (this step) | database size | pg_class rows | ALTER one column in all (own transactions) | one query in a fresh connection")
base=size()
for target in (10,100,1000):
    t=time.perf_counter()
    while made<target:
        s=f"t_{made:04d}"; c.execute(f"CREATE SCHEMA {s}")
        for stmt in DDL.format(s=s).split(";\n"): c.execute(stmt)
        c.execute(f"INSERT INTO {s}.pages(slug,title,body) SELECT 'p'||g,'t','x' FROM generate_series(1,20) g")
        made+=1
    ct=time.perf_counter()-t
    t=time.perf_counter()
    for i in range(made): c.execute(f"ALTER TABLE t_{i:04d}.pages ADD COLUMN IF NOT EXISTS extra_{target} jsonb")
    mt=time.perf_counter()-t
    # a new connection touching one tenant (cold relcache)
    t=time.perf_counter(); c2=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt",autocommit=True)
    c2.execute("SET search_path TO t_0005"); c2.execute("SELECT count(*) FROM pages").fetchone(); q=(time.perf_counter()-t)*1000; c2.close()
    print(f"{made:7d} | {ct:6.1f} s | {size():6.0f} MB | {cat():6d} | {mt:5.2f} s | {q:5.1f} ms")

mt_rls.py: Row-Level Security, o bypass do dono e do superusuário, e seu custo extra:

import psycopg, time, statistics
S="host=/tmp port=5499 user=postgres dbname=postgres"
adm=psycopg.connect(S,autocommit=True)
adm.execute("DROP DATABASE IF EXISTS mt2"); adm.execute("CREATE DATABASE mt2")
for r in ("app_user","owner_role"):
    adm.execute(f"DROP ROLE IF EXISTS {r}")
adm.execute("CREATE ROLE owner_role LOGIN"); adm.execute("CREATE ROLE app_user LOGIN")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt2",autocommit=True)
c.execute("CREATE TABLE docs (id bigserial PRIMARY KEY, tenant_id int NOT NULL, title text)")
c.execute("CREATE INDEX ON docs (tenant_id, id)")
c.execute("ALTER TABLE docs OWNER TO owner_role")
c.execute("GRANT SELECT, INSERT ON docs TO app_user"); c.execute("GRANT USAGE ON SEQUENCE docs_id_seq TO app_user")
c.execute("INSERT INTO docs(tenant_id,title) SELECT 1+g%100, 'doc '||g FROM generate_series(1,500000) g"); c.execute("ANALYZE docs")
c.execute("ALTER TABLE docs ENABLE ROW LEVEL SECURITY")
c.execute("CREATE POLICY by_tenant ON docs USING (tenant_id = current_setting('app.tenant_id')::int) WITH CHECK (tenant_id = current_setting('app.tenant_id')::int)")
def conn(user): return psycopg.connect(f"host=/tmp port=5499 user={user} dbname=mt2",autocommit=False)
# 1. the forgotten WHERE
a=conn("app_user")
with a.transaction():
    a.execute("SELECT set_config('app.tenant_id','7',true)")
    n=a.execute("SELECT count(*) FROM docs").fetchone()[0]; t=a.execute("SELECT count(DISTINCT tenant_id) FROM docs").fetchone()[0]
print(f"1. app_user, tenant 7, query with NO WHERE: sees {n} rows from {t} tenant(s) (table has 500000 rows, 100 tenants)")
# 2. no tenant set -> error, not empty/all
a.rollback()
try:
    a.execute("SELECT count(*) FROM docs").fetchone()
except Exception as e: print("2. no tenant set:", type(e).__name__, str(e).splitlines()[0]); a.rollback()
# 3. insert for another tenant is refused by WITH CHECK
try:
    with a.transaction():
        a.execute("SELECT set_config('app.tenant_id','7',true)"); a.execute("INSERT INTO docs(tenant_id,title) VALUES (8,'sneaky')")
except Exception as e: print("3. insert for tenant 8 while tenant 7:", type(e).__name__, str(e).splitlines()[0])
# 4. owner bypasses RLS unless FORCE; superuser always bypasses
o=conn("owner_role"); n=o.execute("SELECT count(*) FROM docs").fetchone()[0]; o.rollback(); print(f"4a. table owner without FORCE: sees {n} rows (RLS bypassed)")
c.execute("ALTER TABLE docs FORCE ROW LEVEL SECURITY")
try: o.execute("SELECT count(*) FROM docs").fetchone()
except Exception as e: print("4b. table owner with FORCE and no tenant set:", type(e).__name__); o.rollback()
print("4c. superuser (postgres):", c.execute("SELECT count(*) FROM docs").fetchone()[0], "rows (always bypasses RLS; connect the app with a plain role)")
# 5. overhead: RLS vs explicit WHERE (same index), median of 200 runs
def bench(sql, setcfg=True, runs=200):
    ts=[]
    with a.transaction():
        if setcfg: a.execute("SELECT set_config('app.tenant_id','7',true)")
        for _ in range(runs):
            t=time.perf_counter(); a.execute(sql).fetchall(); ts.append((time.perf_counter()-t)*1000)
    return statistics.median(ts)
c.execute("ALTER TABLE docs NO FORCE ROW LEVEL SECURITY")
q="SELECT id, title FROM docs {w} ORDER BY id DESC LIMIT 20"
x=bench(q.format(w="WHERE tenant_id = 7"),True)
y=bench(q.format(w=""),True)
print(f"5. page of 20 for one tenant, median of 200: explicit WHERE (RLS also on) {x:.3f} ms; RLS only {y:.3f} ms")
c.execute("ALTER TABLE docs DISABLE ROW LEVEL SECURITY")
z=bench(q.format(w="WHERE tenant_id = 7"),False)
print(f"   explicit WHERE with RLS off {z:.3f} ms")
c.execute("ALTER TABLE docs ENABLE ROW LEVEL SECURITY")

# 6. server-side execution time (EXPLAIN ANALYZE): app_user is subject to RLS, owner_role bypasses it
import re
c.execute("ALTER TABLE docs NO FORCE ROW LEVEL SECURITY")
def exec_ms(user, q, runs=300):
    k=conn(user); ts=[]
    with k.transaction():
        k.execute("SELECT set_config('app.tenant_id','7',true)")
        for _ in range(runs):
            r=k.execute("EXPLAIN (ANALYZE, TIMING OFF, SUMMARY ON) "+q).fetchall()
            ts.append(float(re.search(r"Execution Time: ([\d.]+) ms","\n".join(x[0] for x in r)).group(1)))
    k.close(); return statistics.median(ts)
for label,q in (("page of 20","SELECT id, title FROM docs WHERE tenant_id = 7 ORDER BY id DESC LIMIT 20"),("count of the tenant's 5,000 rows","SELECT count(*) FROM docs WHERE tenant_id = 7")):
    print(f"6. server-side, {label}: with RLS {exec_ms('app_user',q):.3f} ms | without RLS (owner bypasses) {exec_ms('owner_role',q):.3f} ms")

mt_pool.py: roles e grants, a armadilha da conexão de pool e os timeouts por role:

import psycopg
S="host=/tmp port=5499 user=postgres dbname=postgres"
adm=psycopg.connect(S,autocommit=True)
adm.execute("DROP DATABASE IF EXISTS mt3"); adm.execute("CREATE DATABASE mt3")
for r in ("app","r_alpha","r_beta"): adm.execute(f"DROP ROLE IF EXISTS {r}")
adm.execute("CREATE ROLE app LOGIN NOINHERIT"); adm.execute("CREATE ROLE r_alpha NOLOGIN"); adm.execute("CREATE ROLE r_beta NOLOGIN")
adm.execute("GRANT r_alpha, r_beta TO app")          # app may SET ROLE to either, but has no rights of its own
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt3",autocommit=True)
for t in ("alpha","beta"):
    c.execute(f"CREATE SCHEMA t_{t}"); c.execute(f"CREATE TABLE t_{t}.pages (id serial primary key, title text)")
    c.execute(f"INSERT INTO t_{t}.pages(title) VALUES ('{t} secret page')")
    c.execute(f"GRANT USAGE ON SCHEMA t_{t} TO r_{t}"); c.execute(f"GRANT SELECT ON ALL TABLES IN SCHEMA t_{t} TO r_{t}")
def conn(): return psycopg.connect("host=/tmp port=5499 user=app dbname=mt3",autocommit=True)
# 1. the app role alone sees nothing
a=conn()
try: a.execute("SELECT * FROM t_alpha.pages")
except Exception as e: print("1. app role, no SET ROLE:", type(e).__name__, str(e).splitlines()[0])
# 2. inside a tenant transaction: its own data yes, the other tenant's no
a.execute("BEGIN"); a.execute("SET LOCAL ROLE r_alpha"); a.execute("SET LOCAL search_path TO t_alpha")
print("2a. as alpha, unqualified query:", a.execute("SELECT title FROM pages").fetchall())
try: a.execute("SELECT * FROM t_beta.pages")
except Exception as e: print("2b. as alpha reading t_beta:", type(e).__name__, str(e).splitlines()[0])
a.execute("ROLLBACK")
print("2c. after the transaction the connection is back to:", a.execute("SELECT current_user").fetchone()[0], "and search_path", a.execute("SHOW search_path").fetchone()[0])
# 3. the pooled-connection trap: a pool of ONE connection shared by consecutive requests
pool=[conn()]
def request(tenant, use_local):
    k=pool[0]; k.execute("BEGIN")
    kw="LOCAL " if use_local else ""
    k.execute(f"SET {kw}ROLE r_{tenant}"); k.execute(f"SET {kw}search_path TO t_{tenant}")
    k.execute("COMMIT")
def handler_forgetting_tenant():                       # a code path that never set a tenant
    k=pool[0]; k.execute("BEGIN")
    try: r=k.execute("SELECT title FROM pages").fetchall()
    except Exception as e: r=type(e).__name__
    k.execute("ROLLBACK"); return r
for use_local in (False, True):
    request("alpha", use_local)
    print(f"3{'b' if use_local else 'a'}. {'SET LOCAL' if use_local else 'plain SET'} for alpha's request, then a request that never set a tenant ->", handler_forgetting_tenant())
    pool[0].execute("RESET ROLE"); pool[0].execute("RESET search_path")
# 4. per-role guard rails: ALTER ROLE ... SET applies at LOGIN, not at SET ROLE
c.execute("ALTER ROLE r_beta SET statement_timeout = '300ms'")
import time
b=conn(); b.execute("BEGIN"); b.execute("SET LOCAL ROLE r_beta")
t=time.perf_counter(); b.execute("SELECT pg_sleep(1.5)"); print("4a. ALTER ROLE ... SET statement_timeout, reached with SET ROLE: a 1.5 s query ran to the end (%.2f s)"%(time.perf_counter()-t)); b.execute("ROLLBACK")
b.execute("BEGIN"); b.execute("SET LOCAL ROLE r_beta"); b.execute("SET LOCAL statement_timeout = '300ms'")
t=time.perf_counter()
try: b.execute("SELECT pg_sleep(5)")
except Exception as e: print("4b. SET LOCAL statement_timeout in the same transaction: cancelled after %.2f s (%s)"%(time.perf_counter()-t, type(e).__name__))

mt_helper.py: a função que abre um escopo de tenant (execute mt_pool.py primeiro, ele cria os roles e os schemas):

import re, contextlib
import psycopg
from psycopg import sql

SLUG = re.compile(r"^[a-z][a-z0-9_]{1,30}$")

@contextlib.contextmanager
def in_tenant(conn, slug, timeout_ms=5000):
    """The ONE place that opens a tenant scope. Everything the caller runs inside it can only see this tenant."""
    if not SLUG.match(slug):                                   # the slug becomes an identifier: validate it anyway
        raise ValueError("bad tenant slug")
    with conn.transaction():                                   # BEGIN ... COMMIT (or ROLLBACK on error)
        conn.execute(sql.SQL("SET LOCAL ROLE {}").format(sql.Identifier("r_" + slug)))
        conn.execute(sql.SQL("SET LOCAL search_path TO {}").format(sql.Identifier("t_" + slug)))
        conn.execute(sql.SQL("SET LOCAL statement_timeout = {}").format(sql.Literal(f"{int(timeout_ms)}ms")))
        yield conn                                             # unqualified queries now resolve inside t_<slug>

if __name__ == "__main__":
    conn = psycopg.connect("host=/tmp port=5499 user=app dbname=mt3", autocommit=True)
    with in_tenant(conn, "alpha") as c:
        assert c.execute("SELECT title FROM pages").fetchall() == [("alpha secret page",)]
        try: c.execute("SELECT * FROM t_beta.pages"); raise SystemExit("LEAK")
        except psycopg.errors.InsufficientPrivilege: pass     # the other tenant's schema is closed to this role
    for bad in ("alpha; DROP SCHEMA t_beta", "Alpha", "a", 'alpha"', "../x"):
        try:
            with in_tenant(conn, bad): raise SystemExit("accepted " + bad)
        except ValueError: pass
    with in_tenant(conn, "beta") as c:
        assert c.execute("SELECT title FROM pages").fetchall() == [("beta secret page",)]
    assert conn.execute("SELECT current_user").fetchone()[0] == "app"          # nothing leaked to the next user of the connection
    assert conn.execute("SHOW search_path").fetchone()[0] == '"$user", public'
    with in_tenant(conn, "alpha", timeout_ms=200) as c:
        try: c.execute("SELECT pg_sleep(2)"); raise SystemExit("no timeout")
        except psycopg.errors.QueryCanceled: pass
    print("all tenant-scope checks passed")

mt_dump.py: pg_dump do banco de dados inteiro e de um tenant, e o limite da tabela de locks (ele imprime a falha com 6.800 tabelas nas configurações padrão):

import psycopg, subprocess, time, os
env=dict(os.environ, PGHOST="/tmp", PGPORT="5499", PGUSER="postgres")
adm=psycopg.connect("host=/tmp port=5499 user=postgres dbname=postgres",autocommit=True)
adm.execute("DROP DATABASE IF EXISTS mt5"); adm.execute("CREATE DATABASE mt5")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt5",autocommit=True)
def provision(i):
    s=f"t_{i:04d}"; c.execute("BEGIN"); c.execute(f"CREATE SCHEMA {s}")      # one transaction per tenant
    for t in ("pages","people","posts","media","extra"): c.execute(f"CREATE TABLE {s}.{t} (id bigserial PRIMARY KEY, v text)")
    c.execute(f"INSERT INTO {s}.pages(v) SELECT 'row '||g FROM generate_series(1,20) g"); c.execute("COMMIT")
def dump(*args):
    t=time.perf_counter(); r=subprocess.run(["pg_dump","-Fc","-f","/tmp/g5/dump.out",*args,"mt5"],env=env,capture_output=True,text=True)
    return time.perf_counter()-t, os.path.getsize("/tmp/g5/dump.out") if r.returncode==0 else None, r.stderr.strip().splitlines()[:1]
n=0
for target in (1000, 1360):                                    # 1,000 tenants = 5,000 tables; 1,360 tenants = 6,800 tables
    while n<target: provision(n); n+=1
    tables=c.execute("select count(*) from pg_tables where schemaname like 't\\_%'").fetchone()[0]
    t,size,err=dump(); print(f"{n} tenants ({tables} tables): whole database {t:.1f} s, {size/1e6 if size else 0:.1f} MB", err or "")
    if target==1000:
        t,size,err=dump("-n","t_0005"); print(f"  one tenant (-n t_0005): {t:.2f} s, {size/1e3:.1f} KB")

mt_txn.py: provisionar muitos tenants em uma transação contra uma transação por tenant:

import psycopg
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=postgres",autocommit=True)
c.execute("DROP DATABASE IF EXISTS mt4"); c.execute("CREATE DATABASE mt4")
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt4",autocommit=True)
print("max_locks_per_transaction =",c.execute("show max_locks_per_transaction").fetchone()[0],", max_connections =",c.execute("show max_connections").fetchone()[0])
DDL=["CREATE TABLE {s}.pages (id bigserial PRIMARY KEY, slug text UNIQUE, title text)",
     "CREATE TABLE {s}.people (id bigserial PRIMARY KEY, name text)","CREATE INDEX ON {s}.people (name)",
     "CREATE TABLE {s}.posts (id bigserial PRIMARY KEY, title text)","CREATE TABLE {s}.media (id bigserial PRIMARY KEY, key text UNIQUE)"]
def provision(i):
    s=f"t_{i:04d}"; c.execute(f"CREATE SCHEMA {s}")
    for d in DDL: c.execute(d.format(s=s))
# all tenants in ONE transaction
made=0
c.execute("BEGIN")
try:
    for i in range(5000): provision(i); made+=1
    c.execute("COMMIT"); print("one transaction: created all 5000")
except Exception as e:
    print(f"one transaction: failed after {made} tenants with {type(e).__name__}: {str(e).splitlines()[0]}")
    print("  hint:", [l for l in str(e).splitlines() if 'HINT' in l or 'max_locks' in l][:1])
    c.execute("ROLLBACK")
# one transaction per tenant
c.execute("DROP DATABASE IF EXISTS mt4") if False else None
for i in range(1000):
    c.execute("BEGIN"); provision(10000+i); c.execute("COMMIT")
print("one transaction per tenant: created 1000 more without trouble")

mt_fk.py: referências entre tenants e unicidade por tenant:

import psycopg
c=psycopg.connect("host=/tmp port=5499 user=postgres dbname=mt2",autocommit=True)
c.execute("DROP TABLE IF EXISTS comments, posts CASCADE")
c.execute("CREATE TABLE posts (tenant_id int NOT NULL, id bigint NOT NULL, title text, PRIMARY KEY (tenant_id, id))")
# naive: comment references posts(id) only; safe: the reference carries tenant_id too
c.execute("CREATE TABLE comments_naive (tenant_id int NOT NULL, id bigserial PRIMARY KEY, post_id bigint NOT NULL, body text)")
c.execute("CREATE UNIQUE INDEX posts_id_only ON posts (id)")
c.execute("ALTER TABLE comments_naive ADD FOREIGN KEY (post_id) REFERENCES posts (id)")
c.execute("CREATE TABLE comments_safe (tenant_id int NOT NULL, id bigserial PRIMARY KEY, post_id bigint NOT NULL, body text, FOREIGN KEY (tenant_id, post_id) REFERENCES posts (tenant_id, id))")
c.execute("INSERT INTO posts VALUES (1, 100, 'tenant 1 post'), (2, 200, 'tenant 2 post')")
c.execute("INSERT INTO comments_naive (tenant_id, post_id, body) VALUES (1, 200, 'tenant 1 comments on a tenant 2 post')")
print("naive FK (post_id only): the cross-tenant comment was ACCEPTED")
try: c.execute("INSERT INTO comments_safe (tenant_id, post_id, body) VALUES (1, 200, 'same attempt')")
except Exception as e: print("composite FK (tenant_id, post_id): refused with", type(e).__name__)
c.execute("INSERT INTO comments_safe (tenant_id, post_id, body) VALUES (1, 100, 'own post')"); print("composite FK: a comment on the tenant's own post is accepted")
# uniqueness per tenant
c.execute("CREATE TABLE pages (tenant_id int NOT NULL, slug text NOT NULL, UNIQUE (tenant_id, slug))")
c.execute("INSERT INTO pages VALUES (1,'about'),(2,'about')"); print("UNIQUE (tenant_id, slug): two tenants can both have 'about'")
try: c.execute("INSERT INTO pages VALUES (1,'about')")
except Exception as e: print("the same tenant twice:", type(e).__name__)

Fontes

  1. Amazon Web Services, SaaS Tenant Isolation Strategies. Os modelos silo, pool e bridge.
  2. Documentação do PostgreSQL, Row Security Policies. USING, WITH CHECK, FORCE ROW LEVEL SECURITY e o bypass do dono e do superusuário.
  3. Documentação do PostgreSQL, Schemas, SET (SET LOCAL) e ALTER ROLE (as configurações de role entram em vigor no início da sessão).
  4. Documentação do PostgreSQL, max_locks_per_transaction (o tamanho da tabela de locks compartilhada, padrão 64) e pg_dump.
  5. Microsoft, Multitenant SaaS database tenancy patterns. Os modelos de banco de dados por tenant, multitenant com sharding e híbrido.
  6. Amazon Web Services, Reducing the scope of impact with cell-based architecture. Células e raio de impacto.

Pratique este tema: System Design System Design › Case studies

Mais guias