> ## Documentation Index
> Fetch the complete documentation index at: https://docs.polycore.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Postgres

> How a Polycore runner connects to Postgres with layered read-only enforcement, and how optional writes are previewed row by row before they run.

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.

<Info>
  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.
</Info>

## Architecture

```mermaid theme={"system"}
flowchart LR
  Caller["Agent or dashboard"]
  CP["Polycore control plane"]
  Runner["Runner in your infrastructure"]
  Role["Postgres read-only role"]
  DB[("Postgres or read replica")]

  Caller --> CP
  CP -- "Signed read dispatch" --> Runner
  Runner --> Role --> DB
```

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:

<Columns cols={2}>
  <Card title="Database role" icon="user-lock">
    The connection should use a role with `CONNECT`, schema `USAGE`, and
    `SELECT` only, or connect to a read replica with equivalent permissions.
  </Card>

  <Card title="Runner execution" icon="shield">
    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.
  </Card>
</Columns>

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:

```sql Role template theme={"system"}
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;
```

<Warning>
  `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.
</Warning>

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:

```text theme={"system"}
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:

```json polycore.json theme={"system"}
{
  "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

<ResponseField name="postgres.listSchemas" type="read">
  Searches visible non-system schemas with a bounded limit. Returns schema
  comments and explicit truncation metadata.
</ResponseField>

<ResponseField name="postgres.listTables" type="read">
  Searches visible relations by schema, name, and type. Returns comments,
  estimated rows, total bytes, and explicit truncation metadata.
</ResponseField>

<ResponseField name="postgres.describe" type="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.
</ResponseField>

<ResponseField name="postgres.explain" type="read">
  Plans the same structurally bounded query and bound params without executing
  it, reporting estimated root cost and rows.
</ResponseField>

<ResponseField name="postgres.query" type="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.
</ResponseField>

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

<ResponseField name="postgres.update" type="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.
</ResponseField>

<ResponseField name="postgres.delete" type="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.
</ResponseField>

## How an agent reads safely

<Steps>
  <Step title="Discover">
    The agent lists schemas and tables rather than guessing relation names.
  </Step>

  <Step title="Ground">
    It describes the relevant relations and uses foreign-key metadata to form
    valid joins.
  </Step>

  <Step title="Plan when needed">
    For a potentially expensive request, it asks Postgres for a query plan
    before execution.
  </Step>

  <Step title="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.
  </Step>

  <Step title="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.
  </Step>
</Steps>

### Representative request

```text theme={"system"}
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:

```ts theme={"system"}
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](/authoring/actions) 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:

```json theme={"system"}
{
  "integrations": {
    "postgres": {
      "urlEnv": "POLYCORE_POSTGRES_URL",
      "write": { "urlEnv": "POLYCORE_POSTGRES_WRITE_URL" }
    }
  }
}
```

<Warning>
  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.
</Warning>

### 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:

<Columns cols={2}>
  <Card title="The approved set cannot grow" icon="lock">
    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.
  </Card>

  <Card title="It lands whole or not at all" icon="undo-2">
    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.
  </Card>
</Columns>

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](/concepts/approvals-and-audit) 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:

<Check>
  Schema and relation discovery work only in the intended database and schemas.
</Check>

<Check>A normal aggregate read succeeds and produces a bounded result.</Check>

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

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

<Check>
  Oversized row sets and wide values stop with `maxRows` or `maxBytes`
  truncation metadata.
</Check>

<Check>Long-running work stops at the configured statement timeout.</Check>

<Check>
  The runner can reconnect without exposing the database URL to the control
  plane.
</Check>

<Check>
  Query arguments, outcome, target environment, and caller appear in the audit
  path.
</Check>

## Common design questions

<AccordionGroup>
  <Accordion title="Can this connect to Supabase?" icon="database">
    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.
  </Accordion>

  <Accordion title="Does row-level security still apply?" icon="rows-3">
    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.
  </Accordion>

  <Accordion title="What about multiple schemas?" icon="layers-3">
    Grant `USAGE` and `SELECT` only for the schemas in scope. The agent can call
    `postgres.listSchemas` and qualify relation names as `schema.table`.
  </Accordion>

  <Accordion title="What about large analytics queries?" icon="chart-no-axes-combined">
    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.
  </Accordion>
</AccordionGroup>