Row-level security inside the database engine

Most applications decide who sees which rows in application code. A property owner should see only their own leases, so the leases endpoint adds WHERE owner = ? to its query. That works for as long as every path into the data remembers to add it.

Then the paths multiply. Someone adds a reporting endpoint, then a CSV export, then a nightly job, then an AI agent that is allowed to write SQL. Each one needs the same clause written again, and a path that forgets it returns every row in the table. Nothing fails loudly when that happens; the wrong people see the wrong data.

InventDB takes the rule out of the application. An administrator attaches a row rule to a grant once, and the database engine applies it to every query and every write made on that person's behalf. This article explains how that works, what the rules can express, what happens when a rule cannot be applied cleanly, and what it costs.

The caller travels with the request

When a request is authenticated, the server attaches the caller to the work it is about to do: who they are, their role, and the app they signed in through. The engine reads that identity at its two entry points, the place where SQL is executed and the place where records are written.

Every SQL query and every write passes through one of those two places, whichever interface sent it: the REST API, SQL over HTTP, the tools of the built-in MCP server, forms, file uploads, workflow steps and AI-generated change sets. None of them carries its own copy of the rule, so none of them can forget it. Fetching a single record by its id over the REST API is a lookup rather than a query, and the API checks the row rule on that lookup before it returns the record.

Two kinds of caller are not filtered. A request with no caller attached is the system itself, such as index maintenance or a scheduled backup. Administrators are not filtered either, by design, because they are the people who write the rules. Everyone else is filtered on every call.

Two ways to write a rule

A rule belongs to a grant. A grant gives a person or a role read, write, delete or admin access to a namespace or to one type, and it can carry a rule that narrows which rows that access covers. InventDB accepts rules in two forms.

The simple form compares columns with values, joined with AND, OR, NOT and parentheses. It has no functions and no subqueries, which makes it quick to read and hard to get wrong:

owner = :current_username AND status != 'archived'

The SQL form accepts any SQL boolean expression: comparisons, IN, LIKE, BETWEEN, IS NULL, an allowlist of string, date, numeric and aggregate functions, and subqueries. This rule lets owners see leases on the properties they own, even though the owner is recorded on the property rather than on the lease:

property_id IN (
  SELECT property_id FROM pms.properties
  WHERE owner_id = :current_username
)

A grant may carry both forms, and then a row must satisfy both. Rules refer to the caller through binds:

Bind	Value
:current_user	The person's record id.
:current_username	Their username, which is what business columns usually store.
:current_role	Their role.
:current_app	The app they signed in through.
:user_region, :user_department	Any attribute an administrator has set on the person, one bind per attribute.

Binds are substituted after the rule has been parsed, so a value always stays in its literal position. A username containing a quote or a fragment of SQL cannot change the shape of the rule.

Reads: the rule becomes part of the query

Before a SELECT is planned, the engine lists every table the statement reads, including the tables inside its subqueries, and asks for the caller's rule on each one. There are three possible answers: no restriction, a restriction, or no access. A restriction is joined to the statement's WHERE clause with AND. No access stops the query with a 403, so a subquery cannot be used to peek into a table the caller has no grant on.

Ann owns two properties. She runs this query:

SELECT unit, tenant, rent FROM pms.leases WHERE status = 'Active'

The engine plans and runs this one:

SELECT unit, tenant, rent FROM pms.leases
WHERE status = 'Active'
  AND property_id IN (
    SELECT property_id FROM pms.properties
    WHERE owner_id = 'ann'
  )

If Ann holds two grants on the same type, for example one given to her directly and one through a role, their rules are combined with OR: either one allowing a row is enough. A grant with no rule at all makes the type unrestricted for her. Results streamed back over a long response stay filtered until the last row is sent.

Writes: checked where they land

Returning the right rows is half of the job. A person who can see only their own rows must also be unable to create, change or delete anyone else's, and that check has to hold on every write path, including bulk loads and transactions.

The engine checks records at its write entry points: single inserts, updates and deletes, batches, bulk loads, and writes inside transactions.

