Grants are enforced by a component that has never seen a prompt

A support form is a place where a stranger can write a sentence that later arrives inside a prompt. Nothing exotic has to happen for that: a customer types free text into a field, a system stores it, and months later a retriever pulls stored text into a model's context because it matched a question. Retrieval does not distinguish between a description of a billing problem and an instruction addressed to a model. It matches on relevance, and a sentence written to be obeyed is as retrievable as any other.

That is the mechanism making the rest of this chapter necessary, and it is worth naming before the defences are. A role is the identity a service connects to a database as, and a grant is a recorded permission attached to that role — this role may read that object. Neither has any notion of a conversation. The question this chapter answers is why an authorisation decision belongs there rather than in code that inspects the generated statement, and the short version is that a statement can be perfectly well formed and still name a table nobody should be able to reach through this system.

Consider a statement that satisfies every rule from the previous chapter — an allow-list over a parsed statement's node types, schemas and row bound:

SELECT * FROM analytics.payment_methods LIMIT 100

That statement is a single SELECT. It carries a LIMIT of 100, well under the cap. It touches one table, in the analytics schema, which is on the allow-list. There is no DDL node, no DML node, no INTO, no second statement, no banned function. Every rule from the previous chapter passes, in about 3ms, and the validator is not broken — it enforced exactly what it claims to enforce, which is the shape of the statement. Shape was never the issue. The issue is that a table exists which this system should not be able to read, and nothing in the validator knows that.

The comparison that makes this concrete is the one the validator cannot make. Set that statement beside SELECT * FROM analytics.fct_orders LIMIT 100. Same node types, same depth, same statement count, same schema, same bound, same parse time. Every quantity the validator measures is identical across the pair, and one of the two is an exposure. That is the shape of the mistake worth carrying: a defence whose measurement holds still while the dangerous property — which object the read reaches, and whether this system is entitled to reach it — moves freely. Believing the validator covers this is tempting because the validator does look at table names. It looks at their schemas, which is a question about where a name lives, and entitlement is a question about who is asking.

Where each control actually lives#

Three things could have stopped this, and they differ in a way that matters more than their overlap.

The count in that sentence and the number of rows in the table do not match, and the mismatch is informative rather than an error. Four controls are listed; three of them could have stopped this statement. Network and warehouse policy is the fourth, and it governs where a connection may come from — which host, which network, which warehouse. Here the connection came from the legitimate service, over the legitimate path, as the legitimate role. There was nothing for infrastructure to refuse. It is in the table because the comparison is about where enforcement lives, and it is outside the count because a control that cannot see the failure cannot stop it.

Read the remaining rows as gaps rather than as a ranking. The gap between the first and second rows is a change of executor: the prompt is enforced by a model weighing text against text, the allow-list by a process running deterministic code, so the second is not persuadable by a sentence. That is a real improvement and it is the only one — both still fail invisibly, which is what the fourth column says. The gap between the second and third rows is the one that carries the argument, and it is a change of evidence. A permission failure produces permission denied for table payment_methods, in the database's own audit log, tied to a role and a session, at a timestamp, whether or not anyone had thought about prompt injection that week. The second row produces nothing at all when it fails, because a validator that accepts a statement it should have rejected has, from its own perspective, done its job.

That last clause deserves to be read twice, because it is the difference between a control and a hope. A validator's success and its silent failure are the same event: a statement goes through. There is no signal to count, no log line to alert on, and no way to distinguish the two afterwards except by knowing independently that the statement should not have run. A control whose failures are indistinguishable from its successes cannot be monitored, and anything that cannot be monitored cannot be relied on as the last one.

Every arrow above except the last two is behaving correctly. The retriever returned tickets because the question was about tickets. The model wrote SQL because that is what it was asked to do. The validator passed a statement that satisfies its rules. Only the warehouse refuses, and it refuses on a ground none of the components above it had any access to.

The parser gap is not hypothetical#

The reason to distrust the validator as a security boundary is not that its author was careless. It is that it delegates the hard part to a parser which models a dozen SQL dialects, each of which has constructs the others do not, and which has to make a decision about every statement it is handed.

