How to Configure Goose Postgres Approval

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up goose postgres approval by pairing scoped database roles with human query checks. This stops an AI agent from running bad changes on live tables without a review. Most teams start by giving the local agent a standard developer database key. That quick setup lets the tool read and change any table right away. Broad roles create big risks when an automated tool writes and runs its own queries. One bad prompt can change rows or drop data before anyone spots the mistake. How native goos

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 goose postgres approval by pairing scoped database roles with human query checks. This stops an AI agent from running bad changes on live tables without a review. Most teams start by giving the local agent a standard developer database key. That quick setup lets the tool read and change any table right away. Broad roles create big risks when an automated tool writes and runs its own queries. One bad prompt can change rows or drop data before anyone spots the mistake.

How native goose postgres approval works with roles

A safe setup starts inside the database with tight role rules. Admins create a group role to manage access for many users at once. You then put the agent user into that specific group role. For read tasks, you grant the select privilege on specific tables or views. You can also grant select on just a few safe columns of a table. Notice that the select privilege also allows the copy to action on those tables.

You must also check the default access rules on your database. The special role name public gives rights to every role on the system. By default, public gets connect and temporary rights on databases. It also gets execute rights on functions and procedures out of the box. The object owner can take away these default rights to lock things down. In version 15 and later, regular users cannot create objects in the public schema. Older databases still need an admin to take away that create right from public. On the client side, the agent can ask the user before running a tool. The developer reads the screen and clicks yes or no on the local machine.

Why local prompts and static roles leave gaps

Database roles and local agent prompts solve only part of the safety problem. Database roles decide who can log in and which objects they can touch. They do not pause a single risky update statement to ask a team lead. Once a role has write rights on a table, every update runs right away. A role cannot tell the difference between fixing one row and wiping ten thousand.

Local prompts on the user laptop also have clear limits for teams. The person running the agent is the same person clicking approve on the screen. Prompt fatigue sets in fast when an agent runs dozens of steps in a row. Users start pressing yes out of habit without reading the full SQL query. Also, local approvals leave no shared log for security teams to check later. Even read queries can pull raw emails, phone numbers, or card numbers into prompts. Static column grants are hard to keep up as schemas change every week.

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 a team approval flow

Teams running AI agents against live data need checks that happen in flight. A peer should review write queries in chat before the database runs them. Read queries should have masking that rewrites emails and national IDs in results. Every query and approval click should go into a central log for audits. This approach lets the agent work fast while keeping sensitive rows safe.

Add query and result controls beside Postgres

Grants and pools govern who logs into PostgreSQL. Yet every query in an open session still runs with that role's full scope. In practice, hoop.dev runs beside your database as a sidecar for your Postgres. It reads plain and prepared queries on the wire with no change to your users or drivers.

When a tool or agent sends SQL, hoop.dev Postgres controls check each statement against your deny rules. An AI Analyzer you configure can also score intent or hold a risky query for human review. Matched sensitive columns in result rows are masked in memory before they reach the caller. Each query and decision is logged per listener, which generates evidence for SOC 2, HIPAA, and GDPR audits. View the binary on GitHub.

Frequently asked questions

Can PostgreSQL pause a single query for human review?

No, native database roles check permissions when a statement starts and run it right away. You need an external review flow to hold a query for a peer check.

Why is the public role risky for AI database agents?

The public role gives every database user connect, temporary, and function execute rights by default. An agent can use those default functions unless the owner takes away those rights.

Does column level access stop sensitive data leaks to models?

Granting select on specific columns helps, but it breaks queries that expect full table schemas. Dynamic masking is easier because it rewrites phone numbers and card numbers in query results.

The query runs. The emails stay hidden.