Senior · Architecture and systems

The SQL query is scoped but the cache is not

SQL reads include tenant_id, but Redis caches orders under order:<id>. Tenants may share an order ID. Is the boundary complete?

Try explaining this aloud before opening the answer. State your assumptions and trace a boundary case.

EXAMPLE · FRAGMENT UNLESS A COMPLETE PROGRAM IS SHOWN

package main

import "fmt"

type Key struct{ Tenant, Order string }

func main() {
	cache := map[Key]string{}
	cache[Key{"tenant-a", "42"}] = "Ada's order"
	cache[Key{"tenant-b", "42"}] = "Grace's order"
	fmt.Println(cache[Key{"tenant-a", "42"}])
	fmt.Println(cache[Key{"tenant-b", "42"}])
}

Reveal a strong answer

A request can be authenticated and still read another tenant’s object. Authentication proves a principal; authorization decides whether that principal may perform this operation on this resource. Tenant isolation fails when one endpoint is careful but a cache key, bulk export or queued job forgets the same boundary. Treat tenant identity as part of the operation contract.
Use tenant and resource together in query predicates and uniqueness constraints. A background job records a verified tenant scope and rechecks the relevant permission where policy requires it. Include tenant scope in cache and idempotency keys, pagination cursors and object-storage paths. Consider database row-level security as defense in depth, with explicit connection/session reset rules. The composite key demonstrates why order ID alone is insufficient when IDs are only unique within a tenant. It is not an authorization system. Production code must derive the tenant from a verified principal and membership check, not accept an arbitrary X-Tenant header as authority.
A helper named GetByID can bypass isolation even if the list endpoint filters correctly. Shared cache entries and metrics labels can leak data or create unbounded cardinality. Test two tenants with the same resource ID, guessed IDs, stale permissions, bulk operations and queued work. A context value transports identity; it does not validate the identity or replace an explicit authorization decision.

Common trap

Protecting one HTTP handler or one SQL query does not secure every secondary access path.

Try it

Trace list, detail, bulk export, background job, idempotency key and object-storage paths with two tenants using the same ID.

Worked solution

The cache can cross the tenant boundary despite the correct SQL query. Derive tenant scope from a verified principal and membership decision, and include it in cache and storage keys, predicates, uniqueness and job data. Recheck authorization where policy requires it, including a cache hit. Use tests that attempt cross-tenant access through every access path; context values alone do not authenticate their contents.

Change the constraint

How do permission revocation and row-level security interact with connection pooling?

How did you do?

Self-assessment, not a compiler pass or interview score. Explain the reasoning unaided before marking understood.

Not marked. Progress stays on this browser, separate from course completion.

Primary references

Checked 2026-10-28. Use documentation for your pinned runtime, driver and tool version.