The failure modes are ordinary library behaviour. A construct the parser does not recognise may come back as a generic exp.Command or exp.Anonymous node whose contents are an opaque string, and a walk() that checks node types sees a node type it has no rule for. Different dialect settings parse the same text differently, so a statement validated as snowflake and executed against a Postgres-wire-compatible endpoint has been checked against the wrong grammar. A parse error raises, and whatever the except clause does next is the actual policy — code that logs the exception and retries generation is fine, code that falls back to a regex check has quietly reverted to chapter 3's first version.

Each of those is one step long and each is checkable. A parser must return something for every input it is given, since a validator that only accepts statements the parser fully understands would refuse a large share of legitimate work. So an unrecognised construct gets a catch-all node. A catch-all node carries its contents as text rather than as structure. A type check over the tree therefore sees a node type it was never told about, and a deny-list treats an unlisted type as permitted. The gap is not a bug in the parser; it is the parser doing the only reasonable thing with input it cannot model, meeting a checking strategy that reads absence as consent.

None of these produce an alert. They produce a statement that reaches the database, and if the database is willing to run it, it runs.

Scoping the grants#

The role is only as good as what it can reach, and the common mistake makes it worthless in a way that takes months to notice.

-- what people write
CREATE ROLE bot_ro LOGIN PASSWORD :'pw';
GRANT USAGE ON SCHEMA analytics TO bot_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO bot_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO bot_ro;

Those four lines grant read on every table in the schema and, because of the default privileges, on every table anyone creates there in future. When a data engineer lands analytics.payment_methods_v2 next quarter as part of an unrelated migration, the bot can read it on the day it is created, and no one made that decision. The grant is deny-by-default in name and allow-by-default in behaviour.

The last statement is what converts a decision into a standing rule, and the distinction is worth holding. GRANT SELECT ON ALL TABLES is evaluated once, against the tables that exist when it runs; it is a decision about a known set. ALTER DEFAULT PRIVILEGES is a rule applied at creation time to objects nobody has seen yet, so it delegates every future access decision to whoever next writes a migration. That person is not making a security decision and does not know they are making one.

-- what holds
CREATE ROLE bot_ro LOGIN PASSWORD :'pw' CONNECTION LIMIT 4;
REVOKE ALL ON SCHEMA public, analytics FROM bot_ro;

CREATE SCHEMA analytics_marts;                  -- curated views only
GRANT USAGE ON SCHEMA analytics_marts TO bot_ro;

CREATE VIEW analytics_marts.orders AS
  SELECT order_id, customer_id, status, channel,
         order_total, currency_code, order_date
  FROM analytics.fct_orders
  WHERE is_deleted = FALSE AND is_test = FALSE;

CREATE VIEW analytics_marts.customers AS
  SELECT customer_id, segment, country_code, created_at,
         md5(email) AS email_hash          -- no raw email leaves this view
  FROM analytics.dim_customers
  WHERE is_internal = FALSE;

GRANT SELECT ON analytics_marts.orders, analytics_marts.customers TO bot_ro;
-- no ALTER DEFAULT PRIVILEGES: a new object is unreadable until granted
ALTER ROLE bot_ro SET statement_timeout = '30s';
ALTER ROLE bot_ro SET default_transaction_read_only = on;

Granting on views rather than base tables gives you a place to put the things that would otherwise be conventions the model is asked to remember. The soft-delete filter, the test-order exclusion and the internal-account exclusion move out of the prompt and into the view definition, where they cannot be forgotten by a generator that was distracted by a long question. The column list is the mechanism for column-level exclusion — email is not omitted from the prompt and hoped-for, it is absent from the object the role can select.

Setting the timeout and the read-only default on the role rather than on the connection string means they survive a code path that builds a connection some other way, which is the kind of thing that happens when someone adds a background job six months later.

Two of the values in that block match settings the previous chapter put on the connection pool, and the agreement is the point rather than a duplication. '30s' is the same quantity as the pool's statement_timeout, which that chapter writes as a bare integer of milliseconds; the connection limit here matches the pool's maximum size. In both cases the pool sets the value for connections it makes and the role sets it for every connection made as that role, so the role's copy is what binds when a background job builds its own. There is a residual worth knowing: CONNECTION LIMIT counts concurrent sessions for the role across everything using it, while a pool's maximum applies to that pool alone. Point a second service at bot_ro and the two stop describing the same quantity, and the role's limit is the one that starts refusing connections.

