Table Allowlists & Access Boundaries

A read-only database user is necessary but not sufficient. Even with SELECT-only permissions, the AI model can read from any table the user has access to — including system catalogs, audit logs, and tables containing sensitive data like passwords or tokens. Table allowlists add a second layer: the MCP server maintains a hardcoded list of tables it will query, and rejects any request targeting an unlisted table. This defense-in-depth approach means that even if the database role has broader permissions than intended, the MCP server constrains the exposure.

Allowlists should be explicit, not pattern-based. A glob like "public.*" technically works but defeats the purpose — it exposes every table in the public schema, including any you add later. Enumerate every table by name: ["customers", "orders", "products", "order_items"]. When you add a new table to the database, you must explicitly add it to the allowlist before the AI can see it. This friction is intentional — it forces a security review for every new data exposure.

Column-level allowlists provide even finer control. Instead of exposing all columns in a table, define which columns the AI can read. A customers table might expose id, name, company, and status but not ssn, password_hash, or internal_notes. Column allowlists are specified per table and enforced by constructing SELECT statements with explicit column lists instead of SELECT *. This prevents accidental exposure when new sensitive columns are added to existing tables.

// Table + column allowlist configuration
interface TableAccess {
  table: string;
  columns: string[];       // Explicit column list — no SELECT *
  maxRows: number;         // Per-query row limit
  description: string;     // Why this table is exposed
}

const ALLOWED_TABLES: TableAccess[] = [
  {
    table: "customers",
    columns: ["id", "name", "company", "status", "created_at"],
    maxRows: 500,
    description: "Customer lookup — excludes PII (ssn, email, phone)",
  },
  {
    table: "orders",
    columns: ["id", "customer_id", "status", "total", "created_at"],
    maxRows: 1000,
    description: "Order history — excludes payment details",
  },
  {
    table: "products",
    columns: ["id", "name", "sku", "category", "price", "active"],
    maxRows: 2000,
    description: "Product catalog — full read access",
  },
];

function buildSelectQuery(tableName: string, whereClause?: string): string {
  const access = ALLOWED_TABLES.find((t) => t.table === tableName);
  if (!access) throw new Error(`Table "${tableName}" is not exposed`);

  const cols = access.columns.map((c) => `"${c}"`).join(", ");
  let sql = `SELECT ${cols} FROM "${access.table}"`;
  if (whereClause) sql += ` WHERE ${whereClause}`;
  sql += ` LIMIT ${access.maxRows}`;
  return sql;
}