DEV Community

Cover image for Giving an AI Agent Database Access Without Giving It Your Database
Gabriel Anhaia
Gabriel Anhaia

Posted on

Giving an AI Agent Database Access Without Giving It Your Database

#typescript #agents #postgres #security

"Let the agent query the database" is the feature everyone wants and the tool
everyone builds wrong the first time.

const runSql = tool({
  name: "run_sql",
  schema: z.object({ sql: z.string() }),
  async run({ sql }) { return db.$queryRawUnsafe(sql); },
});
Enter fullscreen mode Exit fullscreen mode

That is a database shell exposed to a text interface that will follow
instructions found in a support ticket. Prompt-level mitigation ("only write
SELECT statements") is not a control — it is a request, and the request is
adjacent to untrusted content in the same context window.

There is a version of this that is safe, and it does not involve trusting
generated SQL.

Layer 1: parameterised tools instead of a query language

The strongest thing you can do is not offer SQL at all. Most "data questions"
resolve to a small number of shapes.

const revenueByPeriod = tool({
  name: "revenue_by_period",
  description:
    "Total revenue grouped by day, week or month for a date range. " +
    "Maximum range 365 days.",
  schema: z.object({
    from: z.string().date(),
    to: z.string().date(),
    granularity: z.enum(["day", "week", "month"]),
    productId: z.string().uuid().optional(),
  }).refine((a) => days(a.from, a.to) <= 365, {
    message: "Range cannot exceed 365 days", path: ["to"],
  }),
  async run(a, ctx) {
    return db.$queryRaw`
      SELECT date_trunc(${a.granularity}, created_at) AS period,
             sum(total_cents) AS cents
      FROM orders
      WHERE tenant_id = ${ctx.tenantId}
        AND created_at >= ${a.from}::date
        AND created_at <  ${a.to}::date
        ${a.productId ? Prisma.sql`AND product_id = ${a.productId}` : Prisma.empty}
      GROUP BY 1 ORDER BY 1`;
  },
});
Enter fullscreen mode Exit fullscreen mode

The query is written by you. The model chooses a shape and fills parameters,
which is exactly the amount of freedom it needs. The tenant filter is not
expressible by the model, so it cannot be omitted.

Five or six tools like this cover a startling share of what people actually
ask. Build those first and see what is left over before reaching for anything
general.

Layer 2: if you must generate SQL, never execute it directly

Sometimes the question space genuinely is open-ended. Then the rule is: the
model proposes, a validator disposes.

import { Parser } from "node-sql-parser";

const ALLOWED_TABLES = new Set(["orders", "order_items", "products"]);

export function validateSelect(sql: string, tenantCol = "tenant_id") {
  const parser = new Parser();
  let ast;
  try { ast = parser.astify(sql, { database: "postgresql" }); }
  catch { return { ok: false as const, why: "unparseable" }; }

  const stmts = Array.isArray(ast) ? ast : [ast];
  if (stmts.length !== 1) return { ok: false as const, why: "multiple statements" };

  const s = stmts[0];
  if (s.type !== "select") return { ok: false as const, why: `${s.type} not allowed` };

  const tables = (s.from ?? []).map((f: any) => f.table).filter(Boolean);
  if (!tables.length) return { ok: false as const, why: "no table" };
  for (const t of tables) {
    if (!ALLOWED_TABLES.has(t)) return { ok: false as const, why: `table ${t}` };
  }
  return { ok: true as const, ast, tables };
}
Enter fullscreen mode Exit fullscreen mode

Parse to an AST — never regex. "SELECT * FROM orders; DROP TABLE users" and
"select/*x*/ * from pg_shadow" both defeat string matching and neither
survives a parser that checks statement type and table names against an
allowlist.

Rejecting anything that is not a single select also rules out CTEs that
write (WITH x AS (DELETE ...)), which is a real Postgres capability people
forget.

Layer 3: the database enforces it anyway

Validation is code, and code has bugs. The last line has to be a role that
cannot do the thing.

CREATE ROLE agent_ro NOINHERIT LOGIN PASSWORD '...';
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM agent_ro;
GRANT SELECT ON orders, order_items, products TO agent_ro;

ALTER ROLE agent_ro SET statement_timeout = '5s';
ALTER ROLE agent_ro SET idle_in_transaction_session_timeout = '10s';
Enter fullscreen mode Exit fullscreen mode

A separate connection pool for the agent, on that role:

export const agentDb = new Pool({
  connectionString: process.env.AGENT_RO_URL,
  max: 4,
});
Enter fullscreen mode Exit fullscreen mode

Now a validator bug is a failed query rather than a data loss event. max: 4
also stops a runaway agent from starving your application pool, which is the
more likely incident of the two.

Add row-level security on top, so the agent's role physically cannot see other
tenants:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY agent_tenant ON orders TO agent_ro
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
Enter fullscreen mode Exit fullscreen mode

Four layers: shaped tools, AST validation, a read-only role, and row-level<br> security.

Layer 4: bound the result before it reaches the context

A valid, permitted, tenant-scoped query can still return two hundred thousand
rows — into a context window you are paying for on every subsequent turn.

export async function runAgentQuery(sql: string, ctx: Ctx) {
  const v = validateSelect(sql);
  if (!v.ok) return { error: `Query rejected: ${v.why}` };

  const client = await agentDb.connect();
  try {
    await client.query("BEGIN READ ONLY");
    await client.query("SET LOCAL app.tenant_id = $1", [ctx.tenantId]);
    await client.query("SET LOCAL statement_timeout = '5s'");

    const { rows } = await client.query(`SELECT * FROM (${sql}) q LIMIT 201`);
    await client.query("COMMIT");

    const truncated = rows.length > 200;
    return {
      rows: rows.slice(0, 200),
      truncated,
      note: truncated
        ? "Result truncated at 200 rows. Add aggregation or a narrower filter."
        : undefined,
    };
  } finally {
    client.release();
  }
}
Enter fullscreen mode Exit fullscreen mode

BEGIN READ ONLY is a second, independent guarantee against writes. The
LIMIT 201 wrapper tells you there was more without a second count query, and
the note tells the model what to do about it rather than leaving it to infer
that the list was cut.

SET LOCAL, not SET, because these are pooled connections and a
session-level setting would leak to the next borrower.

Explain the schema, do not expose it

The model needs to know what it can query. A raw information_schema dump is
both large and revealing.

const SCHEMA_DOC = `
orders(id, created_at, total_cents, status, product_id)
  status ∈ 'pending' | 'paid' | 'refunded' | 'cancelled'
  total_cents is an integer in cents, never a float
order_items(order_id, sku, qty, unit_cents)
products(id, sku, name, category)

Notes:
- Money is always cents. Divide by 100 only when presenting.
- Deleted orders are hidden automatically; do not filter on deleted_at.
- tenant_id is applied automatically; do not include it in your query.
`.trim();
Enter fullscreen mode Exit fullscreen mode

Hand-written, curated, and cached as a stable prompt prefix. It also carries
the semantics that prevent whole classes of wrong answer — the cents rule
alone prevents revenue figures that are off by a hundred.

Log the SQL, always 

logger.info("agent_sql", {
  runId: ctx.runId,
  tenantId: ctx.tenantId,
  sql,
  accepted: v.ok,
  reason: v.ok ? undefined : v.why,
  rows: result.rows?.length,
  ms,
});
Enter fullscreen mode Exit fullscreen mode

Rejected queries are the interesting ones. They tell you whether the model is
trying to do something it should not, and — more often, that your schema doc
is missing something and it is guessing.

A rejected query logged with its reason, feeding back into the schema<br> documentation.

The order that matters

Shaped tools first, because most questions do not need SQL. AST validation if
you must generate it. A read-only role with RLS so the validator is not the
last line. Bounded results so a valid query cannot flood the context.

Every layer assumes the one above it will eventually fail. That is the point —
"the prompt says SELECT only" is a single layer, and it is the one that fails
first.

If this was useful

AI That Acts covers tools that touch
real systems — capability scoping, validation boundaries, bounded results, and
the difference between a tool a model can use and one it can misuse.

AI That Acts — Tool Calling in TypeScript

The full series is at
xgabriel.com/ai-in-typescript.

Top comments (1)

Collapse
 
cekuu35 profile image
Cenk KURTOĞLU •

The layering is right, and SET LOCAL rather than SET is the detail almost everyone gets wrong on a pooled connection. Two gaps, both in the layers you present as the ones that hold when the one above fails.

1. RLS is on orders only, but the grant covers three tables.

GRANT SELECT ON orders, order_items, products TO agent_ro;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;   -- only orders
Enter fullscreen mode Exit fullscreen mode

If that is abbreviated for the post, ignore me. If it is literal, order_items is granted, in the validator allowlist, and has no row filter — so select * from order_items returns every tenant's line items, complete with sku, qty and unit_cents. The agent does not even need a join to get there, and Layer 4 caps it at 200 rows rather than zero.

This is the usual shape: the tenant column lives on the parent, the child table inherits the relationship but not the policy, and it gets missed precisely because it looks like a detail table. Worth a coverage check rather than a per-table memory:

select c.relname, c.relrowsecurity as rls_on, count(p.polname) as policies
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policy p on p.polrelid = c.oid
where n.nspname = 'public' and c.relkind = 'r'
group by 1,2 order by 2,1;
Enter fullscreen mode Exit fullscreen mode

Anything you granted to agent_ro that comes back rls_on = false is readable across tenants.

2. validateSelect only inspects top-level FROM.

const tables = (s.from ?? []).map((f: any) => f.table).filter(Boolean);
Enter fullscreen mode Exit fullscreen mode

A scalar subquery in the select list, or a subquery in WHERE, does not appear in s.from. So this parses, is a single select, and passes the allowlist:

select (select string_agg(usename || ':' || passwd, ',') from pg_shadow) from orders;
Enter fullscreen mode Exit fullscreen mode

Layer 3 saves you — agent_ro has no grant on pg_shadow, so it errors rather than returning anything. Which is exactly your thesis working. But it means the allowlist is doing less than it appears to, and you present it as the control for the open-ended case. Walking the AST recursively for every node with a table reference, instead of reading from once, closes it.

Neither of these breaks the argument. They are both cases of the layer below quietly carrying more weight than the one above, which is the thing worth knowing about your own stack.

Related, if useful — a runnable fixture where a policy exists, the tests are green, and rows still cross the boundary: github.com/cekuu35/supabase-rls-le...