One line in that block is narrower than it looks. md5(email) AS email_hash does exactly what its comment says: no raw address leaves the view. What it does not do is make the column uninformative. The value is stable for a given address, so it still joins across anything else holding the same transformation, and it still answers is this particular address present for any address somebody can supply, since they can compute the same value themselves. The column excludes the address from being read out. It does not prevent an address that is already known from being confirmed, and treating a stable transformation of an identifier as though it were the absence of the identifier is a common step from a true claim to a false one.

There is also a verification step worth insisting on rather than a line worth rewriting. Revoking a privilege from a named role removes what that role was granted directly, and it does not touch a privilege the role holds because it was granted to PUBLIC, which every role is a member of. The reliable check is therefore not to read the grant script and conclude, but to connect as bot_ro and ask the engine what it can actually see. A grant script is a statement of intent; the engine's answer is the state.

The tenant boundary is not a WHERE clause#

A role scoped to a schema is not scoped to a tenant, and in any product where the asker should see only their own organisation's data, the entire access decision otherwise rests on the generator remembering a predicate. It will remember it most of the time, which is the worst possible reliability for an authorisation control. The mechanism that removes the choice is row-level security, a Postgres feature in which the engine attaches a policy predicate to every read of a table, so the filter is applied whether or not the statement asked for it.

Most of the time is worse than rarely, and the reason is about detection rather than about frequency. A control that fails often is discovered in testing. A control that succeeds on nearly every question fails on the questions nobody thought to try, produces a plausible answer when it does, and gives a team months of evidence that it works before the first person sees another organisation's numbers.

ALTER TABLE analytics.fct_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE analytics.fct_orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON analytics.fct_orders
  FOR SELECT TO bot_ro
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

@POOL.connection_hook
def scope(conn, tenant_id: int) -> None:
    # set on checkout, inside the transaction, from the session — never from the model
    conn.execute("SELECT set_config('app.tenant_id', %s, true)", (str(tenant_id),))

The true argument makes the setting transaction-local, so a pooled connection returned and handed to a different tenant's request cannot carry the previous value. FORCE ROW LEVEL SECURITY closes the case where the table's owner is also the querying role, which is easy to arrive at accidentally in a warehouse where one service account created everything.

Both of those exist because a policy binds to a role, and which role a read runs as is less obvious than it looks. That is the condition to check against the grants above: the policy names bot_ro, while the grants give bot_ro nothing on analytics.fct_orders and route it through analytics_marts.orders instead. A view is executed with its owner's privileges unless it has been created to run with the caller's, so a read arriving through the view is evaluated against the owner rather than against bot_ro, and a policy written TO bot_ro is not the policy that applies. The fix is a choice — attach the policy to the role the read actually runs as, or make the view run as its caller — and the thing to insist on is a test that queries through the same path the agent uses and confirms the filter is present, rather than one that queries the base table directly and concludes.

With the policy in place, a generated statement that omits WHERE tenant_id = ... returns that tenant's rows rather than everyone's, and a statement that names another tenant's id explicitly returns nothing. The tenant id comes from the authenticated session — the Slack workspace, the signed JWT — and never from anything the model produced or anything a retrieved document said.

That last sentence is the whole of the design. Every other value in the statement was chosen by a model reading text that a stranger may have written. The tenant id is the one value taken from a channel the stranger cannot write to, which is why it is the one value an authorisation decision may rest on.

Watch the refusals#

A permission error is an event worth alerting on, which is unusual among database errors. In normal operation bot_ro should never touch an object it lacks a grant for, because the tables in the prompt are exactly the tables it can read. A 42501 in the logs therefore means one of three things: the retriever surfaced a table outside the grant, someone changed the grants, or the model wrote a statement about a table that was never in its prompt at all. All three are worth a page rather than a dashboard tile, and the third is the injection case.

The reason the alarm is worth wiring at all is the invariant in the second sentence. The prompt is built from the objects the role can read, so under normal operation the set of tables a statement can name and the set the role can read are the same set, and a permission error is impossible. Any breach of that equality means one of the two sets moved, and the three causes above are the three ways it can move. An alarm is only as good as the invariant behind it, and this one is exact rather than statistical, which is why a single occurrence is worth a person rather than a trend line.

ALTER ROLE bot_ro SET application_name = 'textsql-bot';
-- every statement carries an answer id for correlation
SET LOCAL application_name = 'textsql-bot:ans_01J9F2K4';

