Row-level security, and why it belongs in the database

Row-level security is the answer to a question every application with more than one customer has to answer: of all the rows in this table, which ones is this particular person allowed to touch? Not “may this person read invoices” — that is a coarser question about the endpoint — but “may this person read this invoice”.

The distinction sounds pedantic until you notice that most data leaks are not break-ins. They are ordinary, authenticated users receiving rows that were never meant for them, from a query nobody thought about twice.

Two questions that get confused for one

Access control in a typical application is really two decisions stacked on top of each other, and they fail in different ways.

• Method-level access: may this caller use this operation at all? “Any signed-in user may read the bookings collection. Only an administrator may delete one.” This is a yes-or-no decision made before any data is read, and it is usually driven by a role on the user’s login token.
• Row-level access: given that they may read bookings, which bookings? “The ones for their own company.” This is a filter applied to the data itself, and it can only be answered by looking at how the record relates to the person asking.

Teams reach for roles to solve both, because roles are the tool that is already there. It works right up to the first customer who wants two of their own users to see different subsets of their own data — at which point you are inventing a role per customer, and the role list has become a slow, badly indexed copy of your data model.

The three places the rule ends up

In practice, record-level rules end up in one of three places, and they are not equivalent.

• In the interface. The screen only ever shows the current user’s records, so nobody sees anything they should not. This is not security; it is layout. The underlying request can be re-issued by hand with a different id in it, and everything the interface was hiding comes back.
• In the endpoint code. Every handler adds a condition — `where company_id = current_user.company_id` — before it runs the query. This is real enforcement, and it works. It also has to be repeated in every handler, forever, by everyone, including the person adding a CSV export at half past four on a Friday.
• At the data. The rule is declared once, against the relationship that makes a record “yours”, and the database applies it to every query on that table regardless of which piece of code asked. There is no handler to forget, because the filter is not in the handler.

The second option is the common one and it is worth being precise about why it disappoints. It is not wrong. It is correct on the day it is written and it decays: an endpoint that was safe stays safe, but the surface keeps growing — a reporting query, an admin screen, a webhook, a scheduled job, an AI assistant that queries the same tables. Each one is a fresh opportunity to leave the condition out, and nothing in the system objects when you do.

The rule is a path through your data

The reason record-level rules resist being expressed as roles is that “yours” is almost never a property of the record. It is a path: this task belongs to a project, which belongs to a team, which you are a member of. Ownership is two or three relationship hops away, and it changes when somebody joins or leaves.

That is the shape a good row-level mechanism takes as its input. You point at the relationship that carries ownership, and every query on the collection is narrowed to the records reachable from the person asking. You are describing your data model rather than enumerating cases, which is why it keeps working when the model grows.

It also gives you something the role-per-customer approach cannot: the same person can hold different rights on different records. Organiser of one tournament, ordinary member of the next, and their login token never changes — because the right is recorded on the membership, not on the person.

Multi-tenancy is this problem wearing a suit

Multi-tenancy — one running system serving many customers who must never see each other — reads like an architecture decision, and gets discussed as one: separate databases per customer, or one database with a tenant column. But the second option is exactly the problem above, and the first is often chosen mainly to avoid having to solve it.

Separate databases per customer are genuinely safer against this one failure mode, and they cost you everywhere else: migrations to run per tenant, backups to manage per tenant, cross-customer reporting that becomes a project, and a per-customer setup cost that quietly sets a floor under how small a customer you can afford to take. If the row-level rule holds at the data, a shared database stops being the risky option.

How to tell whether yours actually holds

Whatever mechanism you use, these are the checks worth doing before you promise a customer that their data is isolated. All of them are half an hour of work and most of them find something the first time.

• Ask for a record you should not have. Sign in as one customer, take the id of another customer’s record, and request it directly. You should get nothing — not an empty screen, an empty response.
• Try every door, not just the front one. The list endpoint is usually correct because somebody tested it. Check single-record reads, search, exports, reports, aggregate counts and any real-time subscription separately. Counts are a favourite: “42 results” leaks the existence of rows the list hides.
• Check the write side too. Reads get the attention; updates and deletes are what change somebody else’s data. Try updating a record belonging to another tenant, and try creating a record that points at one.
• Check what happens when the link is missing. A record whose owning relationship is empty is nobody’s row. Depending on the mechanism, that means it is invisible to everyone or visible to everyone — and only one of those is safe. Find out which you have.
• Watch what the check costs. If the rule is applied by joining across relationships, an over-broad configuration can turn one read into a great many joins. Correct but slow is a problem you will meet in production, not in testing.

How this works on our platform

For completeness, since you may be reading this on the site of a company that sells one: on RestAPI.com the row-level rule is a security policy declared on the relationship that carries ownership, and it is applied to every query on that collection. Method-level access stays where it belongs — roles on the token, checked before any data is read — and the two are configured separately because they are different questions.

The part worth stealing even if you never use our platform: the rule is declared against the data model, not written into handlers. Whatever database or framework you are on, that is the property that decides whether your isolation is a guarantee or a habit.