The database says no

At the end of the last milestone I wrote that the next one was the part most portfolio projects skip: proving that a teacher cannot read another teacher’s pay at the database, whatever the application does. This is that milestone. It took one day to design, one day to build and review, and one evening to put on the mini-PC under my desk and check by hand through a tunnel.

The short version. The API used to connect to Postgres as the database owner, which bypasses every access rule Postgres has. Now it connects as a role called minim_app that owns nothing and can do almost nothing on its own. Every signed-in request opens one transaction, switches to a role that matches the person’s job, tells the database who is acting, and only then touches a table. Postgres decides which rows that role can see and which columns it can read. The application still checks first, because a 403 is a better error than a permission failure. But if the application forgets, the database refuses anyway.

I want to write about three things: what “the database refuses” looks like when you test it, the four places the reviews found the plan wrong before any code existed, and a number that came out equal when I half expected it not to.

What a refusal looks like

The old system had four job roles (manager, head, teacher, support) and two add-on grants (hr and marketing), stored as text in a spreadsheet and checked with string comparisons on every server function. I kept the model and moved it into the database as real roles. Four request roles, one minim_hr role holding the grants on the sensitive columns, and two combined roles for a teacher or head who also does HR, because Postgres lets a connection switch to exactly one role at a time.

The connection role is the interesting one:

CREATE ROLE minim_app LOGIN NOINHERIT NOBYPASSRLS;
GRANT minim_manager, minim_head, minim_teacher, minim_support,
      minim_teacher_hr, minim_head_hr TO minim_app;

NOINHERIT means membership lets minim_app switch into those roles but gives it none of their privileges until it does. So a request that forgets to switch has no access to any pay table at all. I wanted that to be a proved fact rather than a hope, so the test suite connects as minim_app, runs no SET ROLE, and asks for attendance:

ERROR:  permission denied for table attendance

The tests assert on that text. Not “it threw”, the database’s own words, read off the error the driver wraps. That distinction matters more than it sounds. A test that only checks for an exception passes when the table name is misspelled.

The one I like best is the column grant. Contact details (phone, address, an email to write to) live in their own table, and only a manager or someone with the hr grant may read those three columns. Everyone else was granted the key column and nothing more. So as a teacher:

SELECT person_id FROM person_contacts;   -- fine, returns nothing (no row policy)
SELECT phone FROM person_contacts;       -- ERROR: permission denied for table person_contacts

Same table, same role, one column allowed and one refused. That is a column grant doing its job, and the pair of queries is the proof.

Row filtering is the other half. Every pay table has a policy of the form “a teacher sees rows where the filer is the person the request said it was”:

CREATE POLICY attendance_own ON attendance TO minim_teacher
  USING (filed_by_person_id = app_person_id())
  WITH CHECK (filed_by_person_id = app_person_id());

app_person_id() reads a transaction-local setting the API writes at the start of each request. The WITH CHECK half means a teacher cannot insert a row filed by somebody else either. The test does exactly that and gets new row violates row-level security policy.

Then view-as. A manager can look at the app as a teacher, and the rule is that view-as is read-only. The application refuses any write with a view-as header before it reaches a service. I also had the request’s transaction set transaction_read_only = on, so that if a future change forgets the first check, Postgres refuses the write with cannot execute INSERT in a read-only transaction. Two locks, and the second one is tested on its own.

Four places the plan was wrong before any code ran

I plan these milestones as a document with the exact SQL and code each task should produce, then a fresh AI agent implements each task and a different agent reviews it. This time I also ran a pre-flight pass over the plan before dispatching anything, looking for pairs of tasks that disagree with each other. It found two problems. The reviews found two more in the first task.

Support had no grants at all. My spec said support (the receptionist role) sees “no rows” in the pay tables. My plan’s SQL gave support no privileges on those tables, which produces permission denied, not an empty result. The test I had written for task two expected the empty result. The spec and the plan disagreed, and the plan was the one that was wrong.

Head could not confirm their own month. A head teacher reads everyone’s draft but has pay of their own, and confirming a month writes to the invoices table. My policy gave head read access and nothing else. The first reviewer traced the confirm route and found the write would fail. The fix split one policy into three: read everything, manager writes anything, teacher and head write their own row.

A grant with no policy is a dead grant. The spec said head could see every contact row’s key column. The migration granted head that column and then enabled row filtering with no policy naming head. Postgres treats no policy as no rows. The grant was correct and did nothing. The reviewer caught it by asking what a head would get back.

