Multi-tenant SaaS in Go: row, schema or database per tenant
Multi-tenancy is one question with three answers, and the question is not technical: how bad is it if one customer sees another customer's data. Everything else, the query patterns, the migration strategy, the connection pooling, follows from where you put that line.
Most teams should choose row-level isolation and spend the saved effort on testing it properly. This explains why, and names the cases where that is the wrong call.
22 min read. Commands and output last run against Grit v3.560.0.
What you will have at the end
A decision you can justify to a security reviewer, with the alternatives considered.
The implementation for row-level isolation in Go and Postgres, including the parts that are usually missed.
A test that actually proves isolation rather than assuming it.
The three models
Row-level: one database, one schema, a tenant column on every table
invoices(id, organisation_id, number, total, ...)^^^^^^^^^^^^^^^ on every tenant-owned table, indexed, not null
- Isolation: application logic, optionally backed by Postgres row-level security.
- Migrations: one, for everybody.
- Onboarding a tenant: one insert.
- Scales to: tens of thousands of tenants without difficulty.
- Failure mode: a forgotten
WHEREclause is a data breach.
Schema per tenant: one database, a Postgres schema each
SET search_path TO tenant_8821;SELECT * FROM invoices; -- cannot see tenant_8822 at all
- Isolation: enforced by the database. A missing clause returns your own rows, not somebody else's.
- Migrations: once per schema. At two thousand tenants this is a job with progress reporting, partial failure and a resume path.
- Onboarding: create a schema and run every migration into it.
- Scales to: low thousands. Postgres slows down with very large numbers of tables, and every schema multiplies the table count.
- Failure mode: a connection returned to the pool with the wrong
search_path.
Database per tenant
- Isolation: complete, including backups, restores and physical location. This is what buys "your data is in your own database, in your own region".
- Migrations: a deployment pipeline, not a command.
- Onboarding: provision a database. Minutes, not milliseconds.
- Scales to: hundreds, and each one costs money whether it is used or not.
- Failure mode: connection pool exhaustion, because a pool per tenant times hundreds of tenants exceeds what Postgres will accept.
Choosing
Row-level unless something forces otherwise. The things that force otherwise are specific and recognisable: a contract promising physical separation, a regulator requiring data residency per customer, per-tenant restore as a product feature, or a handful of enormous tenants whose load must not affect the others.
Hybrid is legitimate and common: row-level for the long tail, a dedicated database for the four customers who demanded one. Design the tenant resolution layer so the connection is a lookup rather than a global, and this stays possible later.
Row-level, done properly
The tenant is never nullable, and never optional
// Embedded in every tenant-owned model, so the field cannot be// forgotten when a new model is added.type Owned struct {OrganisationID uint `gorm:"index;not null" json:"-"`}type Invoice struct {ID uint `gorm:"primaryKey"`OwnedNumber string `gorm:"not null"`Total int64}
json:"-" matters: the tenant id should never be accepted from a request body. It comes from the authenticated session and nowhere else. A field that is bindable is a field a caller can set.
The scope is an argument, not a check
// Not this: forgetting the second line still compiles and still// returns the row.inv, _ := repo.ByID(ctx, id)if inv.OrganisationID != orgID { ... }// This: forgetting the scope does not compile.inv, err := repo.ByID(ctx, orgID, id)
Over several years and several people, the design that fails closed is the one that is still correct. That is the entire argument, and it is enough.
Composite indexes in the right order
-- Wrong order: the tenant column is not the leading column, so this-- index cannot serve "this tenant's invoices, newest first".CREATE INDEX ON invoices (created_at, organisation_id);-- Right: every query starts by narrowing to one tenant.CREATE INDEX ON invoices (organisation_id, created_at DESC);CREATE INDEX ON invoices (organisation_id, status);
Every query filters by tenant first, so the tenant column leads every composite index. This is the single most impactful performance decision in a row-level design, and it is invisible until one customer has a hundred thousand rows.
Unique constraints are per tenant
-- Wrong: two different customers can never both have invoice INV-001.CREATE UNIQUE INDEX ON invoices (number);-- Right.CREATE UNIQUE INDEX ON invoices (organisation_id, number);
This one is usually found by a customer rather than by a test, because it only fires when two tenants happen to choose the same value, which may be months after launch.
Resolving the tenant, once, at the edge
The tenant must be established in exactly one place, from a source the caller cannot choose.
func Tenant() gin.HandlerFunc {return func(c *gin.Context) {// From the verified session, not from a header, a query parameter// or a body field. A tenant a caller can state is a tenant a// caller can change.orgID := auth.Claims(c).OrganisationIDif orgID == 0 {c.AbortWithStatusJSON(http.StatusUnauthorized, respond.Unauthenticated())return}c.Set("org_id", orgID)c.Next()}}
A subdomain is a routing hint, not an authorisation
acme.yourapp.com is a convenient way to land somebody in the right place. It must not be what decides which data they see. If the subdomain picks the tenant and the session says otherwise, the session wins and the request is refused: otherwise visiting another customer's subdomain while signed in is the whole exploit.
Postgres row-level security as a second layer
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;ALTER TABLE invoices FORCE ROW LEVEL SECURITY; -- applies to the owner tooCREATE POLICY tenant_isolation ON invoicesUSING (organisation_id = current_setting('app.org_id', true)::bigint);
// Every checkout from the pool must set it, and every return must// clear it, or a reused connection carries the previous tenant.func WithTenant(ctx context.Context, db *gorm.DB, orgID uint, fn func(*gorm.DB) error) error {return db.Transaction(func(tx *gorm.DB) error {// set_config with local=true scopes the setting to this// transaction, so it cannot leak to the next user of this// connection. The third argument is what makes that true.if err := tx.Exec("SELECT set_config('app.org_id', ?, true)",strconv.FormatUint(uint64(orgID), 10)).Error; err != nil {return fmt.Errorf("set tenant: %w", err)}return fn(tx)})}
The true in set_config is the whole safety of this. Without it, the setting lives on the connection, and the next request to borrow that connection from the pool inherits a tenant it was not given. That is a worse bug than the one RLS was added to prevent, because it is intermittent.
- Background jobs have no request and therefore no tenant. Each must set one explicitly, and a job that iterates all tenants must set it per iteration.
- Migrations run as a role that bypasses RLS, which is correct and means migrations are a path where the protection is off.
- A connection pooler in transaction mode is compatible with
set_config(..., true)and is not compatible with a session-level setting. Know which mode yours uses.
Background work, where the tenant is lost
Every tenant bug that is not a missing WHERE clause is in a job. There is no request, so there is no ambient tenant, and nothing complains about its absence.
// The payload carries the tenant, always, even when it seems obvious.type SendInvoiceReminderPayload struct {OrganisationID uint `json:"organisation_id"`InvoiceID uint `json:"invoice_id"`}func HandleSendReminder(ctx context.Context, t *asynq.Task) error {var p SendInvoiceReminderPayloadif err := json.Unmarshal(t.Payload(), &p); err != nil {return fmt.Errorf("unmarshal: %v: %w", err, asynq.SkipRetry)}// Scoped, exactly as a request would be. The job is not special.inv, err := repo.ByID(ctx, p.OrganisationID, p.InvoiceID)...}
For the job that genuinely must span tenants, a nightly digest for each, make the iteration explicit and scope each pass:
// One pass per tenant, each fully scoped, so the "all tenants" case is// a loop over single-tenant work rather than an unscoped query.return tenant.ForEach(ctx, db, func(ctx context.Context, orgID uint) error {return digest.SendFor(ctx, orgID)})
The test that proves it
This is the part most teams skip, and it is the only evidence that any of the above works. Not a unit test of the repository: an end-to-end test through the real router with two real tenants.
// Every tenant-owned route, exercised by the wrong tenant. Adding a// resource means adding a line here, and the list is short enough that// a reviewer notices when one is missing.func TestNoRouteLeaksAcrossTenants(t *testing.T) {a, b := seedOrg(t, "alpha"), seedOrg(t, "beta")owned := seedEverything(t, b) // one row of each resource, for betafor _, r := range []struct{ method, path string }{{"GET", "/api/invoices/" + owned.Invoice},{"PATCH", "/api/invoices/" + owned.Invoice},{"DELETE", "/api/invoices/" + owned.Invoice},{"GET", "/api/customers/" + owned.Customer},{"POST", "/api/invoices/bulk-edit"}, // the bulk paths too{"GET", "/api/invoices/export"}, // and the export} {t.Run(r.method+" "+r.path, func(t *testing.T) {res := request(t, r.method, r.path, as(a.Owner), body(owned.IDs()))require.NotEqual(t, http.StatusOK, res.Code)require.NotContains(t, res.Body.String(), owned.Secret)})}}
Make it fail on purpose, once
Remove the scope from one repository method and confirm the test goes red. An isolation test is unusually prone to passing for the wrong reason, because an empty result and a correctly-denied result are indistinguishable from outside. A test you have never seen fail is a test you have not verified.
One tenant affecting the others
Isolation of data is not isolation of resources. In a shared database, one customer importing two million rows degrades everybody.
- Rate limit per tenant, not only per IP. A single tenant with an enthusiastic integration is the realistic source of load.
- Cap page sizes and export sizes. An unbounded export is a denial-of-service endpoint with a friendly name.
- Separate queues by weight. A tenant's bulk import must not sit in front of everybody's password resets.
- Set a statement timeout. One pathological query should be killed, not allowed to hold a connection for ten minutes.
- Record usage per tenant from day one. You cannot attribute load retroactively, and the first time you need to, you will need it urgently.
As generated code
$grit plugin add multitenant$grit generate resource Invoice --fields "number:string,total:money" --owned-by organisation
That adds the organisation model, membership with roles, an invitation flow and the organisation switcher, and makes the scope part of every generated query: the list, the detail, the bulk actions, the export and the admin.
grit doctor checks for the specific faults this page describes: a tenant-owned model with no index on the tenant column, a unique constraint that is not composite with it, and a route that touches a tenant table without the scope.
The multitenancy reference covers the generated model and the switcher.
Frequently asked questions
- Which model should I start with?
- Row-level, with the tenant column on every table from the first migration, unless a contract or a regulator forces otherwise. It is the cheapest to operate and the easiest to migrate away from, because the data is already labelled by tenant. Starting with schema-per-tenant to feel safer usually buys a migration problem instead of a security improvement.
- Can I move from row-level to database-per-tenant later?
- Yes, and that is a point in row-level's favour: every row already knows its tenant, so extracting one is a filtered copy. The work is in the application, not the data: connection resolution, migrations across many targets, and anything that assumed one database. Doable for a handful of large customers, which is the usual reason.
- Is row-level isolation acceptable for SOC 2 or ISO 27001?
- Yes. Neither requires physical separation. What they require is that you can describe the control, show it is applied consistently and demonstrate it is tested. An isolation test suite and an audit log are worth considerably more in that conversation than an architecture diagram with separate boxes.
- How do I let an admin see across tenants without weakening everything?
- A separate path, named as such, that logs every access. Not a flag that disables the scope on the normal path, because a flag that can be true is a flag that will be true on a request where it should not be. If the admin console is a different application, it can use a different repository with different methods, which makes the distinction structural rather than conditional.
Next steps
- Policies and row rulesThe status code question, and the write paths that get built without the scope.
- Build a SaaS MVP in a weekendWhy this decision belongs on Saturday morning rather than in March.
- Multitenancy referenceOrganisations, membership, invitations and the generated scoping.
- Zero-downtime migrationsHarder with a schema per tenant, which is part of the decision.
