Whose query is it?

Both systems are written up on the home page: the customer portal and the AI data layer.

The day before I presented our customer portal's security to a customer's technical team, a reviewer reading the slides stopped at one line. It said that even a compromised portal could not reach another customer's designs.

It was not true yet. Every customer's designs live in the same tables. What kept them apart was a filter in the application: each route resolved who had signed in, worked out which customer they belonged to, and added that customer to its query. It was careful code, and it was tested. But an attacker inside the portal would hold the same database credentials the portal held, and those credentials could read every customer's rows. The separation held only for as long as nothing went wrong with the one layer enforcing it.

We put row-level security into the warehouse before the session, and the line on the slide became true. That much is an ordinary story. What made me want to write it down is that the same week, building something that looked unrelated, I ran into the same problem from the other end.

The same mistake, from the other side

People at work wanted to ask Claude about the plant's data in plain language. The obvious build is a connector with a service account: give it read access to the warehouse, let the assistant write SQL, return the results. It works on the first afternoon.

It also means the assistant answers every person's question with everyone's data. Someone who cannot open the finance pages can ask the assistant about receivables, and the service account will read them without complaint. The access model the pages enforce, role by role, gets routed around by a chat window.

The portal and the assistant are the same mistake. In both, the database is asked a question by something that is not the person who wants the answer, so the database cannot apply any rule about that person. Whatever rules exist have to live upstream, in code, and hold perfectly, forever.

So the question I now ask of every connection is not whether the query is correct but whose query it is. If the honest answer is "a service account's", then every rule about who may see what is a promise the application is making on the database's behalf.

Carrying the person down

For the assistant, the fix was to make the query belong to the person. The connector signs the user in with the company's identity provider and exchanges that sign-in for a database token issued on their behalf. Every query then runs as them, and the warehouse applies their grants, not the assistant's.

That needed a server of my own. The vendor's hosted connector for the warehouse would not accept a token minted for a third-party client, so the endpoint lives inside our operator platform. Then the grants had to mean something. The assistant reads a curated layer of views, one schema per report, and raw tables are never granted. Saving someone's page permissions grants or revokes the matching schemas, so there is one permission list and the assistant inherits it.

The portal could not do quite the same thing, because customers are not database users. So the portal keeps one data identity of its own and tells the database which customer each request is acting for. The warehouse policy reads that value and filters every row by it. The policy applies only to the portal's identity, so internal tools see what they always saw.

Not knowing has to mean nothing

The decision that matters most in a policy like that is what happens when the value is missing. A route that reads data before working out who is asking, or a new route someone forgets to wire up, sends a query with no customer attached. The policy can treat that as no filter, or as no rows.

It returns no rows. A forgotten scope shows up as an empty page and a bug report rather than as every customer's designs. The bug is louder and the leak does not happen, which is the right trade when the data belongs to someone else.

Two details nearly broke that.

The first was connection pooling. A session value set once on a connection outlives the request that set it, and a pooled connection is handed to the next request as it is. Set the customer once and the next customer's request inherits it. So the scope is not set per connection. It is prefixed to every batch the portal sends, as an explicit empty value when there is no customer, so nothing stale can survive from one request to the next.

The second was caching. With the database filtering, a lookup made in the wrong scope gets an honest empty answer. Cache that answer under a key that ignores the scope and you serve "this design does not exist" to the customer who owns it. That is not a leak, but to the owner it looks like lost data. The ownership lookup in front of the filtered tables is keyed by scope now, and anything cached there has to be.

I did not apply "fail closed" everywhere, and I think it would have been wrong to. The portal also pins each email address to the one identity-provider account it first signed in with, so a second account claiming the same address is refused. If the store holding those pins is unreachable, sign-in is allowed and logged. The difference is in what is unknown. With a missing scope, I do not know whose query it is at all. With a missing pin store, the person has still signed in properly and I have only lost an extra check. Locking every customer out over an extra check trades a small risk for a certain outage.

The grants you are not looking at

Carrying the person down only works if the grants are the only door. Twice, they were not.

Members of the workspace the warehouse lives in get access through their workspace role, and that role reads past SQL grants entirely. Separately, a group was added to a default read role on the lake underneath, which did the same for everyone in the group. Neither shows up if you only test as an ordinary user, because an ordinary user has neither. The first is now closed in the server, which checks every schema a query names against the person's allowed list before it runs and refuses names that reach into another database. The second was closed by taking the group out of the role and keeping every group out of it.

What I took from that is that a permission model is the set of every path that can grant access, not the one I designed. The one I designed is usually the most carefully built of them, which is exactly why it is not where the gaps are.

Breaking it on purpose

The last piece is knowing any of this still holds next month. The portal's real route handlers now run in the build against a fake session and a fake warehouse that filters the way the real one does, and a failing isolation test blocks the deploy.

A test suite that has never failed has not shown that it can. So I broke the code on purpose. Removing the scope check from one helper turned three tests red. Never setting the customer at all turned nine of sixteen red. Those numbers matter more to me than the green run, because they are the evidence that the green run means something.

Whose query it is

Both systems now give the same answer. The assistant's queries belong to the person asking. The portal's queries carry the customer they act for, and without one they see nothing. Neither depends on every route being written perfectly, and that was the real claim on the slide: not that the code was careful, but that it no longer had to be.