Dev.to Security πŸ” Cybersecurity πŸ‘ 0 πŸ“– 5 min read

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 conne

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';

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.

πŸ“° Read the original article on Dev.to Security

Originally published by Dev.to Security. Aggregated on AIWithGhost for educational purposes β€” full credit and traffic to the original publisher.