Read-Only Agent Data Access Patterns in Postgres

Teams building hook-based guardrails around an agent's SQL access run into the same wall: regex filters and prompt-level instructions miss common table expressions, writable views, and the dozens of creative ways a model can phrase a mutation so it doesn't look like one. A database-level grant does not have this problem. A role that lacks INSERT, UPDATE, or DELETE privileges cannot be talked into exercising them, no matter how the query is worded or how persuasive the prompt sounds. A read-only Postgres role belongs at the base of any agent data access design, but the role alone is not sufficient. It says nothing about how much data a query can scan, which tables or columns an agent can see, whether an operator can shut off one misbehaving agent without affecting the rest, or what ran against the database last Tuesday at 3 a.m.

The pressure to give agents direct database access in the first place comes down to speed. Sweeping a set of conversations through a paging API took roughly seven minutes, but you could run the same query directly in SQL in under a second. That gap between a paginated API crawl and a single indexed query is so large that teams building agent workflows usually end up treating direct database access as the only workable option. But direct database access means the agent sits close enough to the data to cause real damage, and that forces a governance question that paging through an API never raised: what, specifically, stops an agent from doing something an API would have prevented by construction. A read-only role answers part of that question. The rest of this piece is about what has to sit on top of it.

Constructing the Read-Only Role Correctly

Most first-pass read-only roles are granted more broadly than they need to be, and every bit of that extra scope becomes work that a later layer has to compensate for. The core grants are simple: CONNECT on the database, USAGE on the schema, SELECT on all tables in the reporting schema, and an ALTER DEFAULT PRIVILEGES statement so that any table created after the role is set up inherits the same read-only limit automatically. INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TRUNCATE, and SUPERUSER have no place in this role under any circumstance.

The grant should point at a reporting or analytics schema, not at raw application tables. Raw tables carry columns no agent needs to see, and they couple the agent's access directly to however the application happens to be modeled this week. A view built for this purpose, something like a customer_health view exposing account ID, plan, and usage trend while leaving out password hashes, payment tokens, and private notes, does more to protect the business than a SELECT grant against the base table ever could.

One grant that looks harmless but isn't: USAGE ON ALL SEQUENCES. It carries the nextval privilege, so it advances sequence state even when it touches no row. A role built this way can claim truthfully that it changed nothing in the data and still have moved a counter forward, which is a side effect few teams intend to permit. Leave sequence grants out unless you have a specific, named reason to include them.

Identity matters as much as the grant itself. Each agent should connect under its own Postgres identity, or under per-session credentials minted through a claim mechanism, not a credential every agent in the fleet shares. A shared credential turns "revoke the one agent that's misbehaving" into "revoke every agent at once," defeating the purpose of fine-grained access control. If a read replica exists, point agent traffic there instead of at the primary, because one less workload competing for primary resources is a real gain even before any other control is added. Where no replica exists, read-only access on the primary is still the correct call, just a costlier one.

The USERSET trap: why ALTER ROLE settings can be overridden from inside the session

Nearly every operational runbook for a read-only agent role recommends two ALTER ROLE settings: a capped statement_timeout and default_transaction_read_only set to true. Both feel like hard limits. Neither one is.

The reason comes down to parameter context. Both statement_timeout and default_transaction_read_only carry a context of "user" in Postgres, and a user-context parameter can be changed by the very session it was set to restrain. If a session issues one SET statement, that moves the parameter from the role's default into session scope, and the limit the operator thought they'd configured simply stops applying for the rest of that connection. No exploit, no injection, no unusual permission is required. It's one line of SQL that any session is already allowed to run.

The instinctive fix, revoking the SET privilege on the parameter, does not work here. REVOKE SET ON PARAMETER statement_timeout writes no row to pg_parameter_acl and changes nothing about what the session can do, because parameter ACLs exist to grant a non-superuser access to a parameter it could not otherwise touch. statement_timeout is settable by every role by default, so there is no restriction to revoke.

Three things do hold up from inside the session: a CONNECTION LIMIT on the role, object-level grants, and a second, separately privileged session calling pg_terminate_backend against a runaway query. None of these three can be reached or altered by the session they constrain, which is exactly the property that statement_timeout and default_transaction_read_only lack.

The practical consequence is that reading a role's definition and seeing statement_timeout=2s tells an operator what value the session starts with, not what the session is bound to for its lifetime. That gap, between a starting value and an enforced ceiling, is precisely what the next two layers of the stack exist to close.

Statement classification: rejecting dangerous queries before they reach the database

Because a session can lift its own statement_timeout and its own read-only default, something has to sit in front of the database and judge each statement on its own terms before it ever reaches Postgres. That's the job of statement classification: parsing the SQL an agent intends to run and rejecting anything that falls outside an approved shape, regardless of what the role's settings currently claim to be.

Classification has to catch more than the obvious write keywords. It needs to reject DDL, COPY statements, functions that mutate state even when called inside what looks like a read path, SQL comments used to mask a second statement tacked onto the end of a query, and multi-statement execution in general. A filter built to catch INSERT, UPDATE, and DELETE will miss all of these.

Regex-based pre-filtering keeps failing in practice for the same reason. A hook that scans for write keywords has no way to catch a CTE that writes through a data-modifying WITH clause, a writable view that turns a SELECT into a mutation under the hood, or any of the other constructions a model can generate without ever typing the word "INSERT." The fix is to parse the SQL into its actual structure and classify based on what the statement does, not to pattern-match against the words it contains.

