How to Configure Dify Postgres Approval
You set up dify postgres approval by pairing restricted database roles with human review steps. First, you lock down database rights, then you pause agent runs for human sign-off. Teams often let AI apps log in with broad database roles to move fast. When an agent uses an admin role, one bad prompt can wipe out live tables. It can also pull sensitive customer rows into chat logs without anyone noticing. Putting a strict review step in front of database runs stops these mistakes cold. How a di
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 dify postgres approval by pairing restricted database roles with human review steps. First, you lock down database rights, then you pause agent runs for human sign-off.
Teams often let AI apps log in with broad database roles to move fast. When an agent uses an admin role, one bad prompt can wipe out live tables. It can also pull sensitive customer rows into chat logs without anyone noticing. Putting a strict review step in front of database runs stops these mistakes cold.
How a dify postgres approval setup works
A safe setup starts inside the database before you touch the AI workflow builder. You create a dedicated database role just for the AI agent to use. When you manage many users or apps, you can set up group roles first. Then you grant the agent role membership in the right group role.
Next, you strip away default access that every new role gets out of the box. In PostgreSQL, the special PUBLIC role gives privileges to every role on the system. By default, PUBLIC gets the right to connect and make temporary tables on databases. It also gets the right to run functions and procedures. The database owner should revoke these defaults so the agent starts with zero extra power.
If your database was upgraded from a version before PostgreSQL 15, check the public schema. Older versions let every user create new objects in the public schema by default. An admin removes this risk by revoking create rights on the public schema from PUBLIC. In PostgreSQL 15 and later, the default setup blocks this out of the box.
Once the role is clean, you grant read rights only where the agent needs them. The SELECT privilege allows reading from a full table, view, or materialized view. You can also grant SELECT on specific columns of a table to limit exposure. Note that SELECT also allows copying data out, so choose columns with care.
On the app side, you split the AI workflow into two clear phases. The first node lets the model draft a query or propose an action. Instead of running that query right away, the workflow pauses and alerts a reviewer. A human checks the proposed query and approves or rejects the run.
Approval Chains & Escalation + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they miss
Native database roles and app workflow pauses solve the first layer of risk. Database privileges decide which tables and columns the agent role can read or change. Workflow pauses give a teammate a chance to spot bad logic before it runs.
Still, static roles only decide who can log in and which objects they touch. They cannot check what an individual statement does while it travels to the database. If an agent needs write rights for one task, the role stays wide open. A reviewer might approve a safe-looking step in the app, while the raw query changes.
Column grants also struggle when a table holds mixed data across many rows. Granting read access to a column returns every row, including emails, phone numbers, and card numbers. Native roles do not rewrite matched fields in results before the model reads them. They also do not tie an approved app click to the exact database statement 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 PostgreSQL pause a single query for human approval on its own?
No, PostgreSQL checks role permissions right away and either runs or rejects the statement. You need an external workflow step to hold a query and wait for human sign-off.
How do I limit which columns an AI agent can read in PostgreSQL?
You can grant the SELECT privilege on specific columns of a table or view. This stops the agent role from reading other columns in that same table.
Why should I check the PUBLIC role when setting up an AI database user?
The PUBLIC role grants default rights to every role on the database system. Revoking its default connect and function rights keeps your AI role from inheriting unwanted access.
Related guides
In this post