Read-Only Data Access Patterns for Autonomous AI Agents

Flipping a database role to read-only is a permission setting borrowed from a world where humans write queries one at a time, read the result, and decide what to do next, not a safety measure for an autonomous agent. A human analyst submits a query deliberately and reviews it before running the next one. An autonomous agent, working through a reasoning loop, can generate and fire dozens of queries in a single pass, none of them reviewed by anyone before they hit the database. The risk this creates has nothing to do with whether the agent can write data. It has to do with whether the agent can be trusted to ask reasonable questions.

A read-only query can still lock rows, spike CPU, exhaust a connection pool, and slow the application down for every paying customer using it at that moment. Read-only permissions say nothing about query cost, since an agent has no built-in sense of what a query costs. It will run a full-table scan on a ten-million-row table with the same confidence it uses for a lookup by primary key. It will chain unbounded joins across tables it has never seen before. It will ask the same expensive question five times in one reasoning loop because nothing told it the answer was already in hand. None of this requires malice. It only requires a production schema, built for transactional writes and point reads, being asked to serve analytical query shapes it was never indexed or tuned for.

There's also an attack surface that read-only access does nothing to close. A row in a production table can contain a value planted specifically to manipulate an agent that later reads it, an instruction embedded in a customer note or a support ticket field, aimed at getting a model to exfiltrate or misuse data it was technically authorized to see. Permissions control what an agent is allowed to touch. They do not control what an agent decides to do once it touches it. Solving that problem means building structure around the agent, not just adjusting what it is permitted to request.

A governed data layer between agents and production

The structural fix is a governed data layer: a purpose-built tier that sits between agents and production, pre-modeling and pre-calculating the data agents are allowed to see before any agent ever asks a question. Production stays production. Agents never connect to it, query it, or see its schema. Everything an agent reads comes from a separate layer built specifically for that purpose.

A governed data layer, rather than a view defined on top of Postgres and queried live every time an agent asks something, is a distinct materialization, built in advance, that the production database never has to serve directly. The work of transforming raw transactional rows into something query-safe happens ahead of time, on a schedule the team controls, not at the moment an agent happens to ask a question.

The headless content management system is the analogy worth borrowing here. A headless CMS decouples content from any one front end, so the same article can be served to a website, a mobile app, and a kiosk display without any of them touching the authoring database directly. A governed data layer does the same job for business data: it decouples the data from any single consumer, whether that consumer is a dashboard, a quarterly report, or an autonomous agent, so none of them ever need direct access to the system that produces the data.

Governance, in this context, means four concrete things: a defined schema the layer commits to, defined logic for how each metric is calculated, defined rules for which rows and columns each consumer may see, and a trail that records who asked for what. Enforcement for all four lives in the layer itself, not in a system prompt or in an agent's willingness to behave. Because agents can only ask questions the layer already knows how to answer, the entire category of arbitrary, unreviewed SQL, the thing that makes direct database access dangerous in the first place, simply does not exist on this side of the boundary. The same structure also solves a quieter problem: a number like monthly recurring revenue or active users gets calculated once, in the layer, instead of being re-derived slightly differently by every analyst and every agent that asks for it.

Pre-calculated, modeled datasets as the safe unit of agent consumption

Once the layer exists, the next question is what, specifically, lives inside it. The right unit of consumption for an autonomous agent is a pre-calculated, modeled dataset, not open query access against normalized production tables. Normalized schemas, with their many tables, foreign keys, and nullable columns built to support transactional writes and point reads, are the wrong shape for a system trying to reason about trends, totals, and comparisons.

Pre-calculated datasets solve this by flattening and denormalizing the metrics that matter into wide, stable structures built for exactly this kind of question. An agent asking what revenue looked like last week, broken out by product line, should get back a row. It should not need to construct a multi-table join plan to get there, and it should not be trusted to construct one safely even if it could.

Schema stability matters for agents in a way it does not for most human-facing tools. A person adapts when a column disappears or a table gets renamed. An agent built against a dataset schema will fail silently the moment that schema shifts underneath it, often without an error surfacing that anyone notices right away. A governed dataset with explicit versioning closes that gap: the schema an agent depends on only changes when a team decides it should.

