GORM Multi Tenant Patterns With Scopes and Schemas

A multi tenant application serves many customers from one codebase, and the one rule that matters is that customer A must never see customer B's data. The bug that breaks it is rarely clever: it is a query written in a hurry without the tenant filter. This article builds four isolation strategies with GORM on PostgreSQL 18.6, then runs the same hostile mistake against each (a query that forgets the tenant) to see which ones fail open and which fail closed. It is the most useful way to choose, because every strategy works when the code is right.

It builds on scopes and the hooks and callbacks article. The general design discussion is in multi tenant databases in Go and row level security in PostgreSQL, and the Ent version of this problem is Ent multi tenancy.

The model and the lab

Every strategy uses a Note with a TenantID. The program seeds two notes for tenant 1 and one for tenant 2, then walks through four sections:

package main

import (
	"context"
	"fmt"
	"gorm.io/gorm/clause"
	"log"
	"strings"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type Note struct {
	ID       uint
	TenantID int `gorm:"index"`
	Body     string
}

type tenantKey struct{}

func withTenant(ctx context.Context, id int) context.Context {
	return context.WithValue(ctx, tenantKey{}, id)
}

// Pattern 1: an explicit scope. Safe only if every query remembers to use it.
func Tenant(id int) func(*gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB { return db.Where("tenant_id = ?", id) }
}

// Pattern 2: callbacks add the filter for every query, update and delete that carries a tenant in its context.
func registerTenantCallbacks(db *gorm.DB) {
	add := func(tx *gorm.DB) {
		if id, ok := tx.Statement.Context.Value(tenantKey{}).(int); ok && tx.Statement.Schema != nil {
			if tx.Statement.Schema.LookUpField("tenant_id") != nil {
				tx.Statement.AddClause(clause.Where{Exprs: []clause.Expression{
					clause.Eq{Column: clause.Column{Table: clause.CurrentTable, Name: "tenant_id"}, Value: id},
				}})
			}
		}
	}
	db.Callback().Query().Before("gorm:query").Register("tenant:query", add)
	db.Callback().Update().Before("gorm:update").Register("tenant:update", add)
	db.Callback().Delete().Before("gorm:delete").Register("tenant:delete", add)
}

func bodies(ns []Note) string {
	var out []string
	for _, n := range ns {
		out = append(out, fmt.Sprintf("%d:%s", n.TenantID, n.Body))
	}
	return "[" + strings.Join(out, " ") + "]"
}

func main() {
	db := connect("l0732", nil)
	must(db.AutoMigrate(&Note{}))
	db.Create(&[]Note{{TenantID: 1, Body: "a1"}, {TenantID: 1, Body: "a2"}, {TenantID: 2, Body: "b1"}})
	ctx := context.Background()

	fmt.Println("== 1. explicit scope")
	var ns []Note
	db.Scopes(Tenant(1)).Find(&ns)
	fmt.Println("tenant 1 sees:", bodies(ns))
	db.Find(&ns)
	fmt.Println("a query that forgot the scope sees:", bodies(ns))

	fmt.Println("== 2. callbacks fed by the context")
	registerTenantCallbacks(db)
	db.WithContext(withTenant(ctx, 2)).Find(&ns)
	fmt.Println("tenant 2 sees:", bodies(ns))
	res := db.WithContext(withTenant(ctx, 2)).Where("body = ?", "a1").Delete(&Note{})
	fmt.Println("tenant 2 deleting a tenant 1 note, rows:", res.RowsAffected)
	res = db.WithContext(withTenant(ctx, 1)).Model(&Note{}).Where("body = ?", "a1").Update("body", "a1-edited")
	fmt.Println("tenant 1 editing its own note, rows:", res.RowsAffected)
	db.Raw("select * from notes order by id").Scan(&ns)
	fmt.Println("raw SQL ignores the callbacks:", bodies(ns))
	db.Find(&ns)
	fmt.Println("no tenant in the context means no filter at all:", len(ns), "rows")

	fmt.Println("== 3. schema per tenant")
	for _, t := range []string{"t1", "t2"} {
		db.Exec("drop schema if exists " + t + " cascade")
		db.Exec("create schema " + t)
		db.Exec("create table " + t + ".notes (id bigserial primary key, tenant_id bigint, body text)")
	}
	db.Exec("insert into t1.notes (tenant_id, body) values (1, 'x1'), (1, 'x2')")
	db.Exec("insert into t2.notes (tenant_id, body) values (2, 'y1')")
	for _, t := range []string{"t1", "t2"} {
		db.Transaction(func(tx *gorm.DB) error {
			tx.Exec("set local search_path to " + t)
			var got []Note
			tx.Find(&got)
			fmt.Println(t, "sees:", bodies(got))
			return nil
		})
	}

	fmt.Println("== 4. row level security, as a role that is not the owner")
	db.Exec(`do $$ begin if not exists (select from pg_roles where rolname = 'l0732_app') then
		create role l0732_app login password 'app'; end if; end $$`)
	db.Exec("alter table notes enable row level security")
	db.Exec("alter table notes force row level security")
	db.Exec("create policy isolation on notes using (tenant_id = nullif(current_setting('app.tenant', true), '')::int) with check (tenant_id = nullif(current_setting('app.tenant', true), '')::int)")
	db.Exec("grant usage on schema l0732 to l0732_app")
	db.Exec("grant select, insert, update, delete on notes to l0732_app")
	db.Exec("grant usage on all sequences in schema l0732 to l0732_app")

	base := "host=127.0.0.1 port=55432 user=l0732_app password=app dbname=lab sslmode=disable search_path=l0732"
	app, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)

	var seen []Note
	app.Find(&seen)
	fmt.Println("no tenant set, query with no filter:", len(seen), "rows (fails closed)")
	for _, id := range []string{"1", "2"} {
		app.Transaction(func(tx *gorm.DB) error {
			tx.Exec("select set_config('app.tenant', ?, true)", id)
			var got []Note
			tx.Find(&got)
			fmt.Println("tenant", id, "with an unfiltered Find sees:", bodies(got))
			return nil
		})
	}
	err = app.Transaction(func(tx *gorm.DB) error {
		tx.Exec("select set_config('app.tenant', '1', true)")
		return tx.Create(&Note{TenantID: 2, Body: "smuggled"}).Error
	})
	fmt.Println("tenant 1 inserting a tenant 2 row:", strings.Split(fmt.Sprint(err), " (")[0])
}

