Per-tenant transactions in Go with pgx: SET LOCAL search_path, and why every schema name goes through QuoteIdent

This is the third post in a series where I build a multi-tenant API gateway in Go out in the open. The first two covered schema-per-tenant migrations and Redis rate limiting. This one is about the smallest, sharpest piece of the whole thing: one function that every tenant-scoped query runs through, and the two-line allowlist that keeps a schema name from turning into SQL injection.

Here is the shape of it. Each tenant gets its own Postgres schema. A request comes in, I resolve which tenant it belongs to, and then all its queries run inside a transaction whose search_path is pinned to that one schema. The queries themselves use plain unqualified table names, SELECT ... FROM notes, and Postgres resolves notes in the pinned schema. That's the whole trick.

The one primitive

func WithTenantTx(ctx context.Context, pool *pgxpool.Pool, schema string, fn func(ctx context.Context, tx pgx.Tx) error) error {
    quoted, err := QuoteIdent(schema)
    if err != nil {
        return fmt.Errorf("tenant schema: %w", err)
    }

    tx, err := pool.Begin(ctx)
    if err != nil {
        return fmt.Errorf("begin tenant tx: %w", err)
    }
    // Rollback after a successful commit is a harmless no-op; this defer is
    // what guarantees the tx never outlives an error path.
    defer func() { _ = tx.Rollback(ctx) }()

    if _, err := tx.Exec(ctx, "SET LOCAL search_path TO "+quoted); err != nil {
        return fmt.Errorf("pin search_path to %s: %w", schema, err)
    }
    if err := fn(ctx, tx); err != nil {
        return err
    }
    if err := tx.Commit(ctx); err != nil {
        return fmt.Errorf("commit tenant tx: %w", err)
    }
    return nil
}

The store code on top of this stays boring, which is the point:

func (s *PGStore) Create(ctx context.Context, schema, userID, text string) (Note, error) {
    n := Note{UserID: userID, Text: text}
    err := db.WithTenantTx(ctx, s.pool, schema, func(ctx context.Context, tx pgx.Tx) error {
        return tx.QueryRow(ctx,
            "INSERT INTO notes (user_id, text) VALUES ($1, $2) RETURNING id, created_at",
            userID, text).Scan(&n.ID, &n.CreatedAt)
    })
    ...
}

No schema qualifier anywhere in the SQL. The tenant boundary lives entirely in the pinned search_path, so I write the query once and it runs correctly for any tenant.

Why SET LOCAL and not SET

This is the part I'd flag in review if I saw a plain SET there. A pgx pool hands out pooled connections. When a request finishes, its connection goes back to the pool and gets reused by the next request, which is very often a different tenant.

SET search_path is session-level. It sticks to the connection. If I set it that way, the setting rides along when the connection is reused, and the next tenant's queries resolve against the previous tenant's schema. In a multi-tenant gateway that is not a bug, it's a data breach: you're reading another tenant's rows.

SET LOCAL scopes the setting to the current transaction only. At commit or rollback it's gone. So when the connection returns to the pool, no search_path travels with it. The whole design leans on that one word. Here's how I wrote it down in the package doc so the next person doesn't 'clean it up':

// The pinning uses SET LOCAL inside an explicit transaction. SET LOCAL scopes
// the setting to that transaction only, so when the pooled connection is
// released and reused for another tenant, no search_path travels with it. A
// session-level SET would stick to the connection and leak across requests,
// which in a multi-tenant gateway means reading another tenant's rows.

One consequence worth knowing: SET LOCAL outside a transaction is a silent no-op, with a warning you'll never see in prod. That's why the SET LOCAL runs strictly between Begin and fn, never on a bare pool connection.

You cannot bind a schema name

Here's the thing that trips people up. You can bind values as parameters, $1, $2, and pgx sends them separately from the query text, so injection through a value is a non-issue. But an identifier, a table or schema name, is not a value. There is no SET LOCAL search_path TO $1. The name has to be part of the query text.

So the schema name goes into the SQL by string concatenation. "SET LOCAL search_path TO " + quoted. String concatenation into SQL is exactly the sentence that should make you nervous, which is why nothing reaches that concatenation without passing an allowlist first:

var identPattern = regexp.MustCompile(`^[a-z_][a-z0-9_]*$`)

