Multi-Tenancy, Zero-Trust Access & Schema History
You'll learn to
- -Isolate tenants in a shared table with Row-Level Security, and see what goes wrong without it
- -Explain zero trust and why an access log records denials as well as grants
- -Store a version history as a chain of rows that each point at their parent
This chapter joins three concerns that show up together in real large systems: keeping many tenants' data safely apart in one shared schema, checking every access explicitly instead of trusting network location, and keeping a full version history of changing records. All three reuse techniques from earlier in the course.
An Analogy: The Apartment Building
An apartment building shares one structure, one set of pipes and one front door, but every flat has its own locked door. A tenant is one customer or group whose data must stay private from the others. You could build a separate building per tenant, which is the most private and the most expensive. Or you could share the building and make sure every door is secured, which is cheaper but only safe if no door is ever left open by mistake.
Multi-Tenancy: Shared Schema, Isolated Data
The two options for a multi-tenant system are a separate database per tenant, with the strongest isolation and a high operating cost at scale, or one shared schema with a tenant_id column on every table, which is efficient at scale. The shared option has a danger. Isolation now depends on every single query remembering to filter by tenant_id, and one mistake leaks one tenant's data to another.
Run the demo to see the leak and its fix. Three rows belong to two tenants. A query that forgets the filter sees everyone's rows. The same careless query run through a tenant-aware session sees only the right ones, because the session applies the policy itself.
In a real database this is Row-Level Security: a policy attached to the table so the database filters every query by tenant automatically.
CREATE TABLE faction_resources (
id INTEGER PRIMARY KEY,
tenant_id INTEGER NOT NULL,
resource_name TEXT NOT NULL,
quantity INTEGER NOT NULL
);
-- Row-Level Security: the database itself filters every query by tenant,
-- rather than trusting every application query to remember WHERE tenant_id = ...
ALTER TABLE faction_resources ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON faction_resources
USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);That moves tenant isolation from "every developer must remember to filter by tenant_id in every query, forever" to "the database enforces it even if a query forgets". It is the same principle as the constraints chapter: enforce it in the schema, not in application code.
Zero-Trust Access Control
Zero trust means every access request is checked on its own. A session is not trusted just because the user logged in once, or because the request came from inside a trusted network. At the schema level, check each sensitive read or write against the current session and permission state, and log every attempt, whether it was allowed or refused.
CREATE TABLE access_log (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
resource_id INTEGER NOT NULL,
action TEXT NOT NULL, -- 'read', 'write', 'delete'
was_granted BOOLEAN NOT NULL, -- logging denials is as important as successes
session_verified_at TIMESTAMP NOT NULL,
logged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);Logging denials matters as much as logging grants. A pattern of refused requests, such as one user trying a hundred resources they may not open, is exactly how misuse is spotted.
Git-Like Version-Controlled History
A version-controlled record keeps every change as an immutable version, so any past version can be rebuilt and the difference between two versions shown. It extends the event-sourcing pattern from the transactional module to arbitrary content. Each version points at its parent, like a commit in git. The demo commits three versions and then finds the current one and walks its history.
CREATE TABLE content_versions (
id INTEGER PRIMARY KEY,
document_id INTEGER NOT NULL,
parent_version_id INTEGER REFERENCES content_versions(id), -- NULL for the first version
content TEXT NOT NULL,
author_id INTEGER NOT NULL,
committed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- the current version of a document is whichever version has no
-- child pointing back at it as parent_version_id (the tip of the chain)This is a self-referencing structure, like the friend graph, but each version has at most one parent, so it forms a chain (or a tree if branching is supported) and not a general graph. That is why rebuilding history is a simple walk instead of a graph traversal.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Zion Access Control, The Source Code, and Machine City in the LLD Lab's Matrix act.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!