Every SourceForge product that touches more than one customer is multi-tenant from line one. We do not build single-tenant products and then "add tenancy later" — the schema, the auth, the audit log all assume multi-tenancy from the first migration. The mechanism is Postgres Row-Level Security, two database roles, and a discipline. This is the practical implementation.
The shape
A workspace is the tenancy unit. Every customer of a SourceForge product gets one workspace (sometimes more, if they run multiple brands or business units). Every row in every business table carries a workspace_id column. RLS policies attached to each table ensure that queries from a tenant-scoped database role only see rows where workspace_id matches the current session variable.
The session variable is set on every request, server-side, after authentication. The application never queries the database without first setting it. If a request fails to set the workspace_id, the queries return zero rows — which is the right failure mode.
Two database roles
The most important architectural choice: two Postgres roles, not one.
- app — used by the web application. Subject to RLS. Cannot bypass.
- system — used by background workers, migrations, admin tools. Bypasses RLS.
The web application connects through a connection pool that authenticates as 'app'. Every web request runs queries that respect RLS. A request authenticated for workspace A literally cannot read workspace B's data, because the underlying SELECT statement returns zero rows from B's perspective.
The worker pool connects as 'system'. Background jobs that need to operate across workspaces — sending a batch email, running a daily reconciliation, processing a queue — see all rows. This is intentional and is what makes cross-tenant aggregation, monitoring and reporting possible.
The split is enforced at connection-string level. The two roles live in two separate connection pools; the web app never gets a 'system' pool handle. Crossing the boundary requires explicit code that the security review catches.
RLS policy shape
A typical policy on a workspace-scoped table:
CREATE POLICY workspace_isolation ON sales_orders USING (workspace_id = current_setting('app.workspace_id')::uuid);
Reads, writes, updates, deletes all go through the same policy. The policy compiles to a WHERE clause appended to every query. The query planner is smart enough to use it as an index predicate, so RLS imposes near-zero performance cost when the workspace_id column is indexed (which it always is).
Setting the workspace_id
After authentication, before any query runs, the request handler executes:
SET LOCAL app.workspace_id = '...';
The SET LOCAL scopes the variable to the current transaction, which Postgres ties to the connection. The connection is returned to the pool with the variable still set; the next request resets it. We've never had a workspace_id leak across requests, because the next request always overwrites — and because we wrap every request in a transaction.
A small but important detail: when the auth layer fails to identify a workspace, we set the variable to a non-existent UUID, not to NULL. NULL would make the policy comparison evaluate to NULL, which is not the same as false in three-valued logic. A non-existent UUID guarantees zero rows.
What about the public web routes?
Some routes serve unauthenticated traffic — marketing pages, public certificate verification, blog posts. These run on the 'app' role with a special workspace_id reserved for public content. The pattern is:
- Public content lives in tables with workspace_id = 'public' (a reserved UUID)
- Public routes set workspace_id = 'public' before querying
- Customer routes set workspace_id = customer's UUID
- Cross-workspace routes only run as 'system', through a separate connection pool
This means we never have a route that "decides at runtime" whether it's public or scoped. The decision is encoded in which connection pool the route talks to.
What this protects against
The standard threat model:
- Mis-scoped query — a developer writes SELECT * FROM customers without a WHERE workspace_id = ?. The RLS policy adds the predicate automatically. The bug is invisible until someone notices it in code review; the production impact is zero.
- SQL injection — an attacker injects malicious SQL through a parameter. Even if they succeed in altering the WHERE clause, the RLS policy is appended after. They cannot see other workspaces' data.
- Auth bypass — an attacker forges a session and authenticates as another user. They see that user's workspace. They do not see other workspaces. The blast radius is contained to one tenant.
What it does not protect against
- System role compromise — if an attacker gets a 'system' connection, they see everything. We protect this with separate VPC subnets, separate secrets management, and aggressive logging of system-role queries.
- The workspace_id session variable being set wrong — if the auth layer sets the wrong workspace_id for a request, the user sees another workspace's data. We mitigate with extensive unit tests around the auth-to-workspace mapping.
- Backups — backups are full-database. We protect customer data in backups with at-rest encryption and separate access controls.
When we'd revisit
We've considered moving to schema-per-tenant or even database-per-tenant for very large customers. Both work; both have different operational characteristics; neither is strictly better. For our current scale, RLS is the right balance of isolation and ops simplicity. We'd revisit if a single customer's data ever needed legal residency separation, which has not yet happened.
The discipline is what matters more than the mechanism. We're strict about it because retrofitting tenancy onto a single-tenant codebase is one of the most expensive engineering exercises in software. Don't.