DEV Community

Cover image for Your AI agent can read the whole database. Give it a Postgres role that can't
Redlio Labs
Redlio Labs

Posted on AI-assisted

Your AI agent can read the whole database. Give it a Postgres role that can't

Most "chat with your data" features start the same way. Someone wires an agent (or an MCP server) to the app's existing database connection, adds a tool that runs a query, and the demo works on the first try.

That connection usually belongs to the app. It can read every table, every column, every tenant. It can write. Whatever the model is talked into doing, it does with those rights.

Prompt instructions like "only answer questions about the current customer" are not access control. They are a request. The database is the one place that can actually say no, so that is where the limits belong.

Here is the setup we use when we add an AI feature to an existing Postgres-backed app. It takes about an hour, and none of it depends on which model or agent framework you use. Every snippet below was run against Postgres 16 with the tests at the end passing.

Four layers between the agent and your data: role, column grants, row-level security, leak test

1. Give the AI layer its own role

The AI code path should never borrow the app's credentials. Create a role that can only log in and read:

CREATE ROLE ai_reader LOGIN PASSWORD 'use-a-secret-manager'
  NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- Fail fast and never write, even if someone forgets BEGIN READ ONLY
ALTER ROLE ai_reader SET default_transaction_read_only = on;
ALTER ROLE ai_reader SET statement_timeout = '3s';
ALTER ROLE ai_reader SET idle_in_transaction_session_timeout = '5s';
Enter fullscreen mode Exit fullscreen mode

The timeout matters more than it looks. A model that writes its own SQL will eventually write a cross join over your two biggest tables. Three seconds is plenty for an answer a person is waiting on, and short enough that it can't hold locks or starve the app.

2. Grant columns, not tables

Decide what the assistant actually needs to see, and grant exactly that:

GRANT USAGE ON SCHEMA public TO ai_reader;

GRANT SELECT (id, tenant_id, status, total_cents, created_at)
  ON orders TO ai_reader;

GRANT SELECT (id, tenant_id, name, sku, stock)
  ON products TO ai_reader;

-- customers, payments, users: no grant at all
Enter fullscreen mode Exit fullscreen mode

Column grants are boring and that's the point. If customers.email is not granted, no prompt in the world gets it into a response. When a product manager later asks for the assistant to "see customer names", it becomes a one-line diff someone has to review, instead of something that was quietly possible all along.

3. Pin every query to one tenant with row-level security

In a multi-tenant app, the worst failure isn't a wrong answer. It's a correct answer about someone else's data.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY ai_tenant_isolation ON orders
  FOR SELECT TO ai_reader
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
Enter fullscreen mode Exit fullscreen mode

The second argument to current_setting makes it return NULL when the setting is missing, so a query that forgot to set the tenant gets zero rows instead of all of them. It fails closed.

One warning: if your main app connects as a role that is not the table owner, enabling RLS will hide rows from it too until you add a policy for that role. Try this on a staging copy first.

4. Set the tenant inside a transaction

Connection pools reuse sessions, so a plain SET can leak one tenant's setting into the next request. Use set_config with is_local = true, which only lasts until the transaction ends:

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.AI_READER_URL });

export async function runAiQuery(tenantId, sql, params = []) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN READ ONLY");
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    const { rows } = await client.query(sql, params);
    await client.query("COMMIT");
    return rows.slice(0, 200); // the model doesn't need 50,000 rows
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}
Enter fullscreen mode Exit fullscreen mode

The tenantId comes from your session, never from the model's tool arguments. If the model can choose the tenant, the policy is decoration.

5. Prefer named tools over free-form SQL

Everything above still holds if you let the model write SQL. But most features don't need that. A short list of named queries covers the questions people actually ask, and it is far easier to reason about:

const tools = {
  order_status: {
    sql: "SELECT id, status, total_cents, created_at FROM orders WHERE id = $1",
    params: (args) => [String(args.orderId)],
  },
  recent_orders: {
    sql: "SELECT id, status, created_at FROM orders ORDER BY created_at DESC LIMIT 20",
    params: () => [],
  },
  low_stock: {
    sql: "SELECT name, sku, stock FROM products WHERE stock < $1 ORDER BY stock",
    params: (args) => [Number(args.threshold ?? 10)],
  },
};

export async function callTool(tenantId, name, args = {}) {
  const tool = tools[name];
  if (!tool) throw new Error(`Unknown tool: ${name}`);
  return runAiQuery(tenantId, tool.sql, tool.params(args));
}
Enter fullscreen mode Exit fullscreen mode

Start here. Add free-form SQL later, if users keep asking things the tools can't answer, and only after the role, grants and policies are in place.

6. Write the test that tries to leak

