A school platform wanted a natural-language assistant over its records, and the brief called it retrieval. The example questions supplied with the brief asked things like how a particular child's attendance looked this week. There is no document behind a question like that and nothing to embed. It is a live database query, run on behalf of a parent or a teacher, against records about children. The only thing that decides whether the answer is about the right child is the permission boundary.
A row-level policy is only as good as the context it is evaluated in.
A database with row-level security lets you attach a policy to a table. The policy is a condition evaluated against each row, using facts about who is asking: this row is visible if its pupil belongs to the requesting parent, for example. When a query runs, the database quietly adds the policy to it as an extra condition, so rows that fail the test are simply absent from the result. Nothing errors. The query returns fewer rows, and the code that issued it cannot tell that anything was filtered.
To evaluate that condition the database needs to know who is asking, and this is the weak point. The database itself only knows which database role opened the connection. A web application usually serves every user through a small pool of shared connections under the same role, so it has to tell the database who the end user is, typically by setting a variable that the policy reads. That variable can be set for the whole session or for a single transaction. The policy is only as correct as that variable is at the moment the query runs, and on a shared connection the session can belong to whoever used it last.
Policies also apply only in certain contexts. In common implementations, the role that owns a table and any role with a bypass privilege are exempt unless the policy is explicitly forced on them. A view normally runs with the privileges of the role that defined it, so a query through a view may be judged as the view's owner. Functions can be declared to run with their definer's rights. Each of these defaults makes sense for some other purpose. Together they mean the same policy text can be fully enforced, partly enforced or ignored depending on how the query arrived, and every one of those outcomes looks like a normal result. The exact defaults vary by database product and version and have to be checked on the one in use.
- Parent asks a questionin natural language, through the assistant
- Identity set for this transactionwho is asking, scoped to this request only
- Model proposes a queryit chooses what to ask for, nothing more
- Database checks role and pathowner, bypass and view rules decide if the policy applies
- Policy filters the rowsrows for other children are absent, not refused
- Only permitted rows returnthe model can only describe what it received
A rule written into the model's instructions is a request made to the very component it is meant to constrain, and the space of inputs that might talk the model out of it is all of natural language. So the control has to live in the database. That move is necessary and it is not sufficient. Row-level security is evaluated in a context: which role connected, which identity was set, whether the query arrived through a view. The context at runtime is often different from the one the developer pictured when writing the policy. The engineering is proving that the lock holds below the model, in the contexts that actually occur.
Permission leaks rarely look like errors. They look like answers.
When access control fails inside a database, the usual symptom is not a refusal or a crash. It is a result with extra rows in it. The query succeeds, the application formats the result, and the user sees a plausible answer. An assistant makes this worse, because a model will turn whatever rows it receives into fluent, confident prose about a child the user has no right to see.
The other pattern is testing in the wrong direction. Teams write tests confirming that a user can see their own data, because that is what the feature is for. Every leak described here leaves that test passing. A boundary is defined by what it keeps out, and a test suite that never asks for something forbidden says nothing about it.
The reflex, and the fix.
Write the rule into the system prompt
It reads like a rule, it takes one line, and it behaves correctly in every example anyone thinks to try.
The model is the component being constrained, so the instruction is a polite request. Its input space is unbounded, which means it cannot be tested exhaustively, and nothing about it can be proven to hold.
Enforce below the model, then prove each leak path closed
A least-privileged database role, policies forced on the table owner, identity scoped to each transaction, and a test suite written around the records that must not come back.
A boundary that holds whatever the model asks for, with each known leak path first demonstrated and then shown to be closed.
The model decides what to ask for. The database decides what it is allowed to receive.
A demonstrator before a design
During scoping, eight permission checks were run against a correctly written policy, as a reproduction on a test setup. Three of them returned another child's complete record. That made the case for building the boundary deliberately, because it showed that a correct policy is not enough on its own.
Forcing the policy on the owner
By default, the role that owns a table is exempt from that table's policies. Applications very often connect as the role that ran the migrations, which is usually the owner. Forcing the policy to apply to the owner as well turns that common configuration mistake into a non-event.
Views that run as their caller
A view runs with the privileges of whoever defined it unless it is told otherwise. A convenience view added to make queries simpler can therefore route around the policy, with no error anywhere. Views are made to evaluate as the user who is asking.
Identity that ends with the transaction
Identity set for a whole database session survives on a pooled connection into the next request. Any code path that forgets to set it then answers for the previous user, and the answer is well formed and plausible. Scoped to the transaction, a forgotten setting produces nothing at all.
Tests written around the negative case
A test that checks the right parent sees their own child passes in all three leaking configurations, because a leak adds rows and takes none away. The test that tells a working boundary from a broken one asks for another child's record and expects an empty result.
What each choice cost, and what it bought.
ATreating the brief as a permissions engagement
The brief described retrieval, and building retrieval would have matched it. Reading the example questions showed that most of them were about live records, which made the retrieval layer small and the access boundary the main body of work. Re-scoping cost an awkward conversation early on. It avoided building a polished search layer on top of a boundary nobody had tested, over records about children.
BRunning a demonstrator before designing anything
The argument that a correctly written policy might leak is easy to wave away. Showing it settles the matter. The demonstrator cost scoping time, and what it produced was a reproduction on a test setup, never a finding about the client's live system. What it bought was agreement that each leak path had to be closed and proven individually, and a concrete set of cases to prove them against.
CScoping identity to the transaction
Session-scoped identity is simpler to wire up and is how many examples do it. On a pooled connection, it means a request can inherit the previous request's user. Transaction scoping requires every query path to set identity explicitly, every time, which costs discipline throughout the application code. In return the failure mode changes from someone else's child to an empty result, and an empty result is a bug report, not a disclosure.
DMaking the negative case the core of the suite
Tests about what must not come back are less natural to write. Each one needs a second identity and a target record that deliberately belongs to someone else. They are also the only tests that distinguish a working boundary from a broken one, so they became the acceptance criteria, and the tests that a user sees their own data became the supporting cast.
A single headline number hides where a system fails. This work was scored on the dimensions that actually decide whether it holds in production, measured on real, held-out cases rather than the demo path.
The eight-check demonstrator is a reproduction run during scoping, with its method stated. It is not a client outcome.
An assistant that answers the question correctly, about the wrong child, with the policy written correctly and the connection pool still holding the previous parent's identity.
A permission boundary that sits below the model: the owner exemption forced off, views evaluated as their caller, identity scoped to each transaction, a least-privileged role, and a test suite whose assertions are about what must never be returned.
What it owns, and what it hands to a person.
Eight checks is a demonstrator, not an audit. It showed, three times over, that a correctly written policy is not sufficient on its own, and it does not enumerate every leak path. Functions, triggers and extensions that run with elevated privileges are further surfaces this work does not claim to have covered, and so is disclosure by the application after a correct retrieval. The defaults described here differ between database products and versions, so each one has to be re-verified against the database actually deployed.
When an assistant answers from live records, the retrieval work is small and the permission boundary is the engagement. Enforce access below the model. Assume the policy will be evaluated in a context you have not checked, and then check it. Write the tests about what must never come back, because tests about what should come back keep passing when the boundary is broken.
A rule in a model's instructions is a request to the component it is meant to constrain. Put access control in the data layer, below the model.
Row-level security is evaluated in a context of role, identity and query path, and each of those can quietly differ from what the policy's author assumed.
On a pooled connection, identity set for the session can outlive the request. Scope it to the transaction so that a missed setting returns nothing.
Table owners and definer-rights views are commonly exempt from row policies by default. Check which role your application connects as and how your views run.
Test a boundary by asking for what must not come back. A test that a user can see their own data passes when the boundary is broken.