== 1. explicit scope
tenant 1 sees: [1:a1 1:a2]
a query that forgot the scope sees: [1:a1 1:a2 2:b1]
== 2. callbacks fed by the context
tenant 2 sees: [2:b1]
tenant 2 deleting a tenant 1 note, rows: 0
tenant 1 editing its own note, rows: 1
raw SQL ignores the callbacks: [1:a1-edited 1:a2 2:b1]
no tenant in the context means no filter at all: 3 rows
== 3. schema per tenant
t1 sees: [1:x1 1:x2]
t2 sees: [2:y1]
== 4. row level security, as a role that is not the owner
no tenant set, query with no filter: 0 rows (fails closed)
tenant 1 with an unfiltered Find sees: [1:a1-edited 1:a2]
tenant 2 with an unfiltered Find sees: [2:b1]
tenant 1 inserting a tenant 2 row: ERROR: new row violates row-level security policy for table "notes"

1. An explicit scope

The simplest design is Tenant(id), a scope that adds WHERE tenant_id = ?, used on every query. It is transparent and easy to test. The lab shows the weakness in two lines. db.Scopes(Tenant(1)).Find(&ns) returned tenant 1's two notes. The next line queried without the scope and returned all three: a query that forgot the scope sees: [1:a1 1:a2 2:b1].

That is the failure mode of every approach that depends on a developer remembering a filter: it fails open. One missing scope in a reporting query, a background job or an admin screen is a data leak, and code review catches it only when a reviewer is looking for it. A scope is fine as a convenience on top of a stronger mechanism. As the only protection, it is the weakest.

2. Callbacks driven by the context