Pre-calculation changes the economics of the whole system. The computational cost of producing a dataset is paid once, at refresh time. At any meaningful scale, that difference is the gap between a system that stays affordable and one whose compute bill grows with every agent invocation. Columnar file formats fit this pattern well, Parquet being the common choice, because they compress efficiently, support predicate pushdown, and can be queried directly by embedded engines like DuckDB with no server running in the background. The design principle that produces this is simple to state: for every business question an agent might legitimately need to answer, there should be a named, documented dataset built to answer it. If that dataset does not exist yet, the agent should not be asking the question yet either.

Exposing governed datasets to agents through a controlled interface, not direct database connections

Building the right datasets solves half the problem. The other half is how agents actually reach them, and the answer is a narrow, purpose-built interface, never a raw database connection string. A connection string hands an agent everything at once: the ability to inspect the schema, run arbitrary queries, and probe tables it was never meant to touch. A controlled interface hands an agent nothing beyond what it has explicitly been offered.

The Model Context Protocol, known as MCP, is an established open standard built for exactly this pattern. A server exposes a set of named tools and resources, an agent calls them by name, and the server handles retrieval against the governed layer behind it. The agent never sees a connection string. It never sees a schema. It only sees the tools it has been given and whatever those tools choose to return. Where MCP is not yet a team's standard, a REST or GraphQL API can serve the same role. What matters is not the specific protocol but the shape of the boundary: the interface is the only way in, and there is no back channel around it.

An MCP server, or an API built the same way, also becomes the point where policy gets enforced. Row-level filters, rejection of out-of-bounds queries, rate limits, and logging of every call all happen here, none of which is possible once an agent has a direct line to the database itself. The tool and endpoint definitions themselves double as a governance record: they spell out, in a form humans can read, what questions an agent is authorized to ask, and that record can be reviewed, versioned, and audited the same way any other access policy would be.

The practical rule that follows from all this is straightforward. Every dataset in the governed layer should have a named tool or endpoint built specifically to serve it, and the interface should refuse any request that doesn't match something already defined. No passthrough SQL. No schema inspection endpoints left open "just in case." If a question doesn't map to an existing tool, the interface says no, and that refusal is the feature, not a limitation to work around.

Query boundary enforcement: the structural limits that prevent agents from harming the data layer itself

Separating agents from production solves one problem, but it leaves a second one unaddressed: a governed layer with no internal limits can still be worn down by the agents it was built to serve. Even against pre-calculated, well-modeled datasets, an agent can construct a request that pulls every row of a large dataset, fire the same expensive call repeatedly inside one reasoning loop, or ask for an aggregation across so many dimensions at once that the computation balloons. The layer needs its own defenses, separate from and in addition to its separation from production.

Row caps are the simplest of these: the interface refuses to return more than a defined number of rows per call, forcing an agent to narrow its question or paginate. Time-range constraints work the same way for anything date-based. A tool that accepts a date range should reject an open-ended request and cap the maximum span it will serve, rather than allowing a query that effectively asks for the whole history of a table.

Rate limiting at the agent or session level is standard practice in API design generally, yet it's a step teams frequently skip the moment they start treating an agent as "just another internal tool. Caching results inside the interface layer catches a related problem: agent reasoning loops tend to ask the same question more than once, and serving a repeated call from memory instead of re-executing it against the dataset saves real compute. Cardinality limits close off a more subtle risk, refusing to let an agent group results by a column with millions of unique values, since that kind of aggregation can quietly become one of the most expensive queries the layer ever runs.

The design principle holds across all of these: every parameter an agent can pass into a tool needs a defined valid range, and a request outside that range gets rejected. The data layer is already walled off from production. These limits are what keep the layer itself from being worn down by the very agents it exists to serve.

Row-level and column-level access control within the governed layer

Protecting the infrastructure is not the same as protecting the data inside it. A single flat dataset exposed equally to every agent is its own governance failure, regardless of how well the surrounding infrastructure is defended. Different agents exist for different purposes, and the data each one needs reflects that. A customer-facing support agent has no legitimate reason to see the same data as an internal finance reporting agent, so the governed layer needs to encode that distinction itself.

Column-level control handles sensitive fields, personal information, payment details, internal cost data, by leaving them out of the materialization entirely rather than filtering them at query time. A tool pointed at a dataset that never contains a payment field cannot leak that field, because the data was never put there to begin with. Row-level control works alongside this, requiring that a support agent operating inside one customer's session should only ever see rows belonging to that customer, enforced through filter parameters built into the MCP tool itself, not left to the agent's own restraint.

