Postgres

The Postgres integration gives agents and dashboards a broad read surface without giving them a general database credential. The runner can discover the schema, inspect relations, review query plans, and execute bounded SQL. It works with standard Postgres deployments, including managed services such as Supabase.

During early access, Polycore prepares and validates this integration with your team. The examples below show the expected IAM and configuration shape; they are not a request to send us your connection string.

Architecture

The Postgres URL is supplied to the runner as POLYCORE_POSTGRES_URL. It does not appear in polycore.json, capability metadata, or an agent prompt.

Read-only enforcement

The integration uses independent controls:

Database role

The connection should use a role with CONNECT, schema USAGE, and SELECT only, or connect to a read replica with equivalent permissions.

Runner execution

PostgreSQL first describes caller SQL without executing it. The extended protocol accepts exactly one statement, and the runner requires result columns before executing that same SQL with bound params inside BEGIN TRANSACTION READ ONLY. Multi-statement payloads, transaction control, and non-row-returning utilities fail before execution; writes with RETURNING still fail in the read-only transaction. End-to-end and per-statement deadlines plus a bounded admission queue protect the database if the role is accidentally over-privileged.

Results have independent row and encoded-byte ceilings and are collected with a server cursor, rather than materialized without a bound. The response reports truncated and either maxRows or maxBytes as truncationReason. Agents are instructed to aggregate in SQL and request only the columns they need.

1. Create the database role

We adapt this SQL to your database, schemas, ownership model, and migration roles:

Role template

CREATE ROLE polycore_readonly
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
  PASSWORD '<generated-secret>';

GRANT CONNECT ON DATABASE app TO polycore_readonly;
GRANT USAGE ON SCHEMA public TO polycore_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO polycore_readonly;

ALTER DEFAULT PRIVILEGES
  FOR ROLE app_owner
  IN SCHEMA public
  GRANT SELECT ON TABLES TO polycore_readonly;

ALTER ROLE polycore_readonly
  SET statement_timeout = '10s';

ALTER ROLE polycore_readonly
  SET default_transaction_read_only = on;

ALTER DEFAULT PRIVILEGES applies to objects created by the named owner. Repeat it for each role that creates tables, or add the equivalent grant to your migration process. Existing grants do not automatically cover future tables.

A read-only transaction constrains PostgreSQL data changes, but a SELECT can invoke a function. Review EXECUTE grants on customer functions and extensions, especially security-definer functions, dblink, foreign-data wrappers, and any function with network or filesystem effects. Do not grant this role membership in a broader role. A physical read replica is the strongest database-level backstop when operational isolation matters more than freshness; we confirm acceptable replication lag during scoping.

2. Store the connection string

Your deployment layer stores a URL for the read-only role and injects it only into the runner:

POLYCORE_POSTGRES_URL=postgresql://polycore_readonly:<secret>@db.internal:5432/app?sslmode=require

Recommended controls:

Require TLS and verify the server certificate.
Prefer private network connectivity from the runner.
Rotate the password through your existing secret manager.
Restrict source networks where the database supports it.
Use a dedicated role per environment.

3. Enable the query family

The committed runner config declares the capability surface: An empty object takes the production defaults; a limits block commits stricter ceilings:

polycore.json

{
  "integrations": {
    "postgres": {
      "limits": {
        "requestTimeoutMs": 15000,
        "statementTimeoutMs": 5000,
        "acquireTimeoutMs": 2000,
        "maxRows": 500,
        "maxResultBytes": 131072,
        "poolSize": 2,
        "maxQueue": 8,
        "cursorBatchRows": 50,
        "discoveryLimit": 200,
        "maxSqlBytes": 65536,
        "maxParams": 100,
        "maxParamBytes": 131072
      }
    }
  },
  "context": { "file": "./context/product.md" }
}

These are customer-reviewed ceilings. A call may request fewer rows or bytes, but never more. POLYCORE_POSTGRES_URL remains absent from the file.

Available capabilities

read

Searches visible non-system schemas with a bounded limit. Returns schema comments and explicit truncation metadata.

read

Searches visible relations by schema, name, and type. Returns comments, estimated rows, total bytes, and explicit truncation metadata.

read

Returns precise formatted column types, comments, identity/generated flags, primary and unique keys, composite-safe outgoing and incoming foreign keys, checks, indexes, enum values, row estimates, and table size.

read

Plans the same structurally bounded query and bound params without executing it, reporting estimated root cost and rows.

read

Describes and then runs one row-returning extended-protocol SELECT or WITH ... SELECT with bound positional params. Duplicate result names are rejected so object-row mapping cannot overwrite a column. Returns {rows, rowCount, truncated, truncationReason?} within the configured row and encoded-byte ceilings.

The write family below is advertised only when the runner config grants it.

write

Previews one UPDATE and reports the exact rows, columns, and values it would change, then applies only those rows and only if none has been written since.

write

Previews one DELETE, reporting the rows it would remove plus every table reachable through a foreign key, including rows a cascade would take and any restraint that would make the delete fail.

How an agent reads safely

Discover

The agent lists schemas and tables rather than guessing relation names.

Ground

It describes the relevant relations and uses foreign-key metadata to form valid joins.

Plan when needed

For a potentially expensive request, it asks Postgres for a query plan before execution.

Bind values and query a bounded result

It writes runtime values as $1, $2, and so on and sends them separately in params. The runner binds them, adds structural read-only enforcement, and stops collection at the row or byte ceiling.

Return through the governed path

Rows or a structured database error return to the control plane and are linked to the original caller and request.

Representative request

How many active accounts were created in the last seven days?

