Vocational training

Nobody can see what they shouldn't — including us

Access control moved out of the application and into the database, so the limit holds even with the application switched off.

A case management record, CASE-4192, shown twice side by side. On the left a Case Officer scoped to the North district sees the district, status and assignment, but linked cases read "two not in your scope" and case history is unavailable, with two actions available. On the right a Service Manager scoped to all districts sees the same record with both linked case references, fourteen history entries and four actions. Below, three SQL checks run directly against the database return zero rows with no user context, 1,204 of 12,840 rows for the restricted officer, and zero rows for a record in another district.
The same record, two roles. The limit is in the database, not the interface — which is why a record outside your scope returns 404 rather than an empty page.

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.

Case studies

More of the work

Have a similar problem?

Tell us what the process looks like today. We will say whether this approach would work for you.