TenantForge — a multi-tenant SaaS backend starter

In a multi-tenant backend the usual answer is "always filter by tenant_id", and it is one forgotten WHERE clause away from a breach — in a new endpoint, in a JOIN somebody wrote at speed, in a COUNT(*) for a dashboard. It relies on every developer, forever, and it fails open: the symptom of the bug is more data, not less, so nothing crashes and no test fails unless somebody thought to write that exact test.

One database, one schema, a tenant_id column on every tenant-owned table, and PostgreSQL Row-Level Security enforcing it. The repository's single claim is that a request can only ever see the workspace its token names, and the suite exists to try to break it.

Three things make the RLS real rather than decorative. The application connects as a role that cannot escape a policy — the first migration provisions it NOSUPERUSER and NOBYPASSRLS, because "we have RLS" is worth nothing until you can say which role connects. Every policy is FORCEd, since without that a table's owner is exempt from its own policies, and the owner is exactly the role migrations and seeds run as. The tenant is a transaction-local setting, not a query parameter: the request dependency runs set_config('app.current_tenant', …, true), so the value dies with the transaction and cannot leak onto the next checkout of a pooled connection.

Each policy has both halves. USING is why an id from another workspace resolves to nothing; WITH CHECK is why a service that computed the wrong tenant_id gets its INSERT refused instead of silently storing a leaked row. And it fails closed: with the setting unset the predicate is NULL, so an unbound session reads zero rows. The default state of a session that forgot to identify itself is blindness.

Database-per-tenant and schema-per-tenant were both considered and are both argued against in the README rather than waved away — N migration runs per deploy, a partial-failure story, search_path as load-bearing global state. The real cost of the chosen approach is stated too: the policies are invisible in the Python, so a newcomer reads a repository method with no tenant filter and has to know the database is adding one. That is why the suite pins the role attributes, pg_class and pg_policies directly.

The rest is the ordinary security work done properly. Argon2id at the OWASP profile with rehash on login. Refresh tokens are not JWTs — they are opaque random strings stored as a keyed HMAC digest, so a leaked table yields no usable credential, and rotation detects reuse: presenting a consumed token means a copy escaped, so the whole family is revoked and the legitimate holder notices rather than quietly sharing a session with a thief. Access tokens stay stateless but revocable through a token_version claim. UUID keys, one login failure message for every cause with a dummy verification on the unknown-user path, and 404 rather than 403 for another workspace's data — a 403 answers the only question an enumerator is asking.