How to Configure Goose Postgres Least Privilege
You set up goose postgres least privilege by creating a dedicated database role with scoped read rules. You then give that limited role to the AI agent instead of a shared admin account. Many engineering teams start by handing an agent a personal developer login or a broad service role. When an AI tool runs with wide database permissions, a single bad prompt can drop tables. A wide role also lets the agent pull sensitive customer rows into its context window. How goose postgres least privilege
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 least privilege by creating a dedicated database role with scoped read rules. You then give that limited role to the AI agent instead of a shared admin account. Many engineering teams start by handing an agent a personal developer login or a broad service role. When an AI tool runs with wide database permissions, a single bad prompt can drop tables. A wide role also lets the agent pull sensitive customer rows into its context window.
How goose postgres least privilege works in the database
A safe setup starts inside the database engine before the agent ever logs in. Admins first create a dedicated group role to hold the exact permissions the agent needs. Individual user accounts or agent logins then join this group role to inherit those rules. This approach keeps permission changes tidy when several developers run local agent sessions.
Before you add read rights, you must lock down the default database rules. In Postgres, a special role called PUBLIC gives privileges to every user on the system. By default, PUBLIC allows every role to connect to databases and create temporary tables. It also lets any role run functions and procedures unless the owner revokes those defaults. In Postgres 15 and newer, users cannot create new objects in the public schema by default. On older databases or upgraded systems, admins must manually revoke that public creation right.
Once the defaults are tight, you grant the agent role read rights on specific tables. Postgres lets you grant the SELECT privilege on whole tables, views, or materialized views. You can also grant SELECT on specific columns of a table instead of the entire row. Column grants help when a table holds both safe app stats and private emails or phone numbers. Still, you should remember that the SELECT privilege also permits the COPY TO command.
Why native roles are only the first step
Native database roles do a great job of controlling which account can log in. They also define the outer boundary of which tables and columns that account can read. For static apps with fixed queries, role-based rules cover most basic security needs. The database checks the role at login and checks object rights before running a statement.
Least Privilege Principle + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
AI agents like Goose change how queries hit the database during a live work session. An agent builds SQL on the fly based on user prompts and previous query results. Native roles cannot tell the difference between a safe summary query and a bulk data dump. If a column holds mixed text, a basic column grant cannot rewrite card numbers inside the results. Managing column grants across hundreds of changing tables also creates heavy upkeep for database admins. Teams end up needing a way to inspect individual queries and rewrite sensitive fields in flight.
Managing agent sessions across a growing team
Sharing one static database password across every developer laptop makes audit logs hard to read. When the agent runs a slow or risky query, the database log only shows the shared role. Giving each developer a unique database login helps, but it multiplies the credentials you must rotate. You also have to make sure every local agent setup stores those passwords safely on disk. A strong setup combines narrow database roles with clear visibility into every statement the agent sends.
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 I limit an AI agent to specific columns in a Postgres table?
Yes, Postgres lets you grant the SELECT privilege on specific columns of a table or view. If the agent queries a restricted column on that table, the database rejects the statement.
Why can a new read-only role still run functions and create temp tables?
Postgres grants default privileges to a special PUBLIC role that applies to every user on the system. Admins must revoke execute and temporary table rights from PUBLIC to block those actions.
Does the Postgres SELECT privilege allow data exports?
Yes, granting SELECT on a table or column also allows the role to run COPY TO. A role with read access can export any rows and columns it has permission to view.
Related guides
In this post