How to Configure Goose Postgres Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a goose postgres audit log by enabling database query logging and assigning a dedicated role to your AI agent. This setup records every SQL statement the agent runs against your data. Many teams start by giving their agent a shared developer account. That broad role lets the agent read sensitive tables or drop production data by mistake. How a goose postgres audit log works with database roles A clean audit trail starts with a separate database role for the agent. When Goose logs i

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 a goose postgres audit log by enabling database query logging and assigning a dedicated role to your AI agent. This setup records every SQL statement the agent runs against your data. Many teams start by giving their agent a shared developer account. That broad role lets the agent read sensitive tables or drop production data by mistake.

How a goose postgres audit log works with database roles

A clean audit trail starts with a separate database role for the agent. When Goose logs in with its own role, Postgres tags every query with that role name. Teams often create group roles to manage rules for many users at once. You can put the agent role inside a read-only group role to keep rules simple.

Postgres gives the special PUBLIC role default rights on every database. By default, PUBLIC gets the right to log in and make temporary tables. It also gets the right to run functions and procedures. Object owners can remove these default rights to lock down the database. In version 15 and later, Postgres blocks PUBLIC from making new objects in the public schema. On older databases, an admin must remove that create right from PUBLIC by hand.

After you lock down defaults, you grant read rights to the agent role. The SELECT right lets the role read from a whole table, a view, or specific columns. Note that the SELECT right also lets the role export data with copy commands. As the agent works, the database writes each query and role name to its log file.

What native database roles and logs miss

Native roles and logs solve the first part of database safety. They control who can log in and which tables they can read. The database log also gives you a record of past queries for review. This works well when you only need to check history after an incident.

Still, native setups leave a big gap during live agent runs. Database roles check permissions at login, not what individual statements do in flight. If a role has read access to a table, it can pull every row at once by default. Column grants can hide whole columns, but they take a lot of work to maintain.

Audit Log Integrity + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native logs also cannot stop a bad query before it runs. They just write down the damage after it happens. Even worse, query results often hold raw emails, phone numbers, card numbers, or national IDs. When the agent reads a table, those sensitive fields go straight into the model prompt. You cannot pause a risky command for human approval with native roles alone. You also cannot rewrite sensitive fields in results on the fly without changing every table.

How to keep agent database sessions safe

To keep production data safe, teams need controls that act on each query in flight. First, always give your agent a least-privilege role with read rights only on needed tables. Remove default execute and create rights from the PUBLIC role so the agent stays restricted.

Next, ship your database logs to a central store where the agent cannot change them. Match each database query in the log with the task the user asked the agent to do. Finally, add a way to inspect queries and mask sensitive results before they reach the model. Dynamic data masking rewrites matched fields like emails and card numbers in query results. This keeps raw customer details out of model context while the agent still gets useful answers.

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

Why should Goose use its own Postgres role?

A separate role lets you see which queries came from the AI agent in your logs. It also lets you limit the agent to read-only access without blocking down human engineers.

Can Postgres column permissions replace data masking for AI agents?

Postgres lets you grant SELECT on specific columns, which blocks access to other columns completely. Data masking is different because it rewrites sensitive fields like phone numbers in results while keeping the query working.

Does the default PUBLIC role give Goose too much access?

Yes, the PUBLIC role lets any user create temporary tables and run functions by default. You should revoke unneeded PUBLIC rights so the agent only runs approved read queries.

The query runs. The emails stay hidden.