How to Configure n8n Postgres Approval

Published
Length
4 min read

Luis Felipe Dias

All posts

You set up n8n postgres approval by placing a pause step before the database node. You also pair that workflow step with strict database role rules. Many teams connect automation workflows straight to production databases with shared admin keys. When an automated step or AI agent runs with broad rights, mistakes happen fast. A bad prompt or broken loop can alter rows or expose private data. How native n8n postgres approval and roles work In n8n, workflows run a chain of nodes from a trigger t

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up n8n postgres approval by placing a pause step before the database node. You also pair that workflow step with strict database role rules. Many teams connect automation workflows straight to production databases with shared admin keys. When an automated step or AI agent runs with broad rights, mistakes happen fast. A bad prompt or broken loop can alter rows or expose private data.

How native n8n postgres approval and roles work

In n8n, workflows run a chain of nodes from a trigger to an action. You can build an AI Agent node that links a chat model to tools. You must attach at least one tool sub-node to an AI Agent node. The agent decides which tools to call in order to finish a task. For SQL tasks, you attach a Postgres tool sub-node to the agent node. You can also use a Call n8n Workflow tool for custom steps.

To pause a run, builders add a wait or message step before the query. The workflow sends a message to a reviewer and pauses the execution state. Once a human clicks approve, the workflow resumes and runs the Postgres node. On the database side, admins try to limit what that node can do. They create group roles to manage privileges for many users at once. Users or service accounts then receive membership in the group role.

Admins can grant the SELECT privilege on a table, view, or materialized view. That privilege also lets a role read specific columns instead of the whole table. Keep in mind that the SELECT privilege also allows COPY TO operations. By default, the special PUBLIC role gets CONNECT and TEMPORARY rights on databases. It also gets EXECUTE rights on functions and procedures unless the owner revokes them. In PostgreSQL 15 and newer, regular users cannot create objects in the public schema. On older databases, an admin must revoke CREATE on the public schema from PUBLIC.

Why workflow pauses and static roles fall short

Workflow pause nodes and database roles solve only half of the safety problem. A workflow pause checks the step inside the builder, not the live database wire. If an AI agent calls a Postgres tool sub-node directly, it skips workflow pauses. The agent writes and runs SQL queries on its own during the turn. Even if you route the agent through a sub-workflow, maintenance grows hard very fast. Every new workflow needs its own chat alert, wait timer, and error branch.

Database roles also have clear limits during live runs. A role decides who can log in and which objects they can touch. It cannot check the text of a single SQL statement in flight. If a workflow needs to update one row, the role needs UPDATE rights. That same right lets a bad query update every row in the table. Column grants take hours to maintain across hundreds of changing tables. Teams end up using shared credentials stored inside the workflow tool settings. Anyone with editor access in the automation app can use those stored database keys. Static roles also cannot rewrite sensitive fields like emails or phone numbers in results.

Approval Chains & Escalation + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What to look for in query-level approval

A safer setup moves the check out of the workflow canvas. You want controls that sit in front of the database connection itself. Every query from any workflow or agent should pass through one check point. That check point should inspect the exact SQL command before the database runs it. If a statement tries to drop a table or edit rows, it should pause. A human reviewer can then approve or reject that exact query from chat.

You also want data masking that runs on query results in real time. When a workflow reads customer rows, masking rewrites card numbers and national IDs. This stops raw emails and phone numbers from entering chat models or workflow logs. Finally, every query and approval decision should go into one central audit log.

Check every Postgres query on the wire

Database roles and network rules decide who can open a session. They do not read what each SQL statement does after login. With hoop.dev, you run an open-source proxy, a sidecar for your Postgres. It runs beside your database and reads the Postgres wire protocol. Your apps and agents keep their login details and change only the host and port in the connection string their tool uses.

Before a query runs, hoop.dev guardrails check the statement by its effect, table, or pattern. They refuse bad deletes or drops with a real Postgres error. On the way back, hoop.dev data masking rewrites sensitive values in returned rows based on your rules. Every query and verdict goes to the audit trail. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and generates evidence for HIPAA, GDPR, and PCI DSS. See the code on GitHub.

Frequently asked questions

Can an n8n AI Agent run SQL queries without a pause node?

Yes, when you attach a Postgres tool sub-node directly to an AI Agent node. The agent decides when to call the tool and runs queries on its own.

Does a Postgres read-only role block all bulk data exports?

No, granting the SELECT privilege on a table also allows COPY TO commands. A role with SELECT rights can still pull every row she has access to by default.

How do I stop workflows from creating tables in the public schema?

PostgreSQL 15 and later block this by default for regular roles. On older databases, you must revoke CREATE on the public schema from the PUBLIC role.

The query runs. The emails stay hidden.