Stage 7 · Master
Phase 20 — Production Readiness
Security
Close the tenant attack surface and split every service's database login into owner, migrator, and application roles before this platform serves its first paying tenant.
Phases 2 through 14 gave organization, maintenance, payment, and ten other services exactly one job each and a migration path to match. None of them were ever asked the two questions that decide whether a multi-tenant platform is safe to sell: what happens when one tenant's request reaches another tenant's row, and what a single leaked service credential can reach. This lesson answers both before Phase 20's remaining seven lessons build backup, audit, and rollout controls on top of a database access model that has to be trustworthy first.
Map the Tenant Attack Surface Before Hardening It
Every domain table across the platform carries an organization_id column — organization is the tenant root that flat, resident, maintenance, payment, and every other service scope their rows to. Three concrete paths turn that shared column into an incident. First, an IDOR: a repository method that loads a row by primary key without also filtering by organization_id, so any authenticated user who guesses or enumerates another tenant's UUID reads or writes across the tenant boundary. Second, a forged tenant claim: a handler that trusts an X-Organization-Id header instead of the verified JWT claim set by Phase 4's authentication middleware. Third, and worst, a single shared Postgres login used by every service with SELECT, INSERT, UPDATE, DELETE, and schema-owner rights all at once — leaking that one credential is equivalent to leaking the entire database, every tenant, every service, in one step.
// FindByID is reachable from GET /v1/charges/:charge_id. It compiles,
// it passes every unit test written against a single tenant, and it
// leaks every other tenant's monthly charges to anyone who can guess
// a UUID, because nothing here ever reads organization_id.
func (r *ChargeRepository) FindByID(
ctx context.Context,
chargeID uuid.UUID,
) (Charge, error) {
const query = `
SELECT id, organization_id, flat_id, amount_cents, due_on, status
FROM maintenance.charges
WHERE id = $1`
var charge Charge
err := r.pool.QueryRow(ctx, query, chargeID).Scan(
&charge.ID, &charge.OrganizationID, &charge.FlatID,
&charge.AmountCents, &charge.DueOn, &charge.Status,
)
if err != nil {
return Charge{}, fmt.Errorf("find charge: %w", err)
}
return charge, nil
}Nothing here is a typo. The query is valid SQL, the handler compiles, and a single-tenant integration test passes — the missing predicate only becomes visible when a second tenant exists, which is exactly the moment it stops being a lab condition.
// FindByID now cannot compile without a tenant.ID, and cannot return
// a row that belongs to a different tenant even if the caller passes
// the correct UUID for someone else's charge.
func (r *ChargeRepository) FindByID(
ctx context.Context,
tenantID tenant.ID,
chargeID uuid.UUID,
) (Charge, error) {
const query = `
SELECT id, organization_id, flat_id, amount_cents, due_on, status
FROM maintenance.charges
WHERE organization_id = $1
AND id = $2`
var charge Charge
err := r.pool.QueryRow(ctx, query, tenantID.String(), chargeID).Scan(
&charge.ID, &charge.OrganizationID, &charge.FlatID,
&charge.AmountCents, &charge.DueOn, &charge.Status,
)
if errors.Is(err, pgx.ErrNoRows) {
return Charge{}, ErrChargeNotFound
}
if err != nil {
return Charge{}, fmt.Errorf("find charge: %w", err)
}
return charge, nil
}A charge in another tenant now produces the identical ErrChargeNotFound a missing charge produces — the response never distinguishes 'does not exist' from 'exists but is not yours', which closes the enumeration side channel as well as the read.
| Attack vector | Exploit path | Hardening control |
|---|---|---|
| IDOR on a repository method | Enumerate charge/invoice/complaint UUIDs; read or mutate rows outside the caller's tenant | Every repository method takes tenant.ID and every query predicate includes it |
| Forged tenant header | Send X-Organization-Id for a different tenant; handler trusts the header over the verified JWT claim | tenant.ID is only ever constructed from the authentication middleware's verified claim, never from a request header |
| One shared database login | Leak any one service's connection string; act as schema owner across all thirteen services | Split into hoa_owner, hoa_<service>_migrator, and hoa_<service>_app_<generation> per service |
Split Every Service's Login Into Owner, Migrator, and Application Roles
maintenance, like every other service, currently authenticates to Postgres with one login role that owns its schema, runs its migrations, and serves its traffic. That single role has no ceiling: a SQL injection or a stolen connection string on the traffic path inherits schema-owner rights, including DROP TABLE. Least privilege forces that one role apart into three, scoped to a single service's schema so a leaked credential from one service cannot reach another's data at all.
- hoa_maintenance_owner — NOLOGIN, owns the maintenance schema and every object in it. Nothing authenticates as this role directly.
- hoa_maintenance_migrator — LOGIN, but every session immediately runs as hoa_maintenance_owner via ALTER ROLE ... SET role. Used only by the Helm chart's migration Job.
- hoa_maintenance_app — NOLOGIN group role granted SELECT, INSERT, UPDATE, DELETE on the maintenance schema only — no DDL, no other service's schema.
- hoa_maintenance_app_1 — LOGIN, a member of hoa_maintenance_app and nothing else. This is the credential the running Deployment authenticates as, and the one that rotates.
-- Usage, once per service:
-- psql "$ADMIN_DATABASE_URL" \
-- -v service=maintenance \
-- -v migrator_password="$(openssl rand -base64 24)" \
-- -v app_password="$(openssl rand -base64 24)" \
-- -f deploy/postgres/bootstrap-service-roles.sql
SELECT format('CREATE ROLE %I NOLOGIN', 'hoa_' || :'service' || '_owner')
WHERE NOT EXISTS (
SELECT 1 FROM pg_roles WHERE rolname = 'hoa_' || :'service' || '_owner'
) \gexec
SELECT format('CREATE ROLE %I NOLOGIN', 'hoa_' || :'service' || '_app')
WHERE NOT EXISTS (
SELECT 1 FROM pg_roles WHERE rolname = 'hoa_' || :'service' || '_app'
) \gexec
SELECT format(
'CREATE ROLE %I LOGIN PASSWORD %L',
'hoa_' || :'service' || '_migrator',
:'migrator_password'
)
WHERE NOT EXISTS (
SELECT 1 FROM pg_roles WHERE rolname = 'hoa_' || :'service' || '_migrator'
) \gexec
SELECT format(
'CREATE ROLE %I LOGIN PASSWORD %L',
'hoa_' || :'service' || '_app_1',
:'app_password'
)
WHERE NOT EXISTS (
SELECT 1 FROM pg_roles WHERE rolname = 'hoa_' || :'service' || '_app_1'
) \gexec
SELECT format('CREATE SCHEMA IF NOT EXISTS %I AUTHORIZATION %I', :'service', 'hoa_' || :'service' || '_owner') \gexec
-- A migrator session must act as the owner to run DDL, but session_user
-- stays hoa_<service>_migrator in pg_stat_activity and audit logs — the
-- identity that connected is never hidden, only its effective privilege.
SELECT format('GRANT %I TO %I', 'hoa_' || :'service' || '_owner', 'hoa_' || :'service' || '_migrator') \gexec
SELECT format('ALTER ROLE %I SET role = %I', 'hoa_' || :'service' || '_migrator', 'hoa_' || :'service' || '_owner') \gexec
SELECT format('GRANT USAGE ON SCHEMA %I TO %I', :'service', 'hoa_' || :'service' || '_app') \gexec
SELECT format('GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA %I TO %I', :'service', 'hoa_' || :'service' || '_app') \gexec
SELECT format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA %I TO %I', :'service', 'hoa_' || :'service' || '_app') \gexec
SELECT format(
'ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO %I',
'hoa_' || :'service' || '_owner', :'service', 'hoa_' || :'service' || '_app'
) \gexec
SELECT format('GRANT %I TO %I', 'hoa_' || :'service' || '_app', 'hoa_' || :'service' || '_app_1') \gexechoa_maintenance_app_1 is only ever granted hoa_maintenance_app, never hoa_maintenance_owner or hoa_maintenance_migrator — the identity a running Deployment authenticates as can read and write rows in its own schema and nothing else, no matter what a request handler does wrong.
hoa_maintenance_migrator is granted membership in hoa_maintenance_owner and then has its session default set to that role, so every object a migration creates is owned by hoa_maintenance_owner, not by the migrator itself — ownership follows current_user, not inherited privilege. hoa_maintenance_app_1 never receives this grant: it can use what hoa_maintenance_app was explicitly given, and no more, which is the entire point of the split.
Add Row-Level Security as a Second, Independent Boundary
The patched repository method above is correct today, but application code is written by people, and a future report, ad hoc script, or hurried hotfix can omit the organization_id predicate the same way the original method did. Row-level security asks Postgres itself to enforce tenant scope on every statement hoa_maintenance_app_1 runs, so an omitted predicate returns zero rows instead of another tenant's rows — a second boundary that does not depend on every future author remembering the first one.
ALTER TABLE maintenance.charges ENABLE ROW LEVEL SECURITY;
ALTER TABLE maintenance.charges FORCE ROW LEVEL SECURITY;
CREATE POLICY charges_tenant_isolation
ON maintenance.charges
USING (
organization_id = nullif(current_setting('app.current_tenant', true), '')::uuid
)
WITH CHECK (
organization_id = nullif(current_setting('app.current_tenant', true), '')::uuid
);
FORCE ROW LEVEL SECURITY matters as much as ENABLE: without it, the table's owner — hoa_maintenance_owner, and by extension any session that ran ALTER ROLE ... SET role into it — bypasses the policy entirely. hoa_maintenance_app_1 is never that owner, so FORCE closes a bypass that would otherwise sit unused until someone changed the migrator's role membership by mistake.
DROP POLICY charges_tenant_isolation ON maintenance.charges;
ALTER TABLE maintenance.charges DISABLE ROW LEVEL SECURITY;
Dropping the policy before disabling row-level security mirrors the up file's order in reverse — Postgres allows disabling RLS while a policy still exists, but leaving no dangling policy behind keeps the table's state fully clean if 00006 is ever forced back down.
package postgres
import (
"context"
"errors"
"fmt"
"github.com/hoa-platform/backend/pkg/tenant"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgxpool"
)
type TenantTransaction func(ctx context.Context, tx pgx.Tx) error
// WithinTenant opens a transaction and sets app.current_tenant for that
// transaction only (the "true" argument to set_config), so the RLS policy
// above sees the caller's verified tenant on every statement inside fn,
// and the setting cannot leak onto a pooled connection's next, unrelated
// transaction.
func WithinTenant(
ctx context.Context,
pool *pgxpool.Pool,
tenantID tenant.ID,
fn TenantTransaction,
) error {
if pool == nil || fn == nil {
return errors.New("pool and transaction function are required")
}
tx, err := pool.BeginTx(ctx, pgx.TxOptions{})
if err != nil {
return fmt.Errorf("begin tenant transaction: %w", err)
}
defer func() { _ = tx.Rollback(context.Background()) }()
if _, err := tx.Exec(ctx, "SELECT set_config('app.current_tenant', $1, true)", tenantID.String()); err != nil {
return fmt.Errorf("set tenant context: %w", err)
}
if err := fn(ctx, tx); err != nil {
return err
}
return tx.Commit(ctx)
}Every maintenance repository method now runs inside WithinTenant, so the RLS policy and the application predicate agree on the same tenant.ID by construction — an attacker would have to defeat both, not one.
A policy answers one narrow question — which tenant's rows may this session see — and nothing about whether a resident may edit another resident's charge, or whether a suspended staff account should be trusted at all. Those authorization decisions stay in application code. Skipping the application predicate because 'RLS will catch it' removes the layer that produces a clean 404 instead of a raw permission-denied error, and the layer that is exercised by every unit test, not only by an integration suite that actually opens a real Postgres connection.
Applied exercise
Patch an IDOR and prove the blast radius shrinks
services/complaint-service/internal/complaint's repository currently exposes FindByID(ctx, id uuid.UUID) with a cache key of complaint:{id} and no organization_id predicate — the same shape charges.go had before this lesson patched it.
- Add tenant.ID to the method signature, the SQL predicate, and the cache key, following the maintenance example.
- Write the golang-migrate migration that enables and forces row-level security on complaint.complaints, scoped by app.current_tenant.
- Write the bootstrap-service-roles.sql invocation (the exact psql command with -v service=complaint) that creates hoa_complaint_owner, hoa_complaint_migrator, hoa_complaint_app, and hoa_complaint_app_1.
- Using psql as hoa_complaint_app_1, prove a cross-tenant SELECT returns zero rows and a DROP TABLE attempt is rejected.
Deliverable
Updated Go signature and SQL predicate, a new RLS migration, the exact bootstrap command for the complaint service, and two psql transcripts proving both boundaries independently.
Completion checks
- No public method on the complaint repository can retrieve a row without tenant.ID.
- A cross-tenant read and a request for a nonexistent ID produce the identical not-found result.
- hoa_complaint_app_1 can be proven, by direct psql session, to lack DDL rights on its own schema.
Why is hoa_maintenance_migrator granted membership in hoa_maintenance_owner and then set as its own default role, rather than granting hoa_maintenance_owner directly to hoa_maintenance_app_1?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.