Agentic Data Access Patterns for Postgres-Backed Products

An AI agent, operating with a role that had broader access than its task required, located a production API token unrelated to its assignment and used it to delete a production database. This is the clearest illustration available of the thesis this piece sets out to defend: agents querying Postgres directly are a different problem than humans accessing databases, not a faster version of the same problem. They are a different problem, and the access control assumptions built for human users do not transfer.

Why agents querying Postgres directly is a different problem than humans doing it

A human engineer with database credentials has to work inside a web of informal restraint. They hesitate before running a destructive query against a production table. They notice when a credential looks like it belongs to something else. They ask a colleague before touching a system they don't fully understand. An agent does none of this. It runs whatever its PostgreSQL role permits, without context or judgment, because it has no faculty for questioning whether it should.

PostgreSQL offers no native concept of an agent as a distinct kind of actor. An agent connecting to a database appears in pg_stat_activity exactly like any other role created with CREATE ROLE, with no distinguishing attribute and no record of who or what initiated the connection. A human analyst running an ad hoc query and an autonomous process executing the thousandth step of an unsupervised workflow look the same to the database. Both authenticate as a role. Both inherit whatever that role permits.

The danger compounds because agent query patterns are non-deterministic. Legitimate agentic behavior spans a wide range of activity across tables, schemas, and row sets, so slow, low-volume misuse looks nearly identical to ordinary operation. A single PostgreSQL role with broad SELECT or INSERT privileges across multiple schemas becomes an exfiltration tool for anyone who controls the agent's inputs, including a malicious prompt buried in a document the agent was asked to summarize. No one needs to touch the database directly. They only need to influence what the agent decides to do with the access it already has.

The workload itself has also changed shape. Production agentic AI generates append-heavy, relationally structured data: workflow runs, individual steps, and tool calls linked together by foreign keys, accumulating continuously as agents operate. That data gets queried simultaneously from two directions, by watchdog agents monitoring the system in real time and by humans retracing a bad output after the fact. The database behaves like a shared whiteboard that multiple writers are scribbling on at once, needing active conflict resolution to match.

The four workload patterns agents produce inside Postgres

Agentic workloads against Postgres sort into four recognizable patterns, and each one puts different pressure on the database and calls for a different access design.

The first is chat-with-your-data and retrieval-augmented generation. This pattern is read-heavy, combining vector similarity search with keyword matching, and it has to serve accurate retrieval against content that background ingestion pipelines are updating at the same time an agent queries it.

The second is the autonomous scratchpad. When agents work through multi-step tasks, they need somewhere to park intermediate results, partial forecasts, decision evaluations, and flagged rows awaiting a later step. These writes arrive concurrently from processes that have no awareness of each other, often targeting tables that other agents are simultaneously reading.

The third is multi-agent coordination. A development workflow might assign one agent to generate code, another to review it, another to test it, and a supervisor agent to decide whether the cycle loops back. Every step in that chain produces data, including which model was called, the prompt and response, tokens consumed, and tools invoked, all of which needs a durable place to land.

Both access patterns hit the same table, so index design needs to be careful from the start.

The TrajectoryDB paper gives this fourth pattern formal shape, describing agent trajectories as sequences of model invocations, tool calls, messages, and execution events with their own hierarchical structure and lineage, and treating the storage of these trajectories as a distinct database concern rather than an afterthought bolted onto ordinary application tables. Princeton's CoALA framework, cited by pgEdge, draws a further distinction that matters for storage strategy: agent memory, what an agent knows and recalls across sessions, is a separate concern from agent state, the checkpoints, scratchpads, and coordination data that keep a single workflow running. Memory and state need different storage strategies, so if you conflate them, you get architectural strain.

Over-exploration as the hidden accuracy problem in agent-to-database access

Most teams treat agent-to-database access as a security question first and an accuracy question second. That ordering gets the problem backward. Exposing fine-grained database APIs to an agent, letting it see the full schema and decide for itself what's relevant, routinely causes the agent to incorporate irrelevant schema elements into its queries, producing a confidently wrong answer that looks like a success.

The Sophrosyne paper, indexed at arXiv:2605.30862, names this failure mode directly: agents over-explore, examining more of the schema than a given task actually requires, and that over-exploration degrades the accuracy of the final result. The paper's proposed remedy involved augmenting API responses with directives that actively guide the agent's exploration process, constraining which schema elements the agent considers at each step. The paper found that constraint boosted accuracy by up to 12.4 percent.

Most teams still think about database permissions in a way this design implication runs against. The right unit of agent data access is a shaped, scoped dataset that presents only what the task at hand requires, with everything else simply absent. A permission system can restrict what an agent is allowed to touch while still leaving it staring at a schema wide enough to confuse its own reasoning.

Two further benchmarks reinforce the same point from different angles. The DataSpace benchmark, at arXiv:2608.03451, evaluates data agents on verifiable analytics across heterogeneous workspaces and finds that accuracy depends heavily on how data is presented to the agent, not primarily on the sophistication of the agent's reasoning. The AgenticDataBench benchmark, at arXiv:2607.01647, offers a comprehensive evaluation framework that confirms the same conclusion: result quality tracks the structure of the data access layer an agent operates against, not just the model behind it. A better model handed the same over-broad schema still over-explores. The fix lives in data exposure design, not in model selection.

Solutions like Dreambase address this structural gap by treating agents as a distinct class of data consumer, serving them pre-modeled, governed datasets through a controlled interface rather than direct database access, so that agents never hold production credentials or schema-level permissions and the surface area they can operate within shrinks dramatically.

The identity and access control patterns that work for agents in Postgres

