PostgreSQL MCP server: read-only access for agents, approvals on writes
The library includes a PostgreSQL MCP server entry that needs one environment variable, DATABASE_URL. This page shows how to attach it to an agent so the agent can read, and how to put a person in front of any write.
Last reviewed 7 October 2026
Shipped template
The status rests on the postgres entry in the MCP tool catalogue, which is a server entry with one required variable, DATABASE_URL. It is not a finished process. Which tools it exposes is not listed, so the controls below sit in your database and in Studio.
A template that does this job exists in the Swfte library today. Content and availability vary by plan.
What this template does
An agent that can query your database turns a question into an answer without a ticket to the data team. The owner is usually the data or platform lead, who must say what the agent may touch. The failure by hand is a shared connection string with write rights pasted into a tool, and an agent that can then change data when someone prompts it badly.
The control that holds is the database role, not the instructions to the model. Give the agent a role that can only read the schemas you name, put its connection string in DATABASE_URL, and the agent cannot write however it is asked. For the rare write, a separate flow waits for a named person and uses a different credential.
The workflow, step by step
Steps marked as approval gates pause the run until a named person approves. Nothing after a gate runs before that decision, and the decision is recorded.
- 01
Create a read-only role
In PostgreSQL, create a role with login and SELECT only on the schemas or views the agent needs. Grant nothing on other schemas, and no insert, update, delete or DDL rights. This is done in your database, not in Studio, and it is the control everything else rests on. - 02
Set DATABASE_URL
Put the read-only role's connection string in the DATABASE_URL variable of the MCP server entry, stored as a secret. Where you can, point it at a replica, so that a heavy query cannot slow the live system. - 03
Attach to one agent
Attach the server to a single agent in Studio, rather than to every agent in the workspace. Give that agent a short brief: the questions it answers, the schemas it may read and a plain statement that it never changes data. - 04
Draft the query
The agent reads the table list and column names, then drafts SQL from the question, starting from the library prompt. It reads structure first and data second. The draft query is passed to the check, not run yet. - 05
Check before running
A code step in the workflow rejects anything that is not a single SELECT, adds a row limit and refuses queries over tables outside your list. The database role still blocks writes if the check is wrong. Both layers are kept. - 06
Person approves any write
A write is never run through the read-only connection. The statement is shown to a named person in a separate flow. After approval it runs under a different credential held only by that flow. Assign someone other than the requester.Approval gate: a named person approves before the next step runs. - 07
Answer and record
The agent returns the answer with the SQL it ran shown beside it, so the reader can check the logic. The run keeps the question, the SQL as run, the row count, any approved write and who approved it.
What it can do, cannot do, needs approval for, and records
Can
- Read tables and columns the role is granted
- Draft and run SELECT queries within limits
- Show the SQL it ran next to the answer
- Summarise results with a model
Cannot
- Write when connected through the read-only role
- Read schemas the role has no grant on
- Use a credential it was not given
- Prove its answer is right; the reader checks the SQL
Requires approval
- Every insert, update, delete or schema change
- Granting the agent a wider role
- Pointing the server at a production primary
Records
- The question asked
- The SQL as run and its row count
- The approved statement for any write
- Approver, decision and time
What the library holds for this job
| Asset | Type | Covers | Role in this flow |
|---|---|---|---|
| Postgres MCP server | MCP server entry | The job as described | The library entry for a PostgreSQL MCP server; it requires the DATABASE_URL environment variable. |
| Postgresql Data Analysis | Workflow template | Part of the job | Lists tables, runs a query and has a model summarise the result, using the PostgreSQL integration node rather than the MCP server. |
| Postgres MCP | Marketplace sample listing | Sample listing, no template behind it | A sample Marketplace listing described as read-only queries with row-level scopes; no template is seeded behind it. |
| Generate SQL Query | Prompt | Used inside the flow | A starting prompt for turning a question into SQL. |
| Postgres | Integration | Used inside the flow | The database the server connects to. |
Integrations this flow uses
- PostgreSQL: The database the MCP server connects to.
- Slack: Alerts the approver that a write is waiting.
- OpenAI: One model option for drafting SQL and summarising results; any model reachable through Connect can be used.
The full list of tools Studio connects to is on the integrations page.
What you supply, and what this page does not cover
- You create the database role, own its grants and rotate its password. Swfte holds the connection string you give it and cannot widen what the role may do.
- The catalogue does not state which tools the server exposes or whether it blocks writes itself. Treat the database role as the control and test it.
- Row-level scoping, if you need it, is set up in PostgreSQL. The Marketplace listing mentioning it is a sample only.
- Whether personal data may be sent to a model is for your data protection lead to decide. See the trust page.
Common questions
- Does the MCP server stop the agent from writing?
- The catalogue entry does not say, so do not rely on it. Use a database role with SELECT grants only. Then a write fails at the database whatever the agent is asked to do, which is a firmer control than any instruction in a prompt.
- What does the server need to run?
- One environment variable, DATABASE_URL, according to the entry in the tool catalogue. Store it as a secret. Beyond that, you need a database the server can reach and a role with the grants you intend.
- How do writes work if the agent is read-only?
- A separate flow shows the proposed statement to a named person. After approval it runs under a different credential held by that flow only. The read-only agent never holds a connection that can write.
- Is this the same as the PostgreSQL node in a workflow?
- No. The workflow node runs fixed operations you design. The MCP server lets an agent explore and choose queries itself. The first is more predictable, the second more flexible, so narrower grants matter more.