Classification is not a replacement for executing inside a read-only transaction; the two are complementary. If you wrap execution in a read-only transaction, you get a second, independent wall. If a classifier misses something, it still cannot write to the database once it sits inside a transaction that Postgres itself refuses to let mutate state. The order is classify first, then execute inside a read-only transaction, defense stacked on defense rather than either one standing alone.

Resource caps and blast-radius governance: what "read-only" still cannot prevent

A query can pass classification cleanly and execute inside a transaction that is genuinely read-only and still do serious damage. Read-only says nothing about how much CPU, memory, I/O, or lock contention a query is allowed to consume on the way to returning its result, and a SELECT with a bad join plan against a large table (to use a dataset scale that text-to-SQL research has actually tested against) can tie up resources on the primary long enough to degrade everything else running there. Resource caps exist to contain that risk, and they sit at a different layer than anything classification or transaction mode can reach.

statement_timeout has to be enforced server-side, by a privileged session or a connection proxy sitting in front of the agent, not by the agent's own session. Given that the session can lift its own timeout, any cap set from inside that session is not a real cap. Per-role connection limits work differently: Postgres enforces them directly and the session cannot override its own connection limit from the inside, the same property that made CONNECTION LIMIT one of the three controls that survive the USERSET trap. Result-size caps need to live in the query or execution layer sitting between the agent and the database, because "read-only" does not stop a SELECT that scans a billion rows; a limit enforced outside the query itself does. Individual tools or query templates can carry their own row limits as a further constraint on top of that.

Where the infrastructure allows it, routing agent traffic to a read-only replica or a separate connection-pooler lane keeps a pathological query from starving the primary database that production traffic depends on. CNPG supports this kind of routing, and at Supabase scale, Supavisor is the component that handles connection pooling for agent workloads specifically. None of these controls requires trusting the query's intent. They cap what any query, however well-classified, is allowed to cost.

The view layer as a stable, governed contract between agents and data

Everything up to this point has been about blocking behavior: rejecting writes, capping resources, closing session-level loopholes. The view layer is where the argument turns from what to block toward what to expose, and it's where the design of agent access starts to look less like a fence and more like a contract.

Granting an agent SELECT access straight to application tables ties its behavior to the physical schema, and that coupling means every migration becomes a potential agent-breaking change. A stable view schema, something like an agent_api namespace, decouples what the agent sees from how the data is actually modeled. The schema underneath can change freely, because the view surface the agent reads from stays constant, so migrations stop being a risk for the agent.

One Postgres default makes this layer easy to get wrong. Views execute as their creator unless you tell them otherwise, so a view can silently bypass row-level security policies defined on the tables underneath it. Views intended for agent use need security_invoker = true set explicitly, so the view enforces the querying session's own RLS policies rather than the privileges of whoever created the view. Leaving that setting off doesn't throw an error; it quietly removes row-level authorization from a surface that looks perfectly safe.

View definitions belong to the operator, not the agent. Agents asked to generate their own Supabase schema have been observed skipping RLS policies on exposed schemas, hallucinating CLI commands that don't exist, and building views without security_invoker = true. It hands the agent the exact decision it's least equipped to make correctly.

Row-level security on the underlying tables does the rest of the authorization work: it scopes visible rows to an agent's own session-to-conversation ownership chain, and operator-only data is simply absent from the view. RLS owns read authorization at the row level; the application layer continues to own writes, and the two never need to overlap. Column-level filtering happens in the view definition itself. A customer_health view can expose account ID, plan, renewal date, and usage trend while leaving out password hashes, payment tokens, and private notes, so an agent gets the business context it needs without ever being in a position to see a column it shouldn't.

A semantic layer above the views: the basis for trustworthy agent answers

An agent restricted to approved columns in a well-governed view can still give you a confident, fully wrong answer, because access control and meaning are different problems. Knowing that a column exists and is safe to read is not the same as knowing what the column means in business terms, and that second kind of knowledge is where a semantic layer earns its place above the views.

The model needs table names, column names, types, relationships between tables, and short business definitions attached to each, because raw information_schema output doesn't carry any of that context on its own. A model can know perfectly well that subscriptions.status exists as a column, but not know whether a canceled row still counts toward monthly recurring revenue, or which timezone defines the boundary of a reporting day. Whether a generated query returns the number the business actually uses depends on answering exactly those questions.

The same gap appears in MCP. Connecting an agent to a database through MCP moves data to the agent; it does nothing to make that data correct once it arrives. An agent connected through MCP to tables that have never been certified for business meaning will produce the same confident wrong answers as one querying raw tables directly, because MCP is a transport, not a guarantee of correctness. A semantic layer sitting behind that connection certifies the data's business meaning, which is what turns MCP into something an analyst can trust the output of.

A governed query interface built for agents should expose metric contracts, a defined set of allowed dimensions, governed query tools rather than open-ended SQL generation, and responses that carry provenance showing where a number came from. An agent that can write a query against a schema understands only the syntax, while an agent that understands the analytical contract it's operating inside knows what the query means. The most reliable version of this pattern has agents consuming pre-calculated, governed metrics rather than generating ad-hoc SQL against views every time a question comes in. When the calculation happens once, in one place, a human looking at a dashboard, an agent answering a question, and a scheduled report all land on the same number, because none of them recomputed it independently.