Multi Tenant APIs in Go With Isolation and Routing

A multi-tenant API serves many customers from one deployment, and none of them may see another's data. That last clause is the whole difficulty. Authentication tells you who the caller is; tenancy decides which slice of the world they are allowed to see, and the mistake that ends careers is a single query that forgot the slice.

This article builds the isolation in layers, from the request to the database, and runs the cases that go wrong. It also compares the three ways to lay out tenant data, since that choice shapes everything else. It builds on REST API design and on the object-level authorisation problem in BOLA and IDOR, which is what a tenancy bug is: an IDOR where the object belongs to another customer. The code uses SQLite through modernc.org/sqlite so it runs anywhere, and ran on Go 1.25.6.

Choosing a data layout first

There are three standard layouts. Everything else, including how you route and how you migrate, follows from the choice.

A practical rule: start with shared tables for a product with many small tenants, move to database per tenant for a few large ones or where contracts demand it, and avoid schema per tenant unless you have measured a reason, since it combines the migration cost of the third with the shared failure domain of the first. Some systems mix: shared tables by default, a dedicated database for the enterprise tier, with the tenant lookup choosing which pool to use.

The lab uses shared tables, because that is where most of the risk sits. The same layers apply to the other layouts, with the pool choice replacing the WHERE.

Layer 1: resolve the tenant once

The tenant has to come from something the client cannot freely change. Reasonable sources:

• The host name: acme.example.test. Easy for users to understand, works for browsers, and the TLS certificate or wildcard covers it.
• A claim in the verified token: a tenant claim inside a signed JWT. Safe because the signature covers it, and the right choice for APIs called by programs.
• An API key that belongs to exactly one tenant, looked up in the database.

The source that looks convenient and is dangerous is a plain request header such as X-Tenant: acme supplied by the caller, with no check that this user belongs to that tenant. If you accept a tenant from the client, also verify membership against the authenticated identity on every request.

The middleware in the lab maps the host to a tenant name, checks it against the known list and stores it in the request context. Names not on the list never reach a handler.

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"net/http"
	"net/http/httptest"
	"strconv"
	"strings"

	_ "modernc.org/sqlite"
)

type ctxKey struct{}

// tenantFrom resolves the tenant from the host name: acme.example.test belongs to tenant "acme".
// The tenant list is the allow-list; an unknown name never reaches a handler.
func tenantMiddleware(known map[string]bool, next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		name, _, _ := strings.Cut(r.Host, ".")
		if !known[name] {
			http.Error(w, `{"error":"unknown tenant"}`, http.StatusNotFound)
			return
		}
		next.ServeHTTP(w, r.WithContext(context.WithValue(r.Context(), ctxKey{}, name)))
	})
}

func tenant(ctx context.Context) string { return ctx.Value(ctxKey{}).(string) }

type Project struct {
	ID   int    `json:"id"`
	Name string `json:"name"`
}

type Store struct{ db *sql.DB }

// Every method takes the context and reads the tenant from it. There is no method that does not.
func (s *Store) Project(ctx context.Context, id int) (Project, error) {
	var p Project
	err := s.db.QueryRowContext(ctx, `SELECT id, name FROM projects WHERE tenant_id = ? AND id = ?`, tenant(ctx), id).Scan(&p.ID, &p.Name)
	return p, err
}

