Every table in Pulse that holds an agency’s data has carried an organisation id and a set of row-level security policies since the migration that created it. Not added in a hardening pass, not scheduled for before launch. In the same file, in the same commit, as the table itself.
The rule is written as one line in the engineering guide: a table without policies does not merge. This is what that line buys.
Where the boundary lives
Multi-tenant isolation has to be enforced somewhere. There are two candidates and they are not equivalent.
In the application, isolation is a condition you remember to add. Every query says “where the organisation is mine”, and it works until one query does not say it. That query might be a new report, a background job, an export, an admin screen, a fix written at speed on a Friday. The failure is silent: the code runs, the page renders, the rows come back. There is no test that fails, because the query does exactly what it was written to do. The bug is what it was not written to do.
In the database, isolation is a property of the table. The condition is attached to the row, not to the query, and Postgres applies it to every select, insert, update and delete regardless of what asked. A forgotten clause in the interface returns nothing rather than returning somebody else’s records. That is the difference between a mistake that leaks and a mistake that returns an empty list.
Both are worth having and we have both. But only one of them still holds when a developer is wrong, and code is written by people who are sometimes wrong.
Why this matters specifically for an ATS
A general SaaS leak is embarrassing. An ATS leak is a different category of event, for three reasons.
- The records are people who did not choose you. A candidate gave their CV, their salary expectation and their current employer to one recruiter. They have no relationship with the software and no idea it exists. They cannot evaluate our security posture because they were never shown it.
- Your tenants are each other’s competitors. Two agencies on the same platform are frequently bidding for the same roles and chasing the same candidates. A cross-tenant read is not an abstract privacy problem, it is one customer seeing another customer’s live shortlist for a role they are both working.
- The most sensitive fact is often that a row exists at all. A candidate’s name appearing in an agency’s system means that person is looking. That can cost them their current job. The record does not need to be read for the damage to be done; being enumerable is enough.
Given those three, “we are careful about our queries” is not a security model. It is a hope.
Two functions, defined once
The policies do not each contain their own membership check. They call one of two functions: is this user a member of this organisation, and does this user hold at least this role in it. Both were defined in the very first migration in the repository, before any table that uses them existed.
That is a maintainability decision more than a security one, and it earns its keep every time a policy needs changing. Membership logic spelled out inline in every policy means fifty-odd copies of the same subquery, and the day the rules change, forty-nine of them get updated.
The role check is a minimum, not an equality: asking for admin matches an admin or an owner. Writing it as equality is a bug that appears months later, when the founder who owns the workspace discovers there is a screen they cannot open.
Three things we got wrong
The rule being right from the first migration did not stop us making mistakes underneath it. All three are still in the repository as migrations, which is the point of writing this section.
The revoke that did nothing, three times
Database functions in Postgres are executable by default, and our hosting layer explicitly grants that permission to the anonymous role on every new function. We wrote a migration revoking execute from PUBLIC, which removes the general grant and leaves the specific one to anonymous completely untouched. Then we wrote a second migration. Then a third.
The security advisor kept reporting the same problem, and the reason it kept reporting it is that all three migrations were correct SQL that did not do the thing we thought it did. Ten functions were reachable over the HTTP interface without a session.
Nine of those ten were saved by their own first line, which checks membership before doing anything and raises an error when the caller is nobody. That is defence in depth working exactly as advertised, and it is the argument for the belt as well as the braces. The tenth had no such check: it took an organisation id, ran as a privileged function, and rolled a credit period. Its only guard was that the period had to already be due, so the blast radius was small. It was still an unauthenticated write to another tenant’s row, and it was live.
The fix names the anonymous role in every revoke rather than trusting it to fall out of the general grant. That sentence is now copied as a comment into seven later migrations, which is the only reliable way we have found to keep a lesson attached to the place it applies.
The over-correction that took the site down
One of those three attempts went too far and revoked execute on the membership helper from signed-in users as well. Every query on every tenant table immediately failed with permission denied.
The reason is a genuinely counter-intuitive rule and it is worth knowing: a policy runs its condition as the querying role, not as the function’s owner. Marking a function as running with the definer’s privileges changes what its body may touch. It does not grant the caller permission to call it. Take execute away from signed-in users and the policy on every table stops being evaluable by the very people it was written to permit.
Both membership predicates are now explicitly granted to anonymous and signed-in callers, and the reasoning is recorded next to the grant: each one answers only whether the current user is a member of a given organisation, takes no action, and returns a boolean. Called with someone else’s organisation id it reveals nothing beyond false.
Hiding a field is not withholding it
Pulse can share a shortlist with a client through a link, and the recruiter chooses what is on it. The first version of that returned the candidate’s profile links and photographs to anyone holding the link, with no way to withhold anything and no expiry.
The fix is the interesting part. A withheld field is not sent as empty and it is not hidden by the page. It is removed from the response entirely, in the database function, before anything reaches the network. A field concealed in the interface has still been transmitted to the browser, which is not withholding it, it is decorating it. The public page cannot leak a withheld field even by accident, because it never receives one.
Links now expire after thirty days by default. A shared link that never expires is a permanent disclosure of somebody else’s personal data, and thirty days is roughly how long a shortlist is a live document.
The three tables with no organisation id
There is an exception, and it is the kind that confirms the rule rather than the kind that erodes it.
Three tables take writes from the public site: contact messages, waitlist signups, and status incidents. None of them carries an organisation id, because the person writing to them has no session and is not a member of anything. There is nothing to scope by, and inventing a value would be a lie about where the row came from.
So the boundary moves rather than disappearing. Security is enabled on those tables with no policy at all, which means zero rows for every client role. The only write path is a single privileged function that validates its inputs. And then the table-level grants were revoked as well, which is the part worth copying: with security enabled and no policies, the only thing standing between a stranger and the contact inbox is the absence of a policy, so somebody adding a permissive policy later by accident opens it with nothing else to catch them. Revoking the grant means two independent mistakes are required instead of one.
That is stricter than the tenant tables, not weaker. Status incidents are the single deliberate opening: anonymous readers may select published rows, because a status page that requires a session is not a status page.
What this costs
It is not free and it would be dishonest to pretend otherwise.
Every write that touches two or more tables has to be a database function, because the client library has no transactions and a sequence of separate writes is a bug even on the days it works. That is more SQL than most teams write. Policies have a query cost. Debugging is harder, because a query returning nothing might be a bug in your filter or the database correctly refusing you, and the two look identical from the application side.
The trade is straightforward. Those costs are paid continuously by us, in development. The alternative cost is paid once, by a candidate who never agreed to any of this, and it is not recoverable.
Retrofitting it later is not the same thing either. Adding tenancy to a table that already holds production rows means backfilling an organisation id you have to infer, on data where the inference is sometimes wrong. Starting with the column is nearly free. Adding it in year two is a migration nobody wants to run on a Friday.