Skip to main content

​
Tenancy, RLS and derived identity

Three separate controls, all answering versions of the same question: who is this, and what may they see.

​
1 · Row-level security, org-scoped

Every GL table is org-scoped and carries a row-level security policy. anon reads zero rows. Reads in the DevCenter run as a dedicated, RLS-scoped role:
So a cross-organisation caller sees zero, not an error and not a filtered subset assembled by the application. The filtering is a property of the database session. Writes lock for update, filter by org_id, and enforce separation of duties on top.
Application-level filtering and RLS are not alternatives. Application filtering is a where org_id = ... that one query can forget. RLS is a policy that holds for the query somebody writes at 2am to answer a support question.

​
The organisation context

X-WorthState-Org-Id, falling back to the configured single-organisation default. This is a legacy internal wire identifier retained by the shipped protocol; it is not a GreatBook brand or domain claim. The paired half of that lives in the UI: the API proxy strips x-worthstate-org-id from browser requests, so a browser cannot choose the organisation a row is created in. Onboarding a second organisation means the proxy must set it from verified session membership.

​
2 · Read-only sessions, enforced by Postgres

The Auditor and the Accountant share one read channel, so the guarantee is enforced in exactly one place. The Postgres session itself rejects any INSERT, UPDATE or DELETE with read-only transaction. Getting that to be true through the deployed transport took four mechanisms, not one, and the Auditor page walks all four: a transaction-pooler refusal, a forced default_transaction_read_only=on startup parameter, a post-connect check of the port libpq actually reached, and a SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY whose result is read back from the server before the connection is handed out.
psycopg.connect(dsn, read_only=True) is not the mechanism, and this documentation used to say it was.On an autocommit connection that attribute is inert: psycopg applies it when it begins a transaction, and autocommit never begins one. The session reports transaction_read_only = off and executes an INSERT. It is still set, as belt, so a future non-autocommit caller emits BEGIN READ ONLY, but nothing rests on it.
This is why the assurance agents were built before any further write agent. “The Auditor is read-only” is not a claim about the code being careful; it is a property the database enforces and then confirms on request, and a channel that cannot get that confirmation fails rather than degrading into the thing it claims to prevent. Three details:
  • It is a separate read layer, not the shared pool. The shared pool commits on a clean exit, which is correct for the writers it serves and wrong here.
  • The Auditor never imports a writer. It reuses the writer’s pure hash helpers so its recompute cannot drift, but never its post path.
  • The DSN precedence mirrors the Bookkeeper’s, so the reader and the writer see one database. An assurance agent reading a replica would produce findings about a book that is not the book.
The GL’s hash chain and a stable read want the session-mode DSN (port 5432), not the transaction pooler (6543). The advisory lock and the checkpointer both need a stable session.For the assurance channel this is not a preference but a refusal: a session-scoped SET on a transaction pooler would persist onto a backend the pooler later hands to a writer, so the connection is refused rather than used.

​
3 · The acting identity is derived, never client-supplied

Every separation-of-duties control in this system is enforced against one string: the actor. It decides who may approve, and it becomes journal_entries.created_by. That string used to arrive in a browser-supplied X-Actor header which nothing on the server re-derived. So the approver simply chose it. The submitter of a document could open devtools, send X-Actor: <a colleague>, and approve their own document. Maker-checker passed, and the audit trail named someone who never touched it.
An audit trail the subject of the audit writes is not an audit trail.

​
The rule

The actor comes from the identity the request was authenticated with, and X-Actor is ignored whenever such an identity exists. Two authenticated callers, matching the two authentication branches: The second row is the path the gated posting approval uses. Anything else is unauthenticated and never reaches a route.

​
It is a single module, and a test enforces that

devcenter/identity.py is the only place that answers “who is acting”. Mutating routes take the Actor dependency. A test fails the build if any API module reads x_actor itself. That test is the control. Without it, the rule is a convention that holds until the next router, and the next router is where it will not.

​
The second identity form

The Typewriter’s tables key a person by the Supabase auth.users UUID, because that is what their RLS policies compare against. So the same module derives that second form from the same verified session. It is None for a service-token caller, and a route that writes such a column must refuse rather than invent one.

​
The anon read revocation

Migration 0030 revokes anon and authenticated SELECT on the three document tables. The reason is worth spelling out, because it is a latent-exposure pattern rather than an exploited one. The authenticated policies carried no organisation term, and one of the tables has no organisation column at all. So a designated approver reading through the auto-generated REST layer would have seen every organisation’s documents. The application never used those policies - it reads through the service-role connection, org-scoped in the query - so the grant itself was the exposure, not the code.
If that SELECT is ever re-granted, the policies must be org-scoped first. Nothing static fails if it is not, which is exactly why this is written down here.

​
Where it is enforced

One deploy detail found the hard way: granting the read role membership is not enough on modern Postgres. It needs grant ws_reader to postgres with set true, or set local role fails at runtime with a permission error that reads nothing like a configuration problem.

​
The tests that would fail if it broke

That last one is worth naming precisely. The read-only claim is proven, not trusted.

​
What goes wrong without it

Without derived identity, maker-checker is decorative: the approver names whoever they like and the trail agrees. Without RLS, the first multi-organisation bug leaks another company’s ledger, and application-level filtering fails at exactly the query nobody reviewed. Without read-only sessions, “the Auditor cannot write” is a promise about code you would have to read all of to believe.