To stop depending on memory, put the filter where every query passes through. GORM's callbacks run for each operation, and the lab registers three that read the tenant id from the request context and add a tenant_id condition: one for queries, one for updates, one for deletes. A request middleware puts the tenant into the context (withTenant(ctx, id)), and handlers pass db.WithContext(ctx) as always.

It worked for the cases it was written for:

• Tenant 2's Find returned only [2:b1].
• Tenant 2 deleting a1, a tenant 1 note, changed 0 rows: the callback added tenant_id = 2 to the delete, so the row did not match.
• Tenant 1 editing its own note changed 1 row.

The callback only adds the condition for models that have a tenant_id field (the lab checks Schema.LookUpField), so shared reference tables are left alone. Three holes remain, and the lab runs each:

1. Raw SQL ignores callbacks. db.Raw("select * from notes") returned every tenant's notes: [1:a1-edited 1:a2 2:b1]. GORM does not parse raw statements, so it cannot add a filter.
2. A missing tenant means no filter. A Find with a plain context returned 3 rows: no tenant in the context means no filter at all. The callback does nothing when the context has no tenant, which is the fail open case again. You can flip it: make the callback add AddError(ErrNoTenant) when a tenant owned model is queried without a tenant, so the mistake becomes a loud failure. That needs a way for system jobs to opt out, such as an explicit AllTenants(ctx) marker.
3. Joins and subqueries. The condition is added to the main table. A join to another tenant owned table is a separate table in the statement, and the callback does not rewrite it.

Callbacks are a good second layer: they fix the forgotten scope in ordinary code. They are not a guarantee, and the failure is silent unless you make it fail closed.

3. Schema per tenant

Each tenant gets its own schema holding its own copy of the tables. The application sets search_path for the duration of a transaction, and unqualified table names then resolve to that tenant's tables. The lab created schemas t1 and t2 with the same DDL, loaded data, and ran set local search_path to t1 inside a transaction. Tenant 1 saw [1:x1 1:x2] and tenant 2 saw [2:y1], from the same Find.

SET LOCAL matters. Plain SET changes the connection and leaks to the next request that borrows it from the pool. SET LOCAL lasts until the transaction ends. This also means every tenant request must run in a transaction.

The strength is that a forgotten filter cannot cross tenants: the table the query reads belongs to one tenant. Backups, restores and deletion per tenant are easy too (drop the schema). The costs are real:

• Migrations multiply. Every schema change runs once per tenant, and a failure halfway leaves tenants on different versions. A hundred tenants is a hundred migrations, a hundred thousand is a different system.
• Catalog size. PostgreSQL slows down with tens of thousands of tables.
• Identical DDL is required, and the error is odd. My first version of this lab created the tenant tables with id serial while GORM's own table used bigserial. The tenant query then failed with ERROR: cached plan must not change result type (SQLSTATE 0A000). The pgx driver caches prepared statements by SQL text per connection. The same text SELECT * FROM "notes" resolved to tables with different column types in two schemas, and PostgreSQL refused to reuse the plan. With identical DDL it works, and the cache is not a problem. If schemas can drift, either keep them aligned with a check in CI or turn the statement cache off (default_query_exec_mode=exec or simple_protocol) at a cost in speed.
• Connection poolers. Behind PgBouncer in transaction mode, SET LOCAL is safe, SET is not.

4. Row level security

Row level security (RLS) moves the filter into the database. A policy states which rows a role may see and write, and PostgreSQL applies it to every statement, including raw SQL, joins and statements issued by a different program. The lab enables RLS on notes, creates a policy that compares tenant_id to a session setting, and connects as a separate role l0732_app:

• The tenant comes from current_setting('app.tenant', true), set per transaction with set_config('app.tenant', '1', true). The true makes it local to the transaction, like SET LOCAL.
• nullif(..., '')::int turns a missing setting into NULL, and a comparison with NULL matches nothing.

The results are the opposite of the first two strategies:

• With no tenant set, an unfiltered Find returned 0 rows: no tenant set, query with no filter: 0 rows (fails closed).
• Inside a transaction with the tenant set to 1, an unfiltered Find returned [1:a1-edited 1:a2], and with 2 it returned [2:b1]. The code had no Where at all.
• Tenant 1 inserting a row with tenant_id 2 failed: ERROR: new row violates row-level security policy for table "notes". The with check half of the policy covers writes.

