Authorisation belongs in the database
An application grows callers. It starts with the one the users see, then an admin surface, then an import, then a scheduled job, then a report. Each one needs to know who is allowed to see what. If that knowledge lives in the application layer, you now maintain the same policy in as many places as you have callers, and they will drift — not because anyone is careless, but because the import gets written six months after the rule was agreed and nobody reads the UI guard while writing it.
Put the rules where the data is and every caller meets the same predicates, including the ones you have not written yet.
On the ERP we built for a custom car shop, authorisation is row-level security on every table. Not on the sensitive ones. Every one. A table without a policy is a table that is either open or closed by accident, and which of those it is depends on a default nobody is thinking about at the time.
Roles that match the business, not the software
Five roles, and a user can hold more than one. Both halves of that matter.
Five is not a number we picked for tidiness: the roles map to how the shop is actually organised, which is what makes them explicable to the people who have to administer them. A role set invented by engineers tends to describe the software — viewer, editor, admin — and then every real conversation becomes a translation exercise.
Overlap matters because small organisations are full of people doing two jobs. In a ten-person shop the person who books the work may also be the person who invoices it. A model that forces one role per user either invents a sixth composite role for that person, or it makes someone share a login. Both are worse than letting a user hold two roles.
A new account reads nothing
Authentication is password or magic link, behind a swappable provider. The part worth stating is what happens immediately after: a new account reads nothing until it is approved.
Self-service signup on an internal system is a quiet hole. Someone with a company email address should not become a user by filling in a form. Defaulting a fresh account to no readable rows means approval is a deliberate act with a person's name against it, and the failure mode of forgetting to approve someone is an annoyed colleague rather than an open door.
Totals are not application arithmetic
Money is where an authorisation model gets tested, so the same principle runs through it. Totals are generated columns and triggers rather than values computed in the application and written back. Settlement lines are immutable once issued.
The reason is the same as for the predicates. An application-computed total is correct only as long as every writer remembers to recompute it, and the number of writers grows. A generated column cannot be forgotten, because there is nowhere to forget it from.
A parity test asserts that every total matches the lines it is derived from. That test has never been the interesting part of the suite and it is the one I would keep if I could only keep one, because it is the only thing standing between a rounding change and a figure somebody invoices.
File bytes never touch the app server
Uploads and downloads use single-use signed URLs issued directly against object storage. No file bytes pass through the application server at all.
This is usually framed as a performance decision, and it is one, but the authorisation story is better: the signed URL is the permission. It is scoped, it is single-use, and it expires. There is no endpoint to protect, so there is no endpoint whose protection can be wrong.
Testing predicates means testing against a real schema
An RLS matrix runs in CI against a real database — every role against every table, asserting what each one can and cannot reach. Mocking this is not possible in any useful sense: the thing under test is the database's own evaluation of the policy. A mock would only be testing our belief about it, which is exactly the belief that drifts.
That matrix is also the cheapest documentation of the authorisation model that exists. When someone asks what a given role can see, the honest answer is the test file.
Vendors are generic on purpose. Row-level security is a Postgres feature and the argument holds on any Postgres; the auth provider is swappable by design, which is the point of naming it that way. No project identifiers, hostnames or credential names appear on these sites.