How to Configure Copilot Studio Postgres Approval

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up copilot studio postgres approval, combine workflow review steps with limited database roles. You ask a human to review each bot action before the database runs the query. Teams often link their AI bots to data stores with one shared login key. When that role has broad permissions, a bad prompt can alter or delete production rows. How native copilot studio postgres approval works In a standard setup, the bot builder adds a workflow step before the database action. When a user asks th

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.

To set up copilot studio postgres approval, combine workflow review steps with limited database roles. You ask a human to review each bot action before the database runs the query. Teams often link their AI bots to data stores with one shared login key. When that role has broad permissions, a bad prompt can alter or delete production rows.

How native copilot studio postgres approval works

In a standard setup, the bot builder adds a workflow step before the database action. When a user asks the bot to change data, the workflow pauses its run. It sends a message to a manager in chat or email for a manual check. Once the manager clicks approve, the workflow uses its stored login key to reach PostgreSQL.

On the database side, admins limit what that stored login key can do. Instead of giving the bot full table rights, admins create a group role for bots. Users or bot accounts get membership in that group role to keep rule updates simple. Admins can grant read rights on specific columns of a table or view. Note that read rights in PostgreSQL also allow the role to export table data.

Admins also check default rules on the database. In PostgreSQL, the special PUBLIC role gives rights to every role on the system. By default, PUBLIC gets rights to log in, make temp tables, and run functions. Object owners can revoke those defaults so the bot role cannot run extra procedures. In PostgreSQL 15 and later, new databases stop all users from making objects in public. On older databases, admins must revoke that create right from PUBLIC by hand.

What native setup solves and what it misses

This native setup solves two basic problems for teams building AI workflows. First, workflow pauses stop the bot from running write actions without a human check. Second, column-level read roles stop the bot from reading every column in a table.

Still, a large gap keeps the session active during live operations. Workflow approvals happen inside the app builder, not at the database wire level. If a builder skips the review step in one flow, the database still runs the query. The database only checks who logs in and which tables the role can touch. It does not check what a specific statement does in flight before running it.

Approval Chains & Escalation + Copilot Security Implications: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static roles also struggle with sensitive text inside allowed columns. A bot might need read access to a customer table to check order dates. If that query also returns emails, phone numbers, card numbers, or national IDs, raw data leaks. Column grants are all or nothing for each role once the login succeeds. Also, sharing one static login across many bot flows hides which user triggered a query.

Why runtime query checks matter for AI bots

AI bots generate queries and API calls based on messy user prompts. A human reviewer in a chat app often sees only a summary of the action. They do not see the exact SQL statement that will hit the database server. If the bot runs a broad update or reads ten thousand rows, the workflow allows it.

Putting approval and masking rules right at the database session level fixes this gap. Every query gets inspected in flight, no matter how the bot flow was built. Risky commands wait for approval, while data masking rewrites matched fields in results.

Run guardrails and masking on the Postgres connection

Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev is an open-source proxy that sits beside Postgres, RDS, or Aurora as a sidecar for your Postgres. Clients and agent tools keep their credentials and point their connection string at the listener.

On each query, hoop.dev guardrails refuse disallowed drops, deletes, or table reads before they run. Next, hoop.dev data masking rewrites sensitive fields in returned rows that match your rules. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.

Frequently asked questions

Can a read-only PostgreSQL role still copy data out of a table?

Yes. In PostgreSQL, the read privilege on a table also lets that role export data. Admins must watch query volume even when a bot uses a read-only database role.

Why should we revoke default PUBLIC rights for bot accounts?

The special PUBLIC role gives every user rights to run functions and create temp tables. Revoking these defaults stops an AI bot from running database functions it does not need.

Do workflow approval steps block bad SQL queries on their own?

No. A workflow step only pauses the bot inside the app platform. Once approved, the database runs whatever statement the bot sends if the role allows it.

The query runs. The emails stay hidden.