Four rows that were not theirs

Hardening multi-tenant row-level security in four reversible phases, verified by impersonating a member and counting what they could see.

3 min readMicroGym · Isolation stage

The live-operations platform is built to run more than one gym, and every gym must see only itself. That is enforced in the database with row-level security, which is the right tool and a difficult one. Policies cannot be unit-tested the way code can. They recurse the moment two tables check each other. And the first version of them was wrong.

How wrong

Four rows. Signed in as a member, with the policies in place and passing every glance, the member could reach four rows that belonged to someone else. Not a breach, not a leak anyone noticed, but four rows more than zero, and zero is the only acceptable number. Three tables were also found with row-level security switched off entirely, which is the failure mode the feature invites: a table is added, nobody turns it on, and the default is open.

Four phases, each with a way back

The fix was done as four phases of hardening, each a migration with its own rollback file. Not one big migration, because a policy change that breaks a legitimate query breaks it for everyone at once, in production, and the rollback has to be a file you can run rather than a plan you can describe. Phase one dropped the permissive policies that had been quietly granting access with a condition of true. Phase two scoped the gyms table itself, which had been fully open, and introduced the helper that ends the recursion. Phase three closed the settings table while keeping the one insert that signup legitimately needs. Phase four turned row-level security on for the three tables where it had been off entirely.

The check was the same each time: impersonate a member, run the queries the app runs, count the rows that came back that should not have. The number went from four to zero. Only the first phase's file records that count; the later phases carry no verification line, so the four-to-zero figure is the first phase's, and this note says so rather than claiming it for all four. That count is the test. It is crude, it is honest, and it is the thing the more elegant approaches skip.

The recursion

The specific trap is worth naming. Table A's policy checks table B to find the member's gym. Table B's policy checks table A to see whether the member is allowed to look. Postgres notices and refuses with an infinite-recursion error, or, worse, one policy is loosened to make the error go away. The way out is a security-definer function that answers the question, which gym is this user in, without going through the policies itself; a second one, added in the last phase, answers it for the admin who can hold more than one gym. The policies that cross tables ask those functions instead of each other. Three of the eight do not need to and check a column or a single subquery inline.

Publishing the number

The cost of this stage is written on the anatomy for that platform: publishing the number four. It would be easy to say the platform enforces tenant isolation in the database and leave it there, which is true. It is stronger to say that the first version let a member reach four rows, that the person who built it found them, wrote the fix in reversible phases, and counted the result. Anyone who has run a multi-tenant system knows which of those two statements they would rather hear from the person they are about to hire.

Written by Dylan Pulver, who built the platform this decision lives in. Software engineer and entrepreneur, Toronto.

More notes
  • The webhook that credits twiceA payment processor delivers every result at least once. The interesting engineering is in what happens when your side fails halfway through.
  • A ledger you can edit from a route handler is not a ledgerWhy every movement of value on the payments platform goes through a Postgres function, and what it costs to keep it that way.
  • The bug inside the fixA payout race, the fix for it, the bug inside that fix, and why it is the best argument I have for one person owning the whole path.
  • The sale that arrives with no referrerA creator shares a link on Instagram, the buyer taps it, installs the app and purchases. Nothing in that chain carries the creator's name across. Here is what does.
  • The minimum that belongs to someone elseHeld commissions are released to creators once a brand's payout minimum is met. Group the money by creator, the obvious way, and one brand's minimum ends up holding another brand's money.
  • The commission that must not mint twiceA buyer pays and the platform owes a creator a commission. Between those two facts sit a colluding pair, a call that arrives twice, and a cart with three items on one payment.
  • The rule that has to be written twiceFirestore security rules do not cascade to subcollections. Forget that in one place and a single query returns every private message on the platform.
  • Thirty days in the ledgerSplitting a payment at charge time is simpler and wrong: a refund after the creator is paid is a clawback nobody enjoys. Holding the money creates a different set of problems, and each piece of machinery around the hold answers one of them.
  • Arbitrary but consistentA gym's assessment answers become rules that swap an exercise for a member before a session. Two rules can disagree about the same movement. The code says who wins, and the comment admits how.