Multi-tenant SaaS in Go with chi and sqlc: the whole tenancy layer

Row-level multi-tenancy is the least glamorous security boundary in a B2B
SaaS and the one most often broken by a Tuesday afternoon commit: a new
query, a missing WHERE organization_id = $1, and one customer reads
another's data. Row-level multi-tenancy in Go
explained the design; this is the layer itself, as it ships in GoVueKit,
file by file.

The model

Three tables carry tenancy: organizations, org_members (organization,
user, role) and every business table, which carries organization_id.
Identifiers are TEXT, generated in Go (UUIDs), so the schema is identical
on PostgreSQL and SQLite and nothing depends on a sequence.

Roles are three strings with an order: owner > admin > member. There is
no permission matrix, because a boilerplate that ships one ships a
maintenance burden; a product that needs one adds it next to
RoleAtLeast.

The query that decides everything

-- name: GetOrgMember :one
SELECT * FROM org_members WHERE organization_id = $1 AND user_id = $2;

sqlc turns it into a typed Go method. Every request that names an
organization goes through it, once, before any handler runs.

The middleware

func (o *Org) Context(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        u, ok := UserFrom(r.Context())
        if !ok {
            writeError(w, http.StatusUnauthorized, "authentication required")
            return
        }
        orgID := chi.URLParam(r, "orgID")
        m, err := o.Q.GetOrgMember(r.Context(), sqlcgen.GetOrgMemberParams{
            OrganizationID: orgID, UserID: u.ID,
        })
        if errors.Is(err, sql.ErrNoRows) {
            writeError(w, http.StatusNotFound, "not found")
            return
        }
        if err != nil {
            o.Log.Error("org middleware: load membership", "error", err)
            writeError(w, http.StatusInternalServerError, "internal error")
            return
        }
        organization, err := o.Q.GetOrganization(r.Context(), orgID)
        if err != nil {
            writeError(w, http.StatusNotFound, "not found")
            return
        }
        ctx := context.WithValue(r.Context(), orgKey, OrgCtx{Org: organization, Role: m.Role})
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

Three things to notice.

404, not 403, for a non-member. A 403 says "this exists and you may
not see it", which is exactly the enumeration oracle an attacker wants
when guessing organization ids. To an outsider, the organization does not
exist. The same status is returned whether the id is wrong or the
membership is missing, so the two cases are indistinguishable from
outside.

The membership row is the tenant context. The handler downstream
never re-derives who the user is or what they may do; it reads
OrgFrom(ctx) and gets the organization and the role. There is one place
to get tenancy wrong, and it is thirty lines long.

Errors are boring on purpose. A database failure is a 500 with a log
line, never a 404 that would make an outage look like a permission
problem.

The role check, after the tenant check

func RequireOrgRole(minRole string) func(http.Handler) http.Handler {
    return func(next http.Handler) http.Handler {
        return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
            oc, ok := OrgFrom(r.Context())
            if !ok || !org.RoleAtLeast(oc.Role, minRole) {
                writeError(w, http.StatusForbidden, "insufficient permissions")
                return
            }
            next.ServeHTTP(w, r)
        })
    }
}

Here a 403 is correct: the caller is a member, the organization is not a
secret to them, they simply lack the role. The order matters — membership
first (404), then role (403) — and the router enforces it structurally.

The route group

api.Route("/orgs", func(or chi.Router) {
    or.Route("/{orgID}", func(one chi.Router) {
        one.Use(orgMW.Context)
        one.With(middleware.RequireOrgRole(org.RoleAdmin)).Patch("/", orgsH.Update)
        one.With(middleware.RequireOrgRole(org.RoleOwner)).Delete("/", orgsH.Delete)
        one.With(middleware.RequireOrgRole(org.RoleOwner)).Patch("/members/{userID}", orgsH.ChangeRole)
        one.With(middleware.RequireOrgRole(org.RoleAdmin)).Delete("/members/{userID}", orgsH.RemoveMember)
        one.With(middleware.RequireOrgRole(org.RoleOwner)).Post("/billing/checkout", billingH.Checkout)
        // ...
    })
})

Everything under /api/orgs/{orgID} inherits orgMW.Context. A new
tenant-scoped endpoint cannot be mounted outside the group by accident,
because the group is where {orgID} is defined. The role requirement sits
on the route line, next to the verb, where a reviewer sees it.

The convention for business tables

When you add your first resource — the ten-minute tour
walks a projects table through every layer — the queries take this
shape:

-- name: GetProject :one
SELECT * FROM projects WHERE organization_id = $1 AND id = $2;

-- name: ListProjects :many
SELECT * FROM projects WHERE organization_id = $1 ORDER BY created_at DESC;

The handler passes OrgFrom(ctx).Org.ID as the first argument, always.
Because sqlc generates a typed parameter struct per query, a query that
lacks the tenant column also lacks the parameter, which is visible in the
generated signature and in review. It is not a guarantee; it is a
convention that is hard to violate silently, which is what a small
codebase can afford.

Lists of organizations themselves go through membership too: GET
/api/orgs returns the organizations the user belongs to, nothing else,
so an outsider does not even learn that an id exists.

The test that keeps it true

Unit tests on the middleware are cheap and exist. The one that matters
runs against the production binary in Playwright, with three real
browsers:

// RBAC: the member hits an admin-only route → 403, server-side.
const patch = await member.request.patch(`/api/orgs/${orgId}`, {
  data: { name: 'Hacked' },
  headers: { 'X-CSRF-Token': await csrf(member) },
})
expect(patch.status()).toBe(403)

// Cross-tenant isolation: an unrelated user gets 404 on the org, and
// never sees it in their list.
const probe = await stranger.request.get(`/api/orgs/${orgId}`)
expect(probe.status()).toBe(404)
const list = await (await stranger.request.get('/api/orgs')).json()
expect(list.organizations).toHaveLength(0)

An owner creates the organization and invites a member; the member
accepts from the email and gets a 403 on an admin action; a stranger gets
a 404 and an empty list. Remove the WHERE clause, or mount a route
outside the group, and CI goes red on both database engines.

What this does not cover, and why

No PostgreSQL row-level security policies: they would make the SQLite
target a second-class citizen and hide the boundary in the database, where
a Go reviewer does not look. No per-tenant schemas or databases: at the
scale a boilerplate targets, one schema with an indexed organization_id
is faster to operate and to back up. The 404-for-outsiders rule itself is
recorded in the kit's DECISIONS.md, which is public.

See it run, three browsers included, in the labs:

curl -fsSLO https://govuekit.dev/labs/docker-compose.yml
docker compose up -d

Originally published on govuekit.dev. GoVueKit is a complete Go + Vue 3 SaaS codebase you buy once and own; the whole stack runs on your machine with one docker compose up: https://govuekit.dev/labs