These controls belong in the data layer's configuration, not in an agent's system prompt. A system prompt can be overridden, by the model itself or by a prompt injection buried in the data it reads. A filter enforced at the layer level cannot be talked out of its job the same way. Logging which agent called which tool, with which parameters, provides the record that proves row and column controls are actually being honored. For any team handling regulated categories of data, health records, financial information, or other personal data under privacy law, the access control configuration of the governed layer deserves the same review process as any other application-level access control, because it is doing the same job.

Keeping Postgres as the analytical home while protecting it from agent workloads

None of this requires abandoning Postgres. For teams already running on it, the governed layer pattern means materializing datasets from Postgres on a defined schedule and keeping agents off the live database entirely, rather than replacing Postgres with something else.

Postgres is already the operational home for most Supabase teams, and standing up a separate data warehouse just to serve agents would mean maintaining a second system, a sync pipeline to keep it current, and a second schema to keep aligned with the first. The governed layer approach sidesteps all of that. Datasets get materialized out of Postgres on a schedule the team sets, stored in a columnar format such as Parquet, and queried from there by tools like DuckDB. Postgres remains the source of truth. It stops being the thing agents query directly.

The payoff appears in the production database's own behavior. Agent query load never touches the live Postgres instance, so no connection pool slots get consumed by agent traffic, autovacuum runs undisturbed, and indexes tuned for transactional reads and writes face no pressure from analytical query shapes they were never built to handle. Schema changes flow through this process deliberately rather than automatically: when a migration runs against Postgres, the governed dataset's schema only changes when the team decides to expose the new field, not the moment the migration lands. DuckDB's ability to query Parquet files directly, with no server process and no separate cluster to run, is what makes this pattern realistic for small and growing teams that have no case for standing up dedicated analytical infrastructure just to keep their agents safe.

Metric definitions as governance artifacts that agents and humans share

The most common failure in agent-driven analytics is semantic: an agent calculates something like churn using a slightly different time window or cohort definition than the dashboard uses, and leadership ends up looking at two different numbers for what is supposed to be the same question. No access control failed. No data leaked. The numbers just don't agree, and once that happens, trust in both of them erodes.

Pre-calculating metrics inside the governed layer, instead of handing agents raw tables and letting them aggregate on their own, closes this gap by making sure the calculation happens once, the same way, for everyone who asks. Each metric in the layer needs a documented definition: what it counts, how it filters, which time zone it uses, how it treats edge cases. That documentation carries as much weight as the calculation logic itself, because it's the thing that lets a human confirm the agent is measuring what it claims to be measuring.

Giving an agent a named tool, something like a tool called get_monthly_active_users, rather than open-ended aggregation access, means the agent has no opportunity to apply a different definition even by accident. The definition is fixed inside the tool, not left to the agent's interpretation of the request. The same definitions should drive the dashboards people read, the reports agents generate, and the alerts that fire automatically. A single shared definition across all three is what makes a number trustworthy at the organizational level, and it's the only way to guarantee that a figure means the same thing no matter who, or what, produced it.

Auditing and observability for agent data access in production

None of the architecture described so far means anything without a record of whether it worked. A governed data layer with no audit logging gives a team false confidence: they can point to the design and explain how it's supposed to behave, but they have no way to confirm it actually behaved that way, spot misuse as it happens, or debug an agent's reasoning after something goes wrong.

Every call into the layer's interface should be logged with enough detail to reconstruct what happened: which agent or session made the call, which tool it invoked, what parameters it passed, how many rows came back, and how long the call took. That log is what makes anomaly detection possible, and it matters specifically for catching prompt injection in progress, a sudden spike in call volume from a single session, or a pattern of tool calls that doesn't match what that agent is supposed to be doing, are signals worth alerting on the moment they appear.

Reviewing which tools get called most often, and whether that pattern lines up with each agent's stated purpose, is the direct equivalent of reviewing user access logs in a conventional system, and it deserves the same regular attention. The same log also serves as an early warning system for the data layer's own health: if a dataset has missed its refresh schedule, or a tool is quietly serving stale data, the log surfaces the problem in time for a team to catch it before an agent hands someone a wrong answer with full confidence. The architecture makes safe access possible. The audit trail is what confirms, on an ongoing basis, that it's actually happening.