Row Level Security

When several customer companies share one system, rows belonging to different companies sit side by side in the same table. Company A's documents and company B's documents are neighbours, and the only thing separating them is a team identifier written on each row.

In that arrangement, every read has to carry a condition on that identifier. Drop one condition and another company's data appears on screen. It happened at Pagedeck. Building a new view that listed documents by most recently edited, Theo reworked the sort and deleted the team condition along with it. The test account held data for only one team, so nothing looked wrong, and 40 minutes after release a customer reported seeing another team's document titles in their list.

The real problem is the structure that requires the same condition to be pasted into dozens of places in code. A rule that breaks when one site is missed will eventually be missed, and the miss will happen in the least-reviewed query rather than the most obvious one. Hence moving the rule down from the code into the database, where it gets written once.

Write a policy per table, and give the database a way to know who is asking. From then on the condition attaches itself to every query.

1. Choose the tables. Any table with a column identifying a company or a user is in scope. Tables everyone may read, like shared configuration or country lists, stay out — and writing down why each one is out keeps the next person from adding a policy nobody needed.
2. Enable the feature and let the default be denial. Switching it on makes a table with no policy invisible to everyone. Permissions get added one at a time, which means a forgotten policy produces an empty screen rather than a leak.
3. Pass through who is asking. On each database connection, set a value describing whose query this is — the signed-in user's team identifier, held for the life of that connection. The application decides that value once, at the edge, rather than in each query.
4. Write the policies, one per operation: select, insert, update, delete. Reads see a row only when its team identifier matches the current value; inserts are blocked from writing a row under another team's identifier. Cover reads and skip inserts and people can write data under someone else's name.
5. Keep a deliberate bypass route. Aggregation jobs and customer support tools legitimately need to see several teams, so they run under a role that bypasses the policies. Concentrate that code in one place and log who used it when, because this is the one path where a mistake looks exactly like normal operation.
6. Verify with tests. Load data for two teams, query as one of them, and assert that rows belonging to the other come back as zero. Two such lines per table will surface any table whose policy was forgotten before release. Without them, the first evidence that a policy is missing is a customer describing what they saw.
7. Index the column the condition lands on. A policy adds one condition to every query, so without an index on the team identifier you gain full-table scans on tables that used to be fast.

It fits multi-tenancy — several customer companies sharing one system — better than anything else. The value grows with the number of paths that read the data. If the web app, the mobile app, an aggregation job and an internal tool all read the same table, the condition gets written once instead of four times. Where something connects straight to the database with no application in between, this is effectively the only option, because there's no code in which to put a condition.

There are four limits.

1. Policies get hard to read as they multiply. Twenty tables with four operations each is eighty rules, and working out which one is hiding a row is real work. Naming each policy after its table, operation and condition helps here more than documentation does.
2. Values can linger where connections are reused. In a pooled setup, returning a connection without clearing the user value leaves the next request running under the previous person's identity. Build the clearing step into the return path, not as a separate habit.
3. It doesn't replace feature permissions. This decides which rows are visible. Who within a team may delete a document belongs to role-based access control (RBAC), which binds permissions to roles.
4. Not every database offers it. Relational databases generally do, though the expressions allowed differ by product, and document-oriented systems frequently have no equivalent.

"One missing team condition exposed another team's documents. Let's block this in the database."

The same fault keeps recurring in code, so move the rule down a layer.

"There's a select policy and no insert policy."

Reads are closed and writes are open.

"The aggregation job runs under the bypass role, so that code needs its own review."

Naming a path where the policies don't apply.

After the incident, Theo added policies to three tables: documents, comments and attachments. Four operations each made twelve policies, and each table got two test lines running against two teams' data. New views stopped turning missing conditions into incidents. Twice after a release a screen did come up empty, both times a newly created table with no policy written yet. Failing toward blocked rather than toward leaking is part of why the approach was chosen.

The most common reading is that enabling this removes the need for permission checks in the application. The database filters, so checking again above it looks redundant. But what gets filtered is which rows are visible. The person who can read a team document and the person who can delete it are different, and that distinction lives above the database. The bypass role is the other half: aggregation and support tooling run under it, so permission checks on those paths remain the application's job.

The other belief is that performance rules it out. "A condition on every query" sounds expensive. What actually attaches is one condition, and with an index on that column the difference against a hand-written condition is small. What does hurt is a policy that has to consult other tables — resolve permissions through three levels of lookup and that work rides along on every read, including the ones that return a single row. In that case, denormalise what the decision needs onto the row itself so the policy can settle it by reading one column.