This is the part that keeps the setup honest six months from now, when someone adds a table and forgets the policy. Run it in CI against a seeded database with two tenants:

import test from "node:test";
import assert from "node:assert/strict";
import { runAiQuery } from "./ai-db.js";

const TENANT_A = process.env.TEST_TENANT_A;

test("AI role only sees its own tenant", async () => {
  const rows = await runAiQuery(TENANT_A, "SELECT tenant_id FROM orders");
  assert.ok(rows.length > 0);
  assert.ok(rows.every((r) => r.tenant_id === TENANT_A));
});

test("AI role cannot read tables it was never granted", async () => {
  await assert.rejects(
    runAiQuery(TENANT_A, "SELECT email FROM customers"),
    /permission denied/
  );
});

test("AI role cannot write", async () => {
  await assert.rejects(
    runAiQuery(TENANT_A, "UPDATE orders SET status = 'refunded'"),
    /read-only|permission denied/
  );
});

test("missing tenant returns nothing", async () => {
  const rows = await runAiQuery("00000000-0000-0000-0000-000000000000", "SELECT id FROM orders");
  assert.equal(rows.length, 0);
});
Enter fullscreen mode Exit fullscreen mode

If someone later grants SELECT on a whole table, or creates a new table without a policy and points a tool at it, one of these goes red before it ships.

What this doesn't cover

The database can stop the model from reading what it shouldn't. It can't stop the model from misreading what it should. You still need evaluation on real questions, and a way to switch the feature off without a deploy. Those are separate posts.

But the order matters. Put the limits in Postgres first, then work on how good the answers are. An assistant that is sometimes wrong is a product problem. An assistant that shows one customer another customer's orders is an incident.


I'm Mayursinh, founder of Redlio Labs. We build and add AI features to custom software for mid-size companies, and this is the checklist we run before any of them touch production data.

Top comments (2)

Collapse
 
kami_butt_69b922ade8de012 profile image
Agentic Affairs •

Prompt instructions are not access control — that sentence alone is worth the post. The detail I'd steal is the fail-closed RLS default: a missing tenant setting returns zero rows instead of all of them. Most teams get the read-only role right and then leak everything through a pool session that forgot to reset the tenant. Have you seen the 3s statement timeout ever cut off a legitimately slow analytical query, or is that the right ceiling in practice?

Collapse
 
acemeridian profile image
Ace Meridian •

I ran sections 1–6 as published on PostgreSQL 16.4 with node-postgres 8.23.1: two tenants, three orders and two products each (the seed schema is mine, since the post doesn't show one). Your four leak tests pass. Three things they don't catch:

1. The low_stock tool leaks. products gets a column grant that includes tenant_id, but only orders gets RLS. callTool(A, "low_stock", { threshold: 10 }) returned tenant B's "B prototype" and "B bolt" next to A's widget. A CI query over the catalog catches this without knowing the tool list: any relation where has_any_column_privilege('ai_reader', oid, 'SELECT') is true but RLS is off or no policy applies to the role. On this schema it returned products.

2. Free-form SQL can re-point the tenant. The model can call set_config too:

SELECT o.tenant_id, o.total_cents
FROM (SELECT set_config('app.tenant_id', '<tenant B>', true) AS t OFFSET 0) s
CROSS JOIN LATERAL (SELECT tenant_id, total_cents FROM orders WHERE s.t IS NOT NULL OFFSET 0) o
Enter fullscreen mode Exit fullscreen mode

runAiQuery(A, ...) returned B's three orders. SELECT set_config('statement_timeout', '0', false) switched off the 3 s limit for the pooled session: the next request saw statement_timeout = 0, and pg_sleep(5) ran 5.01 s instead of being cancelled at 3 s. What closed both in my runs: REVOKE EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) FROM PUBLIC; tenant and timeout set inside runAiQuery with SET LOCAL (tenant checked against a UUID regex first); the model's SQL sent with queryMode: "extended", so it is a single statement; and REVOKE USAGE ON LANGUAGE plpgsql FROM PUBLIC, because a DO block could still SET LOCAL the tenant and return B's totals in a RAISE EXCEPTION message. Grant both back to the roles that need them.

3. "Zero rows" only holds on a fresh connection. A query that skips runAiQuery returned 0 rows on a new session. On a pooled connection that had already served one request, current_setting('app.tenant_id', true) is '', not NULL, so the same query failed with invalid input syntax for type uuid: "". Still no leak, but an error instead of an empty result, depending on which connection the pool hands out. Your fourth test passes the zero UUID, so it never takes that path. NULLIF(current_setting('app.tenant_id', true), '')::uuid in the policy returned 0 rows in both cases.

Your four tests still pass with all of that in place. Does the free-form SQL path in your setup run as the same ai_reader role, or behind a statement allowlist?