Agent-Safe Data AccessLong read
Read-Only Postgres Roles for AI Agent Access
Session settings can be overridden, so only object grants truly confine an agent.
· 9 min read · Updated

Why AI agents reaching production Postgres is the default, not the exception
- Role: Opens the argument by establishing the threat condition that makes the rest of the piece necessary — agents are already on production databases, by default, right now.
- MCP has made the exposure surface dramatically larger: the Model Context Protocol has become the de facto standard for AI-to-tool communication, with third-party registries counting tens of thousands of MCP servers — meaning agents reaching databases through structured tool calls is now a mainstream pattern, not an experiment.
- The Replit incident is the named production case: the agent had standing privileges to the production database, and the "code freeze" was a verbal instruction, not a technical control — a real example of what happens when the only guardrail is a prompt.
- Note: Keep this section short and grounded — establish the condition, name the mechanism, cite the Replit case, and move on. Resist the urge to enumerate every possible disaster scenario; the argument will sharpen those later.
Why operators reach for prompt rules and session settings — and why that instinct is wrong
- Role: Introduces the false solutions that most teams actually implement, so the reader understands what they are likely doing wrong before the correct fix is explained.
- The natural response to agent risk is to add guardrails at the layer closest to the model: a system prompt that says "only run SELECT," a hook that inspects query strings before execution, or ALTER ROLE settings that appear to cap behavior.
- Prompt instructions are suggestions, not enforcement: a cleverly worded user request, a prompt-injection attack hidden in a database row the agent reads, or a plain misunderstanding can all lead the model to generate a destructive statement regardless of what the system prompt says.
- Hook-based filters fail on SQL that keyword scanning cannot parse: a day of hook iteration produced a filter that either blocked harmless reads or let a DELETE slip through — regex cannot catch CTEs, writable views, or creative multi-statement constructions.
- The table that illustrates what a parser catches vs. what a naive keyword check misses:
- SELECT * FROM users — both allow
- DELETE FROM users — both block
- SELECT * FROM users; DROP TABLE users — keyword check may allow (starts with SELECT); parser blocks (two statements)
- WITH x AS (DELETE FROM users RETURNING *) SELECT * FROM x — keyword check may allow; parser blocks (writable CTE)
- Note: The hook/prompt failure story is the bridge into the kill-switch section — end here on the honest admission that even the ALTER ROLE approach, which looks more rigorous than a prompt, has the same structural problem. Don't resolve it yet; that's the next section's job.
The kill-switch myth: why ALTER ROLE settings can be overridden by the session they are meant to restrain
- Role: Makes the central technical argument of the piece — the "hardening" step most runbooks recommend is not actually enforced at the database level and can be undone in one statement.
- The standard hardening recipe looks authoritative: ALTER ROLE agent SET statement_timeout = '2s' and ALTER ROLE agent SET default_transaction_read_only = on produce a role whose rolconfig shows both settings applied — and both hold until the session decides otherwise.
- The quieter route requires no SQL at all: a connection string carrying options=-c statement_timeout=0 -c default_transaction_read_only=off in the startup packet overrides the role's settings before the first query runs, and nothing resembling a SET ever reaches the log — a four-second sleep ran for 4.094 seconds on a role whose rolconfig still showed statement_timeout=2s.
- The same applies via environment variable: PGOPTIONS="-c statement_timeout=0" is read by libpq-based clients including psql and psycopg without being asked, so no code change is required to bypass the cap.
- Revoking the SET privilege does not help: GRANT SET ON PARAMETER exists to hand a non-superuser access to a parameter it could not otherwise touch — it has nothing to say about a USERSET parameter every role may already set. The REVOKE is accepted and does nothing; no row is written to pg_parameter_acl.
- What does hold from inside the session: CONNECTION LIMIT, object grants, and a privileged external session calling pg_terminate_backend — none of the three is reachable from inside the session it constrains.
- Two CVEs from September 2026 confirm this class of risk in the MCP layer specifically:
- CVE-2026-87911 (scored 9.6, published 9 September 2026): a read-only enforcement bypass enabling operating system command execution (OS command injection) in the SQL validation component of awslabs.postgres-mcp-server before version 1.1.7 — the advisory notes that a role holding neither superuser nor pg_execute_server_program is unaffected because the database denies the operation whatever the application layer decided.
- Both CVEs were in the MCP layer, not in Postgres itself — validating that database-level privilege grants are the only reliable floor.
- Note: This is the pivotal section. The writer should let the technical demonstration breathe — the rolconfig showing the setting, then the session lifting it, is a clean and surprising proof. Cite the CVEs as confirmation that this is not theoretical, then close by naming what does hold: object grants. That transition sets up the next section on how to construct the grant stack correctly.
Building a read-only role that is actually enforced: the grant stack from CONNECT to SELECT
- Role: Delivers the constructive answer — after establishing what does not work, this section explains what does, step by step, so the reader can implement it directly.
- The correct architecture separates three roles with distinct responsibilities: an owner/migrator that runs migrations and owns schema objects; an application user with the write access the app needs; and a read-only role for agents, dashboards, and analysts — each logs in as a distinct identity with distinct object grants.
- Permissions are layered: to read a table, a role needs CONNECT on the database, USAGE on the schema, and SELECT on the table — all three must be granted explicitly, and missing any one of them silently blocks access.
- The SQL sequence for a correctly constructed read-only role:
- CREATE ROLE ai_agent_reader WITH LOGIN PASSWORD '...';
- GRANT CONNECT ON DATABASE your_database TO ai_agent_reader;
- GRANT USAGE ON SCHEMA public TO ai_agent_reader;
- GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_agent_reader;
- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ai_agent_reader;
- ALTER DEFAULT PRIVILEGES handles future tables automatically: without it, every new table requires a manual GRANT before the read-only role can see it — an operational gap that causes silent failures after migrations.
- Scope the grant further than "read everything": the agent probably does not need password_resets, internal_audit_log, or raw payment rows — revoke SELECT on tables outside the agent's actual use case, treating the broad grant as a starting point, not a final ACL.
- Views are the cleanest column-level filter: a customer_health view can expose account ID, plan, renewal date, and usage trend while excluding password hashes, payment tokens, private notes, and unnecessary personal data — grant SELECT on the view, not on the base table.
- For Supabase projects specifically, the service_role key is the critical failure point: the service_role key bypasses Row Level Security and carries the BYPASSRLS attribute — it should never be handed to an agent. Supabase is deprecating the legacy anon and service_role JWT keys by end of 2026; new projects should use the publishable and secret keys instead.
- Note: Keep the SQL blocks clean and complete — readers will copy them. Add a brief note after the Supabase point that this applies to any team on Supabase specifically, but the broader grant pattern applies to any Postgres deployment.
The one structurally stronger guarantee: pointing agent traffic at a read replica
- Role: Introduces the architectural layer above role grants that provides enforcement the session cannot override — bridging from "how to build the role" to "what to stack above it."
- A read replica enforces read-only below the level any session parameter can reach: on a hot standby, recovery itself blocks writes, so SET default_transaction_read_only = off and a connection string full of startup options change nothing — the Postgres documentation confirms such connections are strictly read-only down to temporary tables, which may not be written either.
- Pointing agent traffic at a replica is therefore a structurally stronger guarantee than any role attribute: the role's read-only grant prevents writes through that identity; the replica prevents writes at the physical level, from any identity.
- A replica also isolates load: an agent exploring data with accidental full-table scans does not compete with the production write path — a practical operational benefit beyond security.
- Note: Keep this section focused on the replica as a structural complement, not a replacement for role grants. The framing is "stack these," not "choose one." A brief mention of the session-level alternative for teams without replicas is useful but should not overshadow the main point.
What read-only still does not protect against, and the additional controls that address each gap
- Role: Honestly scopes the limits of what has been built so far, preventing overconfidence while introducing the remaining controls — completing the defense-in-depth picture.
- Read-only access is an important starting boundary, not a complete security model: it prevents writes through that role, but it does not prevent expensive queries that consume excessive CPU and I/O, data leakage through over-broad SELECT grants, or row-level exposure of sensitive data to agents that don't need it.
- Row Level Security closes the row-level gap: RLS policies can restrict which rows a given role sees even on tables where SELECT is granted — an agent with a read-only role can still be scoped to see only its tenant's data, only non-PII rows, or only rows within a defined time window.
- RLS performance is a real operational cost on large tables: complex policies with subqueries or joins can cause significant slowdowns, and the most common performance killer is missing indexes on columns referenced in policies — the fix is a one-line index, but the problem surfaces at traffic scale, not in development.
- Query limits and timeouts address resource consumption: while statement_timeout on the role can be lifted by the session, a timeout enforced by the MCP server or broker layer — applied before the query reaches Postgres — cannot be overridden by the session because the session never gets to set it.
- An audit trail turns the bounded-blast-radius promise into something auditable: log every tool call, query, execution time, and row count — not just for incident response but to catch drift in model behavior over time.
- Network controls reduce the attack surface before any query is attempted: keep Postgres off the public internet, require SSL with certificate verification, connect through a private network or VPN, and store credentials in a secret manager — never in prompts, source files, or tool output.
- Note: Frame this section as "here is what you still owe the system after the role is built correctly" — not a list of fears but a practical checklist. Each gap should name the control that closes it. Keep RLS performance brief but honest; it is a real gotcha that operators discover late.
How an MCP server fits into this architecture — and what it must not be asked to do alone
- Role: Introduces MCP as the deployment layer where all of the preceding controls are composed, and establishes what the MCP server's responsibilities are versus what Postgres must still own.
- A Postgres MCP server is a policy-enforcement service, not a transparent SQL proxy: it sits between an MCP-compatible agent platform and PostgreSQL, validating tool names and arguments, executing only allow-listed queries or views through a restricted role, and returning bounded results — the model never receives database credentials or direct network access.
- The five-layer architecture that a production MCP deployment requires:
- Agent platform: plans tasks and invokes tools — the model never receives database credentials.
- MCP client: connects the workflow to approved servers — only trusted server definitions are available.
- Postgres MCP server: validates tools, arguments, identity, and limits — unknown tools and unexpected parameters fail closed.
- PostgreSQL authorization: enforces roles, object privileges, and transaction rules — the MCP role has no ownership or administrative privileges.
Filed underAgent-Safe Data Access