func (s *Store) Projects(ctx context.Context) ([]Project, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, name FROM projects WHERE tenant_id = ? ORDER BY id`, tenant(ctx))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Project
	for rows.Next() {
		var p Project
		rows.Scan(&p.ID, &p.Name)
		out = append(out, p)
	}
	return out, rows.Err()
}

func (s *Store) AddTask(ctx context.Context, projectID int, title string) error {
	_, err := s.db.ExecContext(ctx, `INSERT INTO tasks (tenant_id, project_id, title) VALUES (?, ?, ?)`, tenant(ctx), projectID, title)
	return err
}

// leakyProject is the bug this design exists to prevent: the tenant is forgotten.
func (s *Store) leakyProject(ctx context.Context, id int) (Project, error) {
	var p Project
	err := s.db.QueryRowContext(ctx, `SELECT id, name FROM projects WHERE id = ?`, id).Scan(&p.ID, &p.Name)
	return p, err
}

func main() {
	db, err := sql.Open("sqlite", "file:tenants?mode=memory&cache=shared&_pragma=foreign_keys(1)")
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	for _, q := range []string{
		`CREATE TABLE projects (tenant_id TEXT NOT NULL, id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY (tenant_id, id))`,
		// The composite foreign key means a task cannot point at another tenant's project, even if application code tries.
		`CREATE TABLE tasks (tenant_id TEXT NOT NULL, id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, title TEXT NOT NULL,
			FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id))`,
		`INSERT INTO projects VALUES ('acme', 1, 'Website'), ('acme', 2, 'Billing'), ('globex', 3, 'Reactor')`,
	} {
		if _, err := db.Exec(q); err != nil {
			panic(err)
		}
	}
	store := &Store{db}

	mux := http.NewServeMux()
	mux.HandleFunc("GET /projects", func(w http.ResponseWriter, r *http.Request) {
		ps, _ := store.Projects(r.Context())
		json.NewEncoder(w).Encode(ps)
	})
	mux.HandleFunc("GET /projects/{id}", func(w http.ResponseWriter, r *http.Request) {
		id, _ := strconv.Atoi(r.PathValue("id"))
		p, err := store.Project(r.Context(), id)
		if err == sql.ErrNoRows { // not "forbidden": a tenant must not learn that another tenant's id exists
			http.Error(w, `{"error":"not found"}`, http.StatusNotFound)
			return
		}
		json.NewEncoder(w).Encode(p)
	})
	mux.HandleFunc("GET /leaky/projects/{id}", func(w http.ResponseWriter, r *http.Request) {
		id, _ := strconv.Atoi(r.PathValue("id"))
		p, _ := store.leakyProject(r.Context(), id)
		json.NewEncoder(w).Encode(p)
	})
	h := tenantMiddleware(map[string]bool{"acme": true, "globex": true}, mux)

	get := func(host, path string) {
		rec := httptest.NewRecorder()
		req := httptest.NewRequest(http.MethodGet, path, nil)
		req.Host = host
		h.ServeHTTP(rec, req)
		fmt.Printf("%-24s %-22s %d %s", host, path, rec.Code, rec.Body.String())
	}
	get("acme.example.test", "/projects")
	get("globex.example.test", "/projects")
	get("globex.example.test", "/projects/1")
	get("globex.example.test", "/projects/3")
	get("evil.example.test", "/projects")
	get("globex.example.test", "/leaky/projects/1")

	ctxAcme := context.WithValue(context.Background(), ctxKey{}, "acme")
	ctxGlobex := context.WithValue(context.Background(), ctxKey{}, "globex")
	fmt.Println("acme adds a task to its own project:  ", store.AddTask(ctxAcme, 1, "Fix footer"))
	fmt.Println("globex adds a task to acme's project: ", store.AddTask(ctxGlobex, 1, "Sneak in"))
}

acme.example.test        /projects              200 [{"id":1,"name":"Website"},{"id":2,"name":"Billing"}]
globex.example.test      /projects              200 [{"id":3,"name":"Reactor"}]
globex.example.test      /projects/1            404 {"error":"not found"}
globex.example.test      /projects/3            200 {"id":3,"name":"Reactor"}
evil.example.test        /projects              404 {"error":"unknown tenant"}
globex.example.test      /leaky/projects/1      200 {"id":1,"name":"Website"}
acme adds a task to its own project:   <nil>
globex adds a task to acme's project:  constraint failed: FOREIGN KEY constraint failed (787)

Read the program from the top. tenantMiddleware is the only place that decides who the tenant is, and tenant(ctx) is the only way the rest of the code asks. That asymmetry is deliberate: if resolving happens in one function, auditing it is one function's worth of reading.

Layer 2: make forgetting impossible

The Store type has no method that takes a tenant as an argument. Each one reads it from the context and puts it in the WHERE. A developer adding a new query copies an existing one and inherits the filter. The shape of the API nudges toward the safe path, which beats a code review checklist.

The same idea in other designs: a repository constructed per request with the tenant fixed (store.For(tenantID)), a database handle wrapper that rewrites queries, or an ORM scope applied by default. Whichever you pick, test that it cannot be skipped. GORM and similar libraries make it easy to bypass a global scope with one call, and a reviewer will not always notice.

Layer 3: let the database refuse

Application filters can have bugs. Constraints cannot be forgotten. The schema in the lab makes (tenant_id, id) the primary key for projects and declares the tasks' foreign key over (tenant_id, project_id). A task can only reference a project in its own tenant, whatever the application code does.

The last line of the output shows it: the globex tenant tried to add a task to acme's project 1, and SQLite answered FOREIGN KEY constraint failed. That is a failure the attacker cannot get around with a cleverly shaped request, because the rule lives where the data does.

On PostgreSQL the next layer is row level security. You enable it on each table, write a policy that compares tenant_id to a setting such as current_setting('app.tenant'), and set that setting at the start of each transaction. The database then adds the predicate to every statement, including the ones you forgot. It is the closest thing to a safety net for shared tables, with two caveats: connection pools must set and reset the setting reliably (inside a transaction with SET LOCAL), and the application role must not be a superuser or the table owner, because those bypass policies. See the PostgreSQL row security documentation for the exact rules. I did not run Postgres in this lab, and I would test the policy with the same leak check described below before relying on it.

Reading the run

Acme listed its two projects and globex listed one. The global ids (1 and 2 for acme, 3 for globex) are chosen in the seed, and the primary key includes the tenant, so two tenants could also reuse the same number.

Globex asking for /projects/1, which belongs to acme, gets a 404 with the same body as an id that does not exist. This detail matters. A 403 would tell globex that project 1 exists, which is information it should not have. Use 404 for objects outside the tenant, so existence is not an oracle.

The host evil.example.test is not a known tenant, so the middleware answers 404 before routing.

The line that matters most is the last one before the task inserts: /leaky/projects/1. It uses the query that forgot the tenant, and globex receives acme's Website project with a 200. Every multi-tenant system contains a query like this somewhere in its history. The reason to keep it in the lab is to show the failure mode, and the reason to keep a version of it in your test suite is so the next one gets caught.

Test the leak, do not assume it

Write the test once and run it for every read endpoint:

1. Create data in tenant A and tenant B.
2. Authenticate as B.
3. Request each of A's object ids through every route that accepts an id, list routes included.
4. Assert 404 or an empty list.

Then add a second kind of test: a static check or a grep in CI for raw SQL touching tenant tables without tenant_id. It will be noisy and incomplete, and it catches lazy mistakes before review does. If you use sqlc, the query files are a convenient place to enforce that every query on a tenant table has the parameter.

Things that quietly cross tenants

Most leaks are not in the obvious SELECT.

Caches. A cache key of project:1 is shared by everyone. Include the tenant in every key: acme:project:1. See caching in Go APIs.

Background jobs. A job that processes "all pending exports" runs outside any request and has no tenant in its context. Store the tenant id in the job payload and rebuild the context before running.

Logs and metrics. Add the tenant to structured log fields so you can answer "which customer is this error for?" Be careful with metric labels: a label per tenant becomes high-cardinality at thousands of tenants.

Search indexes and object storage. Prefix object keys with the tenant (tenants/acme/ and then the file name) and filter search queries by tenant. A shared index with a forgotten filter leaks as easily as a table.

Uniqueness. A unique constraint on email or slug means one tenant's data blocks another's. Make uniqueness per tenant: UNIQUE (tenant_id, slug).

Sequences and IDs. Sequential global ids leak volume and invite guessing across tenants. Use random ids, as discussed in the IDOR article, in addition to scoping.

Noisy neighbours

Shared tables mean shared capacity. One tenant running a huge export can slow everyone. Limit by tenant, not just by IP: rate limits and quotas keyed on the tenant (API rate limiting and usage metering cover the mechanics), per-tenant concurrency caps on expensive endpoints, and query timeouts so one slow query cannot hold a connection forever. For the largest customers the answer may be moving them to their own database, which is a good reason to build the tenant lookup as a function that returns a pool, not a constant.

Routing to a pool per tenant

If you take the database-per-tenant route, or plan to for a few large customers, make the lookup explicit now. The store asks a registry for the pool that serves the tenant in the context:

type Registry struct {
	shared  *sql.DB
	mu      sync.RWMutex
	pools   map[string]*sql.DB // dedicated databases, opened on first use
	dsns    map[string]string  // tenant -> DSN for the tenants that have their own database
}

func (r *Registry) For(ctx context.Context) *sql.DB {
	name := tenant(ctx)
	r.mu.RLock()
	db, ok := r.pools[name]
	r.mu.RUnlock()
	if ok {
		return db
	}
	if _, dedicated := r.dsns[name]; dedicated {
		return r.open(name)
	}
	return r.shared // the default: shared tables, filtered by tenant_id
}

Two cautions apply. Cap the total number of open connections across all tenant pools, because a hundred pools of ten connections each will exceed what a database server accepts. And close pools for tenants that have been idle for a while. The shared path keeps the tenant_id filter even for dedicated tenants where it is redundant, so the code has a single set of queries.

Per tenant configuration

Tenants differ: a plan with limits, feature switches, a branding block, an allowed IP range, a custom domain. Keep these in a tenants table and load them once per request, or better, cache them for a short time keyed by tenant id and refresh on change. Put the loaded settings into the context beside the name so handlers read limits from one place.

Be wary of the shortcut of sprinkling if tenant == "acme" through handlers. It works for the first special case and becomes unmaintainable by the fifth. Turn each exception into a setting with a default, and the handler asks the setting.

Migrations and lifecycle

With shared tables, one migration serves everyone, which is the main operational benefit. With database per tenant, treat migrations as a rollout: run them tenant by tenant, record the version each is on, and design every migration to be backward compatible with the previous release of the code, since the fleet will be mixed for a while.

Plan onboarding and offboarding early. Creating a tenant should be one idempotent operation. Deleting one needs a defined answer: soft-delete with a grace period, then purge across tables, object storage, caches and search, with an audit record. Export on request is easy with a database per tenant and a query per table with shared tables. Check soft deletes and audit trails for patterns that fit.

What to carry away

There is no tenancy feature to switch on in Go; it is a set of habits enforced by structure. If a new engineer can add an endpoint by copying a neighbour and cannot accidentally skip the tenant, the design is working. If they have to remember a rule, it will eventually be forgotten, so spend the effort on making the rule impossible to break.

Keep three rules. Resolve the tenant in one place from a source the caller cannot choose. Make the safe path the only path in your data access code. Back it with constraints, and with row level security where your database supports it. Then test the failure on purpose, because the bug that matters is the one in the query nobody thought was about tenants. The OWASP API Security Top 10 lists broken object level authorisation as its first item, and a missing tenant filter is the most common way to earn it.