func QuoteIdent(name string) (string, error) {
    if name == "" {
        return "", fmt.Errorf("identifier is empty")
    }
    if len(name) > 63 {
        return "", fmt.Errorf("identifier %q exceeds 63 bytes", name)
    }
    if !identPattern.MatchString(name) {
        return "", fmt.Errorf("identifier %q contains characters outside [a-z0-9_]", name)
    }
    return `"` + name + `"`, nil
}

The pattern is deliberately narrower than what Postgres actually allows for identifiers. Lowercase ASCII letters, digits, underscores, starting with a letter or underscore. No uppercase, no unicode, no dots. My tenant schema names never need any of that, so anything fancier is a signal that something is wrong, and I'd rather reject it than quote it and hope.

The double-quoting on the way out matters too. Quoting alone doesn't save you, because a " inside the name would close the quote early. The allowlist is what makes the quote safe: by the time I wrap it, I already know there are no quotes to worry about. Belt and suspenders, and the test table spells out exactly what the suspenders are for:

{"embedded double quote", `tenant_"acme`},
{"quote injection", `x"; drop schema public;--`},
{"semicolon", "tenant;acme"},
{"dot qualified", "public.notes"},
{"64 bytes", strings.Repeat("a", 64)},

Every one of those returns an error, not a quoted string.

The registry is the only source of schema names

The allowlist is a second line of defense, not the first. The first is that a schema name never comes from a request in the first place. The request header names a tenant, and the tenant middleware resolves that to a schema through the registry, which reads it from a public.tenants table:

err = r.q.QueryRow(ctx, "SELECT schema FROM public.tenants WHERE id = $1", id).Scan(&schema)

So the value that reaches WithTenantTx was written by the migration path, not typed by a caller. An unregistered tenant is rejected with a 404 before any SQL runs. QuoteIdent still runs on every call anyway, because defense in depth is cheap and the day someone adds a new code path that skips the registry, I want the allowlist standing there.

The sharp edges

Two things I actually worried about.

First, the empty-search_path footgun. I pin the path to the tenant schema and nothing else, no public fallback. That's on purpose: a query that accidentally hits a table that only exists in public fails loudly instead of quietly reading shared data. The cost is that anything you'd normally reach through public, an extension function, a shared type, now has to be schema-qualified explicitly. If you're used to public always being on the path, this will surprise you the first time a function call can't be found.

Second, prepared statements and plan caching. pgx caches prepared statements per connection, and Postgres resolves an unqualified name in a plan using the search_path that was in effect when the plan was built. That combination is where a subtle cross-tenant bug would hide if the pinning were sloppy. What keeps it honest here is that the SET LOCAL and the queries run in the same transaction on the same connection, so the pinned path is always in effect when the query is planned and executed. I didn't want to trust my reasoning on that, so there's a test that hammers the pool from 32 goroutines across two schemas and checks the path both inside and outside every transaction:

if normalizeSearchPath(inside) != schema {
    t.Errorf("inside tx: search_path = %q, want pinned %q", inside, schema)
    return
}
...
got := normalizeSearchPath(outside)
for _, s := range schemas {
    if got == s {
        t.Errorf("after tx: search_path %q leaked out of the transaction", outside)
        return
    }
}

It runs under -race in CI. If SET LOCAL ever quietly became SET, this is the test that goes red.

The code is all in internal/db of the gateway repo if you want to read the rest. Next post in the series is the middleware chain that sits in front of all this. Questions and holes-in-my-reasoning welcome in the comments.

Comments

[Mads Hansen]

The SET LOCAL distinction is the part that should be in every code review checklist for schema-per-tenant designs.

Connection pools make session state dangerous because the bug does not look like a bug locally. Everything works until one request borrows a connection with yesterday’s tenant context still attached. Then the boundary fails in the worst possible way: quietly.

A few things I like in this pattern:

• tenant scope is pinned inside an explicit transaction
• schema names come from a registry, not the request
• identifiers pass a deliberately narrow allowlist
• the store layer stays boring
• rollback is guaranteed on error paths

The “boring store code” point is underrated. If every query has to remember tenant mechanics, someone will eventually forget. Putting the tenant boundary in one primitive makes the safe path the normal path.

Also +1 on rejecting more than Postgres technically requires. If tenant schema names only need lowercase ASCII and underscores, accepting anything broader is just buying future incident surface.