Letting an Agent Query an Existing Database: Read-Only Roles, Row-Level Security, Settings an Agent Can Override and a Curated View Layer

The request

An analytics or support agent is far more useful when it can answer questions from the database the application already uses: how many refunds did this customer get this quarter, which orders are stuck in a state. The quick version gives the agent a connection string and a prompt that says "only run SELECT statements". That prompt is not a boundary. The model may comply on every test and still emit an UPDATE when a user asks it to fix something, and prompt injection in retrieved text can ask for exactly that.

The boundary has to be the one the database already enforces for people: a role with privileges, policies on rows, limits on resources, and a schema designed to be read. The question this article answers is which of those controls hold against SQL that the client itself writes, and which ones only look like they do.

What stays deterministic: privileges, not defaults

PostgreSQL distinguishes between privileges, which a role cannot grant itself, and session settings, which the session can change. That distinction decides what an agent can undo with one more statement.

The row-level security documentation is explicit about the edges: superusers and roles with BYPASSRLS always bypass policies, table owners normally do too, and a table with RLS enabled but no policy denies everything. The predefined role pg_read_all_data grants SELECT on everything and, per the documentation, does not bypass RLS, which makes it convenient and far too broad for an agent.

Views are a capability layer, with one trap

Grant the agent a schema of views written for questions rather than the application's tables. Views hide columns the agent should never see, rename codes into words the model understands, pre-join the obvious relations and fix the grain of each answer. They are also where the trap lives. By default a view accesses its base tables with the privileges of the view owner, and the CREATE VIEW documentation states that row-level security policies of the view owner are applied unless the view has security_invoker set to true. A view owned by the application's schema owner can therefore quietly return every tenant's rows to an agent whose own role is restricted. Create agent views with security_invoker = true, or own them with a role that is itself subject to the policies. A security_invoker view also needs the agent to hold SELECT on the base table, which reopens the table to direct queries; grant it on the view's columns only, so that querying the table directly shows no more than the view.

Illustrative PostgreSQL setup; test it by connecting as the agent role, including a direct query on app.refunds.

CREATE ROLE agent_tenant_42 LOGIN NOINHERIT;
CREATE SCHEMA agent_api;
CREATE VIEW agent_api.refunds WITH (security_invoker = true) AS
  SELECT r.id, r.order_id, r.amount_cents, r.reason, r.created_at
  FROM app.refunds r;
ALTER TABLE app.refunds ENABLE ROW LEVEL SECURITY;
-- Every non-owner role now sees no rows unless a policy names it, so the
-- application role needs its own policy (or must own the table).
CREATE POLICY agent_tenant_42_rows ON app.refunds FOR SELECT
  TO agent_tenant_42 USING (tenant_id = 42);
GRANT USAGE ON SCHEMA agent_api TO agent_tenant_42;
GRANT SELECT ON agent_api.refunds TO agent_tenant_42;
GRANT USAGE ON SCHEMA app TO agent_tenant_42;
-- security_invoker needs the agent to read the base table: grant only the view's
-- columns, so a direct query on app.refunds cannot see more than the view does
GRANT SELECT (id, order_id, amount_cents, reason, created_at) ON app.refunds TO agent_tenant_42;
ALTER ROLE agent_tenant_42 SET statement_timeout = '5s';

Resource limits outside the session

A read-only query can still hurt an application database: a cross join, a scan of a billion-row table, an ORDER BY without a LIMIT. Set statement_timeout on the role, because it catches honest mistakes, and accept that a determined session can SET it away. The limits that cannot be negotiated live outside the session: a connection pool with a small size for the agent role, a query gateway that rejects anything except a single SELECT before it reaches the database, a read replica so the primary is never at risk, and a gateway-side deadline that cancels the backend if the statement runs too long.

An estimated cost gate is a reasonable addition. Running EXPLAIN (FORMAT JSON) first returns the planner's total cost, and a gateway can refuse plans above a threshold. Treat it as a heuristic: estimates come from statistics and can be wrong by orders of magnitude, so the deadline still has to exist.

A gate you can run

The runnable example in agent-decision-lab demonstrates the layering with SQLite and the Python standard library, so it runs anywhere. SQLite has no roles, but it has two hooks that act inside the engine: an authorizer callback consulted for every table and column a statement touches, and a progress handler that can abort a statement after a budget of virtual machine instructions. The gate opens the database read-only, allows reads of five named columns and a short list of functions, and aborts long statements.