The agent can inspect the live schema, identify the relevant timestamp and status fields, and execute an aggregate SELECT. It does not need a custom action for that question.

Parameterized runtime filters

Values from a dashboard control, form, URL, or caller must never be interpolated into SQL:

await polycore.run("analytics/postgres.query", {
  sql: `
    SELECT date_trunc('day', created_at) AS day, count(*)::int AS accounts
    FROM accounts
    WHERE status = $1
      AND created_at >= $2::timestamptz
    GROUP BY 1
    ORDER BY 1
  `,
  params: [status, startDate],
  maxRows: 100,
});

Params bind values only. A dynamic table, column, operator, or sort direction must be selected from fixed SQL fragments in the page code. Never put the raw UI value into SQL syntax.

Writes

The query family above cannot INSERT, UPDATE, DELETE, or run DDL, and it never can: it is the read path, and its role should be one the database itself prevents from mutating. A stable, repeated write workflow belongs in a named action with a narrow input schema, explicit business rules, and a separately scoped credential. For one-off changes, the optional Postgres write family adds postgres.update and postgres.delete. It is off until the runner config grants it, and the grant may name a separate connection so the read path stays mutation-incapable at the database:

{
  "integrations": {
    "postgres": {
      "urlEnv": "POLYCORE_POSTGRES_URL",
      "write": { "urlEnv": "POLYCORE_POSTGRES_WRITE_URL" }
    }
  }
}

Do not reuse POLYCORE_POSTGRES_URL as the write credential. Keep generic reads on a role that the database itself prevents from mutating. Omitting write.urlEnv deliberately reuses the read connection, so only do that when that role already has write access anyway.

Every write is previewed before it runs

A SQL predicate does not name its rows until it is evaluated, so showing an approver the statement shows them the wrong thing. UPDATE profiles SET status = 'suspended' WHERE email LIKE '%@acme.com' reads as one careful change and hits fourteen rows, including the CI bot. So the statement is parsed with PostgreSQL’s own parser, rewritten into an equivalent SELECT, and run inside a read-only transaction. That reports:

every row that would change, with before and after values;
the columns the statement assigns;
every table reachable through a foreign key, and whether a cascade would delete those rows or a restraint would make the whole delete fail;
anything the preview could not see, such as a BEFORE trigger that may rewrite the values actually written.

Two properties follow from the mechanism rather than from a rule:

The approved set cannot grow

The apply names primary keys, so a row that starts matching the predicate while a human is deciding is not eligible. Fourteen rows were shown; exactly those fourteen can change.

It lands whole or not at all

Each row is matched on the version it had at preview time. A row that was written in the meantime aborts the entire apply inside one transaction, so a stale approval changes nothing rather than half.

Anything the rewriter cannot transform faithfully is refused rather than approximated: a missing WHERE clause, a WITH clause, UPDATE ... FROM, DELETE ... USING, DDL, a table with no primary key. A statement matching more rows than the configured limit (1000 by default) is refused too, on the grounds that an approval which does not name its rows was not really an approval.

Why this is what makes unattended SQL writes reasonable

Because the preview reports what a statement would do, your access policy can decide on the consequence instead of the text. A rule can let a small, in-scope status change dispatch immediately while the same capability, used against a different table or two thousand rows, waits for a human. An agent gets to write the SQL the task actually needs, inside bounds you declared.

Production validation

We verify all of the following with your team:

Schema and relation discovery work only in the intended database and schemas.

A normal aggregate read succeeds and produces a bounded result.

COMMIT; DELETE, multiple statements, transaction control, utility commands, INSERT, UPDATE, DELETE, and DDL all fail before they can mutate data.

Hostile quote/comment text sent as a bound param remains a literal value.

Oversized row sets and wide values stop with maxRows or maxBytes truncation metadata.

Long-running work stops at the configured statement timeout.

The runner can reconnect without exposing the database URL to the control plane.

Query arguments, outcome, target environment, and caller appear in the audit path.

Common design questions

Can this connect to Supabase?

Yes. Supabase runs Postgres. We create a dedicated database role and supply its connection string to the runner rather than using a broad service credential in an agent.

Does row-level security still apply?

It depends on the role and your policies. We review RLS behavior explicitly rather than assuming it. Table grants and RLS solve different parts of the access model.

What about multiple schemas?

Grant USAGE and SELECT only for the schemas in scope. The agent can call postgres.listSchemas and qualify relation names as schema.table.

What about large analytics queries?

Prefer an analytics replica or warehouse. The 10-second timeout and result cap protect the interactive path; they are not a substitute for workload isolation.

Integration model

Data platforms

Interfaces

Build the catalog

Architecture

Read-only enforcement

Database role

Runner execution

1. Create the database role

2. Store the connection string

3. Enable the query family

Available capabilities

How an agent reads safely

Representative request

Parameterized runtime filters

Writes

Every write is previewed before it runs

The approved set cannot grow

It lands whole or not at all

Why this is what makes unattended SQL writes reasonable

Production validation

Common design questions

​Architecture

​Read-only enforcement

Database role

Runner execution

​1. Create the database role

​2. Store the connection string

​3. Enable the query family

​Available capabilities

​How an agent reads safely

​Representative request

​Parameterized runtime filters

​Writes

​Every write is previewed before it runs

The approved set cannot grow

It lands whole or not at all

​Why this is what makes unattended SQL writes reasonable

​Production validation

​Common design questions

Architecture

Read-only enforcement

1. Create the database role

2. Store the connection string

3. Enable the query family

Available capabilities

How an agent reads safely

Representative request

Parameterized runtime filters

Writes

Every write is previewed before it runs

Why this is what makes unattended SQL writes reasonable

Production validation

Common design questions