Shared tables
Row-policy centered
Operationally compact, with greater dependence on correct policy and role use.
Application architecture · Tenant boundaries
Isolation you can prove.
I needed tenant separation to survive more than a remembered filter. The design had to align trusted identity, PostgreSQL privileges, row policy, and tests that could genuinely fail.
A missing WHERE tenant_id = ? can turn an ordinary
request into a disclosure. That makes application discipline useful,
but insufficient as the only boundary.
This entry records a defense-in-depth design direction for CareFlow. It is not a universal database recipe and does not claim that any single mechanism creates compliance.
Chapter 01
Choose the isolation unit deliberately.
Tenant count, data sensitivity, recovery boundaries, operational load, and customer evidence all affect the choice.
Schema per tenant
Visible namespace
A structural boundary with migrations and provisioning that must stay coordinated.
Database per tenant
Administrative separation
Stronger isolation possibilities with higher operational cost.
Chapter 02
Three boundaries must agree.
Each layer answers a different authorization question.
01 · Identity
Who is asking?
Tenant authority comes from validated, trusted context—not a client-supplied tenant field.
02 · Grants
Where may it enter?
Narrow roles constrain reachable namespaces and operations.
03 · RLS
Which rows?
Row-level policy is a backstop for shared relations, not the whole design.
Chapter 03
Authority should expire with the transaction.
Connection pools make leftover session state a cross-request risk.
BEGIN
→
VALIDATE CLAIM
→
SET LOCAL ROLE
→
QUERY
→
COMMIT / RESET
Chapter 04
The test that lies.
A privileged local identity can make row-policy tests meaningless.
PostgreSQL documents that superusers and roles with
BYPASSRLS always bypass row security. Table owners
normally bypass it too unless row-level security is forced. Test
through a dedicated non-superuser role that resembles the production
path.
Chapter 05
Prove denial, not only success.
| Probe | Expected evidence |
|---|---|
| Own-tenant read and write | Allowed through the application-like role |
| Other-tenant read | No rows disclosed |
| Other-tenant insert or update | Rejected by privilege or policy |
| Pooled connection reuse | Prior tenant authority does not survive |
| Migration regression | All probes repeat in CI |
Field check
Before the boundary is trusted.
Primary sources
Watch the entry
Isolation You Can Prove
How CareFlow aligns trusted tenant identity, database privileges,
row policy, and production-like leakage tests.