Abridged from examples/python/agent_sql_gate in agent-decision-lab; the tests cover every row of the table below.

ALLOWED_COLUMNS = {("refunds_view", c) for c in ("id", "order_id", "amount_cents", "reason", "created_at")}

def strict_authorizer(action, arg1, arg2, db_name, source):
    if action in (sqlite3.SQLITE_SELECT, sqlite3.SQLITE_RECURSIVE):
        return sqlite3.SQLITE_OK
    if action == sqlite3.SQLITE_READ:
        # db_name is None for the columns of a CTE, which exist only inside this statement
        if (arg1, arg2) in ALLOWED_COLUMNS or db_name is None:
            return sqlite3.SQLITE_OK
        return sqlite3.SQLITE_DENY
    if action == sqlite3.SQLITE_FUNCTION:
        return sqlite3.SQLITE_OK if arg2.lower() in ALLOWED_FUNCTIONS else sqlite3.SQLITE_DENY
    return sqlite3.SQLITE_DENY

conn = sqlite3.connect(f"file:{replica}?mode=ro", uri=True)
conn.set_authorizer(strict_authorizer)
conn.set_progress_handler(budget.tick, 1000)

Worked example: a bypass found while building the gate

The first version of the gate ran against the application database and used a tenant view, refunds_view, whose body filters tenant 42. To let the view read its base table, the authorizer allowed any read whose fifth argument, the inner-most view responsible for the access, was refunds_view. It passed every planned test. Then one more statement went in: WITH refunds_view AS (SELECT * FROM refunds) SELECT sum(amount_cents) FROM refunds_view. SQLite reports the name of a common table expression in that same argument, so the reads of the base table arrived labelled as coming from refunds_view, the gate allowed them, and the query returned 14,400 cents: both tenants, where 4,500 belonged to tenant 42.

The fix was not a smarter callback. Column-level checks cannot tell a filtered view from an unfiltered CTE reading the same columns. The strict gate instead connects to a replica that holds only tenant 42, which is the role row-level security plays in PostgreSQL: the engine the agent talks to contains nothing it must not return, whatever shape the query takes.

The general lesson transfers directly: a gate that reasons about query text or about names inside the query can be steered by the query. Put tenant isolation where the query cannot reach it.

Migration steps

1. Inventory the questions the agent must answer and write one view per question family, with security_invoker and plain column names.
2. Create login roles for the agent, one per tenant where tenants share tables, with SELECT on those views only and a pool of its own.
3. Enable RLS on the base tables the views read, keyed on the role rather than on a settable variable.
4. Route agent SQL through a gateway that accepts one SELECT statement, applies a deadline and logs the text, plan cost and row count.
5. Point the agent at a replica. Measure rejected statements and timeouts per day before widening the view layer.

When an agent is unnecessary

If the questions are known in advance, build parameterised tools instead of free SQL: refunds_for_customer(customer_id, quarter) is easier to test, cheaper to run and impossible to turn into a full scan. Free SQL earns its risk only for genuinely open-ended analysis, and even then a semantic layer of views gives the model most of its accuracy.

Failure modes

• A prompt as the only restriction, defeated by one injected instruction.
• Tenant isolation through a session variable that the agent can SET.
• Views that run as their owner and bypass the policies of the role that queries them.
• statement_timeout as the only time limit, undone by SET.
• An agent role in the application pool, so a slow agent query starves user traffic.
• Granting pg_read_all_data for convenience, which exposes every table.
• A table-level grant under a security_invoker view, which lets a direct query read every column the view was meant to hide.

Practice and related reading

Further reading:

• SQL injection in 2026, including LLM-generated SQL
• SQL injection through LLM applications
• DDL behind a lock queue and lock_timeout

Sources and scope

Checked against these sources on 21 October 2026. Examples and figures are illustrative, not measurements.

• PostgreSQL documentation: Row Security Policies
• PostgreSQL documentation: CREATE VIEW (security_invoker)
• PostgreSQL documentation: Client Connection Defaults (statement_timeout, transaction_timeout, default_transaction_read_only)
• PostgreSQL documentation: Predefined Roles (pg_read_all_data does not bypass RLS)
• PostgreSQL documentation: EXPLAIN
• Python documentation: sqlite3 (set_authorizer, set_progress_handler)
• SQLite C interface: Compile-Time Authorization Callbacks