How to connect PostgreSQL to an AI agent — with approval on writes

This is the whole path from a PostgreSQL database to an agent your team can ask questions, with a person standing between the agent and anything destructive. It is an afternoon's work, most of it spent deciding what the agent should not see.

Before you start

Make a database role for Caterfli and give it the least it needs. The platform's own restrictions are a second line of defence, not a substitute for database permissions: if the role cannot write, nothing on the platform can write, whatever an agent proposes. A read-only start might look like this:

CREATE ROLE caterfli LOGIN PASSWORD 'choose-a-long-one';
GRANT CONNECT ON DATABASE shop TO caterfli;
GRANT USAGE ON SCHEMA public TO caterfli;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO caterfli;

Then decide how Caterfli reaches the database. It does not need to be open to the internet. Either allow-list the addresses Caterfli connects from — the connection page lists them, with a ready-made pg_hba.conf line — or tick Connect through an SSH tunnel and go through a bastion host you control.

1. Add the connection

Under Connections, choose PostgreSQL and fill in the host, the port (5432 by default), the database name, a schema if it is not the default, and the role's username and password. The password is encrypted before it is stored and decrypted only inside the connector when a call is made. The model never sees it.

2. Let discovery run

Caterfli reads tables, columns, types and foreign keys directly. Every visible table gets a list tool and a count tool — list_orders, count_orders — with the real column list baked into each tool's argument schema. A describe_schema tool lets the agent work out which table answers a question.

This is the step where most of the real decisions are made:

• Untick tables the agent has no business reading. Every table is included by default. A shorter list also helps the model choose the right tool.
• Add row filters if the scope is narrower than a table — orders.status in paid,shipped, for instance. Reads, counts, updates and deletes all stay inside the filter.
• Turn writes on only if you need them. A new connection is read-only. Allow write operations adds create, update and delete tools for the selected tables.
• Leave ad-hoc SQL off unless you need analysis no generated tool covers. It is the widest setting on the page.

Even with writes on, schema changes and any UPDATE or DELETE without a WHERE clause are refused.

3. Review the agent

Caterfli drafts an agent from what discovery found, with every tool attached and an approval flag on each. Reads flow through. Deletes arrive enabled but gated, so each one stops for a manager, admin or owner to approve that exact call. Create and update tools do not need approval by default; tick it on the ones you want a person to see first. Any change whose arguments say it touches more than 10 records stops regardless.

Try it in the test panel first; it shows every proposed call with its arguments.

4. Ask

Deploy, and the agent appears in web chat. Add Slack, WhatsApp or Telegram if that is where your team already talks; the same checks apply through every door.

What runs underneath

A few limits apply to every call without anyone configuring them:

• Values are bound parameters, never pasted into SQL text.
• Reads are capped by row count and payload size.
• A statement_timeout is set on the session, so a runaway query is cancelled by PostgreSQL itself rather than left running after Caterfli stops waiting.

When the schema changes, re-run discovery from the connection page. The details of each setting are on the PostgreSQL integration page, and the full reference is in the docs.