• An insert is checked against the new record.
• An update is checked against the stored record and against the record as it will be stored. A person cannot change a row outside their rule, and cannot change a row in a way that moves it outside their rule.
• A delete is checked against the stored record.
• A batch is checked as a whole before any of it is written. If one record is refused, the call fails with a 403 and nothing in the batch is stored.

A SQL rule with a subquery cannot be evaluated against a record that has not been written yet. For writes, the engine therefore runs the rule's subqueries once per call, with the caller's binds filled in, and then tests each record in memory against the result. A 200-record bulk insert costs one run of the subquery, not 200.

Rules that follow relationships

In practice most access depends on a relationship rather than a column on the row. An owner sees a lease because they own the property. A clinician sees a patient because they have an appointment with them. SQL rules say this directly. These are rules from our test scenarios:

Two properties of these rules make them practical to run a business on.

The rule reads with its own authority. A rule's subqueries run as the policy, not as the caller. The technician's rule reads the visits table to find today's route, but the technician has no grant on the visits table and cannot query it directly. Assignment tables stay hidden from the people they control.

Access becomes data. Giving someone access to a client is an insert into the client team table rather than an edit to a role, and removing the row removes the access. Rules that compare dates end access on their own: the auditor's view of the evidence closes the day after the engagement ends. The rule is evaluated on every query, so there is no cache of permissions to invalidate.

When in doubt, deny

A security rule is only as good as its behaviour at the edges. Each of these cases denies rather than guesses:

• A rule that does not parse is refused when it is saved, with a 400 and the parse error.
• A rule that names a bind the person does not have, such as :user_region for someone without a region, denies access through that grant. A missing value never widens access.
• A correlated subquery, one that refers to the outer row, is refused with a message explaining how to write it as column IN (SELECT ...).
• A rule may not read the engine's internal _system tables, and subquery nesting is capped.
• Any condition the evaluator cannot decide is treated as a refusal, and a query plan that contains one is refused rather than run unfiltered.

Rules are written only by administrators. Because a rule's subqueries run with the rule's authority, a rule could reveal what is in a table, and that power belongs with the people who already see everything.

What it costs

A rule is extra work on every query of the type it governs. In our measurements a simple column rule added a few milliseconds to a query, and a rule with a subquery added the time to run that subquery once per query. On writes, resolving grants and checking the record added under 10 milliseconds to a single insert, and 200-row bulk inserts showed no per-row cost.

The enforcement code also sits on queries that have no rule at all, so we measured it against a control build on the same machine, interleaved, over a 10-million-record dataset. On our 3,511-query battery it changed total time by −0.9% single-threaded and +0.7% with four threads, and every query returned the same rows on both builds. Our benchmarks page describes how these runs are set up.

How we test it

Row security is easy to demonstrate and easy to get wrong at the edges, so the test suite builds eight complete scenarios, one per industry: property management, clinics, agencies, legal and accounting, contractors, home services, wholesale and compliance. Each has real people, roles and data. For each scenario the suite checks:

• reads return exactly the rows the rule allows;
• writes inside the rule succeed and writes outside it are refused;
• deletes, bulk inserts and bulk deletes are allowed whole or refused whole;
• the rule's own assignment tables stay hidden from the person;
• a person whose subquery comes back empty sees nothing and can change nothing.

Every rule is checked twice, once granted through a role and once granted directly to the person, because the two paths are resolved separately. The current run passes all 205 checks.

Setting a rule

In InventDB SOAR, rules are set under Team access. Pick a role or a person, add a simple or SQL rule, and use Preview as to see how many rows a real person would get before you save, for example "ann would see 2 of 3 rows". You can also describe the access you want in plain English, such as "owners see only the properties they own", and the AI role drafter proposes a rule for you to review.

Over the API, a rule is part of the grant:

POST /api/permissions/ann
Authorization: Bearer <admin token>
Content-Type: application/json

{
  "scope": "type",
  "resource": "pms.leases",
  "accessLevel": "write",
  "rowFilterSql": "property_id IN (SELECT property_id FROM pms.properties WHERE owner_id = :current_username)"
}

The same rules apply on InventDB Serverless and InventDB SOAR, because both run on the same engine. An AI agent connected over MCP signs in as a person, and the SQL it runs and the changes it makes are held to that person's rules.