Context
A vocational training organisation running operations across several countries, with teams
split by department and by region. Records ran into the tens of thousands across a media
catalogue and its supporting workflow.
The problem
Everything ran on a spreadsheet-style SaaS platform, and on that kind of platform permission
is a property of a page, not a property of the data. What a person could see depended on
which screen had been shared with them. The “role” field in the table controlled nothing.
That has one consequence that matters more than any other: you cannot answer the question
“what can this person see?” from the data itself. It is the first question an auditor
asks, and the honest answer was that somebody would have to open every screen and check.
Each page also re-implemented its own limits — one with 59 fields visible and 15 editable,
another with 28 and 5. Every screen was reinventing the boundary, and no two agreed.
What we built
We moved the data to PostgreSQL and moved the boundary into the database, where every route
to the data has to pass through it.
- The application connects as a role that is not a superuser and has no bypass
privilege. There is no connection in the system that sees everything.
- Row-level security is not just enabled but forced, so even the table owner is subject
to it.
- The user’s identity is set inside the transaction and cleared when it ends, so it
cannot leak between two requests sharing a pooled connection. Auditors ask about this one
specifically.
- The write role has no DELETE at all. Every write goes through a function that checks
permission first.
- Even an administrator’s “see everything” is a recorded policy row, not a hidden
exception — readable, auditable, and revocable.
The decision we are most pleased with is a small one. Geography is not a containment
tree. The work is distributed, and one person may look after several regions that do not sit
next to each other. So region is a scope dimension whose value is a set, not a branch of a
hierarchy. Every regional model we have seen that got this wrong had to be rebuilt.
The result
Three checks, run over HTTPS against the live system:
- A connection with no user context returns zero rows.
- A restricted account sees only its own scope — around ten thousand records from a
catalogue several times larger.
- A record outside that scope returns 404, not an empty page. The account is not told
the record exists.
Access is now something you can produce a list of, from the data, on demand.