Tagging each session with the answer identifier links the warehouse's own query history to the application's record of what was asked, who asked it and which prompt produced the statement. When a permission denial fires, the investigation starts with a question and a prompt rather than with a fragment of SQL and a timestamp, and that difference is the gap between a twenty-minute review and a day of archaeology.

Credentials belong in the same conversation. bot_ro's password is the one secret whose leak makes every control in this chapter irrelevant, and it should be short-lived: an IAM-issued token — identity and access management, the cloud provider's own credential service — or a vault lease measured in hours, rotated automatically, with the pool re-authenticating on expiry. A static password in an environment variable that has not changed since the service was deployed is the practical bypass, and it does not require any understanding of SQL to use.

What the user sees when the engine refuses#

The database's error text names a table, and naming it to the user tells them that a table called payment_methods exists, which is information the refusal was supposed to withhold. The mapping is therefore fixed and deliberate: log the full error with the answer identifier, and return a message that describes the boundary rather than the object — "I can only read the certified marts, and that question needs data outside them; here is what I can see." That message includes the list of tables the bot does have, which turns a dead end into a redirect and generates a steady trickle of useful requests to the data team for marts that ought to exist.

Naming the readable tables while withholding the refused one is consistent rather than contradictory, and the reason is which direction the information travels. The readable set is already in the model's prompt and already in every answer the system gives, so it is public to anyone using the product. The refused name is not, and confirming it turns a guess into a fact.

Why both, and what each one is for#

The read-only role does not make the AST validator redundant either, which is the mirror image of the argument and worth stating so the pair is understood rather than the role treated as the only real control.

The validator produces a good failure. A rejected statement can be reported to the user as "I generated a query that reads outside the tables I'm allowed to use", can be counted, and can be fed back into generation for a repair attempt. A permission error from the engine arrives as a driver exception halfway through execution, after the round trip, and the honest thing to show a user is a generic refusal, because the error text names a table that the user was not supposed to learn exists. The validator also catches statements the role would happily execute — a query with no LIMIT over 2.4 million rows is fully within bot_ro's grants and will still take the service down.

There is a second asymmetry visible in the two chapters' settings. The validator's allow-list admits both analytics and analytics_marts, while the corrected grants give bot_ro nothing outside analytics_marts. The two controls disagree about how wide the permitted area is, and that disagreement is the arrangement working: the narrower one binds, and the wider one is there to give a well-formed refusal for the cases it can see. A team that reconciles them by widening the grants to match the validator has inverted the whole design.

The grants also have a boundary that gets overstated in the other direction. A role scoped to twelve mart views does not stop data leaving through the data it is allowed to read. A question phrased as an aggregate over a group of one — revenue for the single customer in a segment, or a count filtered down until it identifies a person — returns permitted rows that disclose exactly what the column exclusion was meant to prevent. Differential-privacy-style minimum group sizes, or a rule refusing to report an aggregate over fewer than k rows, live above the engine and are a separate control with separate false positives. The role answers who may read which objects, and nothing about what may be inferred from them.

The step from a grant to a guarantee about disclosure is the one to refuse. A grant is a statement about objects, and every object-level control shares its ceiling: it can decide what may be read, and it cannot decide what a reader can work out from what they were allowed to read. An aggregate is built from permitted rows by permitted arithmetic, and there is no point in the chain at which anything forbidden occurs. Expecting the permission system to catch it is expecting a control to enforce a property it has no vocabulary for.

What the role gives you is that the guarantee holds when the layer above is wrong. The validator's correctness depends on a parser, a dialect setting, an exception handler and a deny-list that someone keeps current. The grant's correctness depends on the database's permission system, which has no notion of prompts, no notion of retrieved documents, and no way to be argued with by a customer typing into a support form.

That independence is the property to hold on to, and it is stronger than defence in depth usually is. Two controls stacked on the same assumption fail together, and the assumption shared by a system prompt and an AST allow-list is that the text arriving in the prompt is what somebody intended to be there. The grant does not rest on that assumption, because it never reads the text. It is checking a role against an object, and the answer it gives is the same answer whatever was written on the form.

Neither control has any view on whether an answer is right. bot_ro will execute a query summing an order-level total across line-item rows without hesitation, because reading analytics_marts.orders and analytics_marts.order_items is exactly what it is for. The engine enforces access. The number that comes back is somebody else's problem, and the class of bug it hides is the one that survives everything built so far.