@lumifai/harness-tool-pack-postgres

PostgreSQL schema inspection, JSON query, guarded insert, and allowlisted file-write tools for the Lumif harness.

Setup

import { createPostgresToolPack } from '@lumifai/harness-tool-pack-postgres';

createPostgresToolPack({
  connectionString: process.env.DATABASE_URL,
  // Per-tenant credentials: return a connection string for a least-privilege,
  // RLS-bound role. Access control is enforced in PostgreSQL, not in the pack.
  resolveConnectionString: async (context) => tenantDatabaseUrl(context),
});

To enable the file-based insert/update tools, add explicit table and column allowlists to the same pack. Without allowedTables, those tools are not registered.

createPostgresToolPack({
  connectionString: process.env.DATABASE_URL,
  allowedTables: ['public.campaign_briefs'],
  allowedColumns: {
    'public.campaign_briefs': ['id', 'name', 'metadata'],
  },
  jsonColumns: { 'public.campaign_briefs': ['metadata'] },
});

Access control (which schemas, tables, columns, and rows are visible or writable) is enforced in the database by the connection role's GRANTs and row-level security policies. Provision a dedicated role with the minimum privileges needed (for example SELECT for reads and INSERT only on approved tables) and connect as that role; the tools expose exactly what the role can see and write.

Tools

| Tool | Purpose | | ----------------------------- | ------------------------------------------------- | | postgres_list_schemas | List accessible schemas with relation counts | | postgres_get_schema_details | Columns, keys, foreign keys, optional indexes | | postgres_preview_data | Bounded preview of one relation | | postgres_query | Execute a validated JSON QueryPlan | | postgres_insert | Insert rows from inline JSON or a workspace CSV | | postgres_insert_from_file | Insert rows from an allowlisted workspace JSON | | postgres_update_from_file | Update one row from an allowlisted workspace JSON |

Raw SQL is intentionally unsupported.

Insert sources

postgres_insert accepts one table target and a discriminated source:

{
  "schema": "public",
  "relation": "users",
  "source": {
    "kind": "json",
    "columns": ["email", "active"],
    "rows": [["[email protected]", true]]
  }
}

{
  "schema": "public",
  "relation": "users",
  "source": {
    "kind": "csv",
    "path": "/data/users.csv"
  }
}

CSV paths must be absolute within the Mastra workspace filesystem (for example /data/users.csv). Host filesystem paths are rejected. Partial success is intentional: valid rows commit; rejected rows are returned with row indexes and error codes. Structural failures (missing workspace, bad CSV, unknown table) fail the tool call.

Security model

Defense in depth:

1. PostgreSQL least-privilege role, RLS, column grants, and per-tenant credentials (the authority for access control)
2. No raw SQL: the model emits validated JSON plans or insert payloads, compiled server-side and parameterized via pg-sql2
3. Read-only transactions for queries; read-write transactions with per-row savepoints for inserts; timeouts; hard row/byte/cell limits
4. Schema metadata is loaded on demand (schema list is a single lightweight query; details/query compile load only the schemas they need) and cached briefly per connection fingerprint
5. CSV inserts read only through the configured Mastra workspace filesystem

Extension functions are disabled by default. If enabled, configure and call them with the exact schema-qualified name, such as public.mask_email. Use date_part(field, source) instead of EXTRACT. current_date / current_timestamp are supported as niladic keywords.

Bundled agent skills ship in this package and are merged into workspace.skills when the pack is enabled.