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.
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';
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
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);
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();
}
}
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));
}
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);
});
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)
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?
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_stocktool leaks.productsgets a column grant that includestenant_id, but onlyordersgets 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 wherehas_any_column_privilege('ai_reader', oid, 'SELECT')is true but RLS is off or no policy applies to the role. On this schema it returnedproducts.2. Free-form SQL can re-point the tenant. The model can call
set_configtoo: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 sawstatement_timeout = 0, andpg_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 insiderunAiQuerywithSET LOCAL(tenant checked against a UUID regex first); the model's SQL sent withqueryMode: "extended", so it is a single statement; andREVOKE USAGE ON LANGUAGE plpgsql FROM PUBLIC, because aDOblock could stillSET LOCALthe tenant and return B's totals in aRAISE EXCEPTIONmessage. Grant both back to the roles that need them.3. "Zero rows" only holds on a fresh connection. A query that skips
runAiQueryreturned 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 withinvalid 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), '')::uuidin 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_readerrole, or behind a statement allowlist?