That is fail closed: a forgotten filter returns nothing, not everything. RLS is the strongest option here for shared tables, and it has conditions:

• The role must not be a superuser or the table owner. Superusers and roles with BYPASSRLS skip policies, and owners skip them unless the table has FORCE ROW LEVEL SECURITY. The lab uses the postgres superuser to create the data and a second role to query it; run the lab's policy as postgres and you would see all 3 rows. Connect the application as a limited role and keep the migration role separate.
• Every request must set the tenant inside a transaction. Wrap it in a helper: begin, set_config, run the work, commit. A pool connection that skips the helper has no tenant, and sees nothing.
• Policies cost planner work and need indexes. Index tenant_id and put it first in composite indexes.
• The policy expression is trusted code. Test it as you test queries.

Getting the tenant into the request

Every strategy needs the tenant id, and where it comes from is a security decision. Derive it from something the client cannot choose freely: the verified claims of the access token, or the host name matched against a table of tenants. Never read it from a query parameter, a form field or a JSON body, because a client that can set its own tenant id can read anyone's data. Resolve it once in middleware, put it in the request context with a private key type, and have the store layer read it from there and nowhere else.

For RLS the store needs a helper that opens the transaction and sets the tenant before any work runs:

func (s *Store) InTenant(ctx context.Context, tenant int, fn func(tx *gorm.DB) error) error {
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Exec("select set_config('app.tenant', ?, true)", strconv.Itoa(tenant)).Error; err != nil {
			return err
		}
		return fn(tx)
	})
}

Every store method goes through InTenant, so the transaction, the setting and the cleanup are one code path. A linter rule or a test that scans for s.db. outside the helper keeps people honest.

The details that bite later

Uniqueness is per tenant. A unique index on email alone makes tenants fight over addresses. Make it unique (tenant_id, email), with the tenant first so the index also serves tenant scoped lookups. The same goes for slugs, SKUs and invoice numbers.

Ids still need a scope. A request for /notes/42 that loads by primary key returns the note whichever tenant owns it, unless the filter applies. Look up by (tenant_id, id), and return 404, not 403, for another tenant's id, so the response does not confirm that the id exists.

Background jobs have no request. A job that processes every tenant's rows needs an explicit way to say so. Make it a named code path (AllTenants) that connects with a different role, so ordinary request code cannot reach it by accident. In RLS terms that role has a policy allowing all rows, or BYPASSRLS, and its credentials never go to the web processes.

Caches and search indexes need the tenant in the key. A cache entry keyed by note id alone serves tenant A's note to tenant B. Prefix every cache key and every search document with the tenant id.

Logs and metrics. Add the tenant id to log fields so an incident can be scoped, and keep customer data out of them.

Choosing

A sound default for a SaaS with many small tenants: a tenant_id column, a scope or callback for convenience, and RLS as the guarantee. For a few large tenants with strict isolation or per tenant restore needs, schema per tenant (or a database per tenant) fits better. Do not rely on the application filter alone for anything that would be a breach if it leaked.

Test the isolation itself

Write the test that matters: create data for two tenants, run every repository method as tenant A, and assert nothing from tenant B appears, including through joins and counts. Add a test that runs a deliberately unfiltered query and asserts that it returns nothing (RLS) or is rejected (a fail closed callback). Run these as the application's database role and not as a superuser, or the RLS tests pass for the wrong reason. The schema per test technique in GORM testing makes this cheap.

Checklist

• Treat a scope as convenience, not security.
• If you use callbacks, make a missing tenant an error, and remember raw SQL and joins escape them.
• With schema per tenant, use SET LOCAL, keep DDL identical, and plan the migration fan out.
• With RLS, connect as a non owner role, set the tenant per transaction, and test the policy.
• Test cross tenant access explicitly, as the application's real role.

The mechanics are documented in the PostgreSQL row security policies chapter, and the callback API in the GORM callbacks documentation.