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 Edited on AI-assisted

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

Updated Nov 18: thanks to @acemeridian, who ran every snippet and found three gaps: the products table had no row-level security, pooled connections return an empty string instead of NULL for the tenant setting, and free-form SQL can call set_config itself. All three are fixed below, re-tested on Postgres 16, and the test suite now has a check that would have caught the first one.

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;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

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

CREATE POLICY ai_tenant_isolation ON products
  FOR SELECT TO ai_reader
  USING (tenant_id = NULLIF(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 has never been set in this session. On a pooled connection that already served a request, it comes back as an empty string instead, which is what the NULLIF is for. Either way, a query that forgot to set the tenant gets zero rows instead of all of them. It fails closed.

Every table you grant to ai_reader needs its own policy. Granting a table and forgetting this step is the easiest way to undo everything else here, so section 6 has a test for it.

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, and think hard before adding free-form SQL. The role, grants and policies above are not enough on their own for model-written queries: ai_reader can call set_config itself, so a single query can switch app.tenant_id partway through and read another tenant's rows, or turn off statement_timeout for the pooled session. If you do need it, run the model's SQL through a parser-based allowlist (one SELECT, no function calls outside a short list, no DO blocks) before it reaches the database. Named tools avoid the whole problem.

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

The tests above only cover tables you remembered to test. This one asks Postgres for every table and view ai_reader can read, and fails if any of them has row-level security off or no policy for the role. Before the fix, it returned products:

test("every table the AI role can read has a policy for it", async () => {
  const rows = await runAiQuery(TENANT_A, `
    SELECT c.relname
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r', 'p', 'v', 'm')
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND has_any_column_privilege('ai_reader', c.oid, 'SELECT')
      AND (NOT c.relrowsecurity
           OR NOT EXISTS (SELECT 1 FROM pg_policy p
                          WHERE p.polrelid = c.oid
                            AND (0 = ANY (p.polroles)
                                 OR 'ai_reader'::regrole = ANY (p.polroles))))`);
  assert.deepEqual(rows, []);
});
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 (5)

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
 
redliolabs profile image
Redlio Labs •

Thanks! You've named the exact failure mode, and another reader has since shown a second way to hit it: if the model can write its own SQL, it can call set_config and switch the tenant mid-query. The post is updated with the fix.

On the 3s timeout: it's meant for questions someone is waiting on in a chat box, and in that setting it's held up. When a question really needs a slow analytical query, we don't raise the limit for the AI role. We point that tool at a precomputed summary table or a read replica with its own role and a longer timeout, so a slow query can never hold locks on the main database.

Collapse
 
kami_butt_69b922ade8de012 profile image
Agentic Affairs •

The set_config catch is a good one — the model writing its own SQL can reach straight past the tenant boundary, so the post update matters. The replica-and-summary-table split is the right call for slow queries; the tradeoff I'd watch is staleness — how fresh do those summary tables need to be before a chat answer starts misleading someone?

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?

Collapse
 
redliolabs profile image
Redlio Labs •

Thank you for running all of it and writing this up. I reproduced all three on Postgres 16 before changing anything, and you're right on each one.

  1. products had the grant but no policy, so low_stock returned both tenants. The post now enables RLS on products too, and your catalog query is in section 6 as a test. On my copy it returned products before the fix and nothing after.
  2. The set_config re-point worked exactly as you showed. To answer your question: in the setup as written, free-form SQL would have run as the same ai_reader role, so it wasn't safe. The post now says that plainly: named tools only, unless the model's SQL goes through a parser-based allowlist before it reaches the database.
  3. The pooled connection returned '' and the uuid cast errored, as you said. The policies now use NULLIF(current_setting('app.tenant_id', true), '')::uuid, and I added a note at the top of the post crediting you.

This is the most useful comment we've had on anything we've published. Thanks again.