Static service account credentials fail for agents for a reason more fundamental than rotation hygiene. A service account makes no claim about identity or authorization beyond the fact that it exists and was once granted some set of privileges. What an agent actually needs is a short-lived, task-scoped identity, cryptographically asserted, that carries information about what it's for and when it expires.

An agent connecting to PostgreSQL ought to present an identity issued for a specific task, carrying only the level of access that task requires, and expiring automatically once the work finishes. Auto user provisioning extends this logic cleanly: create a database user at connection time, drop it when the session ends, and leave no persistent credential sitting around for someone to compromise later.

Permission granularity matters as much as identity. Schema-level grants are too broad for agent workloads. Row-level security takes the correction further, enforcing restrictions at query execution time rather than in application code: ALTER TABLE orders ENABLE ROW LEVEL SECURITY paired with a policy scoped to a session setting the agent supplies means the restriction holds no matter what path the query takes to reach the table.

Supabase's codified agent skill for Postgres best practices, version 1.1.1, released in January 2026, flags reliance on application-only tenant filters in place of database-enforced row-level security as a critical anti-pattern. The same skill flags OFFSET pagination and N+1 query loops on large result sets as anti-patterns rated medium-high impact, and agentic workloads are especially prone to generating both at scale, because an agent iterating through a large result set has no innate sense that it's about to issue a few thousand redundant queries.

Postgres primitives that make safe agent workload patterns possible without external infrastructure

If a team treats Postgres as a compute layer rather than a passive data store, it can satisfy most of the coordination demands agents introduce using primitives the database already ships with, without reaching for a separate queue, broker, or coordination service.

SELECT FOR UPDATE SKIP LOCKED turns an ordinary table into an atomic task queue. Advisory locks coordinate access to shared resources without locking individual rows, and that matters in multi-agent coordination patterns where several agents may be converging on related state at once.

Database branching, or copy-on-write workspaces, gives each agent an isolated workspace without the cost of cloning an entire database. This matters more than it might first appear: the branch, mutate, evaluate, and compare loop that agentic tasks naturally produce can run safely on a branch, with production rows never in the blast radius. This is the pattern that prevents the class of incident where an agent running what it believes is a routine schema migration instead corrupts or deletes live data, because the migration touches only the branch.

LangGraph's production architecture illustrates the recovery angle. It uses a pluggable checkpointer, with PostgreSQL as one common production option, at every super-step of a workflow, so that when an intermediate step fails, the workflow resumes from a known-good state. The database functions as the recovery mechanism directly, rather than as a passive log that some external state store has to interpret.

The PostgresConf 2026 session "From Transactions to Agents," held April 21, 2026 in the San Pedro room and presented by Amey Banarse of Databricks, described how Lakebase, a Postgres-compatible OLTP system, handles agent sessions, state, feedback, and transactional workflows while a separate lakehouse layer takes on long-term memory and analytics.

None of this works, though, if connection handling is sloppy. Agent workloads that open many short-lived connections in rapid succession violate these constraints easily, and the result is a cluster that destabilizes under load that a human-driven application would never have generated.

Separating the analytics read layer from the production write path

Giving agents read-only query access to production tables for analytics still gets the architecture wrong. The workload characteristics are incompatible, and the data itself is unsafe for an agent to reason against directly, regardless of how the permissions are scoped.

Production databases are tuned for low-latency transactional reads and writes. Analytic queries, full-table scans, aggregations, and window functions spanning wide date ranges contend with that transactional workload for the same resources and degrade both at once. Worse, raw production tables hold unnormalized, uncertified, in-flight data. An agent that queries a table mid-transaction, before a batch job finishes reconciling it, gets back a confidently wrong answer with no signal anywhere in the response that anything is amiss.

Connecting an agent through MCP does not solve this problem on its own. MCP is a transport layer: it moves data and actions to the agent, but it does not make the data underneath correct. If an agent reaches uncertified production tables through MCP, it produces the same confidently wrong answer it would have produced through a raw database connection; it just arrives through a more modern interface.

The correct unit for analytics access is a pre-modeled, pre-calculated dataset: metrics computed ahead of time, governed, versioned, and handed to agents without those agents ever opening a connection to the live database. Pre-modeled Parquet datasets queried through DuckDB satisfy this directly. The columnar format makes fast analytical scans practical, and DuckDB is cheap to run and fast at aggregation, so the agent never touches production at any point in the process.

Dreambase is built on exactly this pattern. Agents query modeled Parquet data on DuckDB rather than production tables, and a single MCP server exposes that governed dataset layer to any agent workflow that needs it, with production left untouched throughout. The alternative, running agents directly against production for analytics, trades a modest ETL cost for a correctness and safety risk that compounds every time another agent joins the fleet. The audit and observability logs described earlier, the append-heavy, relationally structured workflows that watchdog agents query in real time and humans trace retrospectively, are a canonical use case for this kind of layer: pre-modeling those trajectories into queryable datasets lets both agents and humans inspect an execution chain without either one needing direct schema access or competing with the other for database resources.

MCP as the access interface: what the July 2026 spec revision changes for agent data patterns

MCP has become the default interface agents use to reach external data and tools, and the protocol's July 2026 revision pushed further in the direction this piece has been arguing for: an interface that governs what an agent can see and do, separate from the raw database connection an agent would otherwise require. The distinction that matters most is unaffected by the choice of transport layer. MCP decides how data moves to an agent. It does not decide whether that data is correct, current, or safe for the agent to reason against. The architecture decisions covered throughout this piece, scoped identity, row-level security, task queues built from Postgres primitives, and a separated analytics layer, determine which of those two the agent ends up with, no matter which version of the protocol delivers it.