“Pre-existing warning” was not pre-existing. The last one is the one I would have let through. Task three moved every request onto a single database connection, and the month calculation fires eight queries at once with Promise.all. On one connection those queue, and the Postgres driver prints a deprecation warning when they do. The implementer reported the warning as pre-existing, same count as before the change. The reviewer did not believe it, because the mechanism in the diff was exactly what causes that warning. I sent the implementer back with an instruction to prove the count on the base commit and on its own commit. Zero before. Five after. Zero again once the eight queries ran one after another. The claim in the report was wrong, and it was wrong in the direction that hides a regression.

Sixteen rulings in all, each one written down with what it costs if I am wrong. I have started to think of the plan as an argument rather than a set of instructions. The spec is the authority. The plan argues from it, and reviewers are allowed to win.

The number that came out equal

The risk in filtering rows at the database is that the pay calculation sees less data than it used to. A teacher’s month used to be computed from every attendance row in the month, then the teacher’s slice picked out. Now the teacher’s request only sees their own rows, plus the shared rate tables, and the calculation runs on that.

So I added a gate: for every demo person, compute their month as themselves (filtered) and as the manager (unfiltered), and compare seven numbers. Work, materials, expenses, two tax lines, the total, and the line count.

Person	Total as self	Total from the manager’s draft
Teacher	¥17,517	¥17,517
Cover teacher	¥4,043	¥4,043
Head	¥24,500	¥24,500

They matched first time. The reason is worth stating, because it was luck until I understood it. The row filter and the pay attribution use the same column, filed_by_person_id. Pay follows whoever filed the row, and the policy shows a teacher the rows they filed. Filtering by that column cannot change a sum grouped by that column. Had the old system paid the scheduled teacher rather than the filer, the gate would have failed, and I would have had to widen the policy or change the calculation.

A reviewer later pointed out the gate could pass with all zeros, if a fixture change ever zeroed a person out. It asserts each total is above zero now.

The deploy, and a cookie with a different name

The migration ran against the mini-PC through an SSH forward, with the runtime role’s password set from an environment variable rather than written into the migration file. I checked the role column first, because the migration converts free text into an enum and fails on any value outside the four. Then both containers rebuilt, and I repeated the SQL proofs on the box: bare minim_app refused, a teacher sees one of two rows, phone refused, TRUNCATE refused, a manager reads the phone, the read-only path refuses a write.

The last check needed a real browser session as the manager, which needs Google sign-in, which needs a public hostname. So: start the quick tunnel, point the auth origin at it, register the redirect URI, sign in, copy the session cookie, and curl the view-as routes from my laptop.

Every request came back 401. The cookie was fresh, the session row was in the database, the token matched. Twenty minutes went into that before I found it. On an HTTPS origin the auth library names its cookie __Secure-better-auth.session_token. On plain HTTP, which is what every local test uses, it is better-auth.session_token. Same value, different name, and the server only looks for the one that matches its origin. With the prefix, all four checks passed: the manager’s own identity, the teacher’s identity under view-as, the teacher’s month, and a 403 READ_ONLY_VIEW on the write. Then the tunnel came down and the box went back to its resting state.

Two smaller traps

Git Bash on my Windows machine drops any TZ value containing a slash before it reaches Node. The test that checks the pay calculation under Tokyo time and under UTC had been running both passes under the system zone, which happens to be Tokyo. Every prior “both timezones green” was one timezone. The oracle now runs through PowerShell, which forwards the variable.

And a CREATE ROLE ... IF NOT EXISTS guard does what it says: if the role exists, it skips. It does not check the role’s attributes. A minim_app created by hand with the defaults would keep the ability to bypass row security, and every proof in the suite would pass on CI while the deployed box ignored the policies. The final review promoted that from “minor” to “fix before merge”, and the migration now sets the attributes on the line after, whether the role existed or not.

What I am keeping

Ask the database for its own refusal. A test that asserts permission denied for table person_contacts proves a column grant. A test that asserts “an error occurred” proves a typo could be anywhere.

Two locks on anything read-only. The application check gives a good error. The transaction flag makes the error impossible to skip.

A claim in a report is a claim. “Pre-existing, same count” cost one message to check and would have cost a silent regression to believe.

When filtered input must not change an answer, find the column the filter and the answer share. If there is none, the gate will fail, and it should.

Grants and policies are separate switches. A grant with no policy returns nothing. A policy with no grant returns an error. You need both, and you find out which one you forgot by reading the result as the role.

The client comes next. Until now every screen has been a proof that the contract works. The next milestone is the one where a teacher files attendance from a phone and a manager sees warnings and drafts, with view-as as a banner rather than a header.