How to Configure Mastra Postgres Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a mastra postgres audit log, give your AI agent a dedicated database role and enable query logging in Postgres. This lets your team see every query the agent runs against your data. Many teams start by giving Mastra agents a shared admin login to move fast. Broad roles let an agent read or change any table without clear tracking. If an agent makes a bad write, you cannot tell which workflow ran it. How to set up a mastra postgres audit log A clean setup starts with a dedicated datab

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 a mastra postgres audit log, give your AI agent a dedicated database role and enable query logging in Postgres. This lets your team see every query the agent runs against your data. Many teams start by giving Mastra agents a shared admin login to move fast. Broad roles let an agent read or change any table without clear tracking. If an agent makes a bad write, you cannot tell which workflow ran it.

How to set up a mastra postgres audit log

A clean setup starts with a dedicated database role for your Mastra app. When you use a group role, you can manage privileges for many users at once. You then grant individual agent roles membership in that group role. This makes it easy to track which agent logged in and ran a query.

Next, you must lock down what that role can do by default. In Postgres, the special PUBLIC role grants rights to every role on the system. By default, PUBLIC gets the right to connect to databases and create temporary tables. It also gets the right to run functions and procedures. The database owner can revoke these defaults so agents only keep what they need.

You also need to check schema rights. In Postgres 15 and newer, users cannot create objects in the public schema by default. On older databases, an admin must revoke that create right from PUBLIC by hand. For read tasks, you can grant the select right on specific columns of a table. This select right also lets the role copy data out of those columns. Once roles are tight, your database audit log records the exact role name for every query.

What native roles solve and what they miss

Native database roles and audit logs solve the first half of the access problem. They decide who can log in and which tables they can touch. They also write down a history of the queries that ran on the server. This helps your team review past actions after an incident happens.

Still, native setup rules leave a big gap during live agent runs. Database privileges check who logs in, not what each query does in flight. If an agent has select rights on a customer table, it can pull every row. Native column grants are static and hard to update for fast-changing AI workflows. Also, a standard log records raw queries after they finish, rather than stopping bad ones early. It does not rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs in query results.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

How to tighten database privileges for AI agents

To keep your database safe, combine strict role design with clear log review habits. Start by splitting read roles from write roles for every Mastra workflow. Give a read-only role to agents that only answer user questions from data. Give a separate write role only to workflows that must update records.

Review the PUBLIC role on every database before you connect an agent. Remove default execute rights on functions that agents do not need to run. Check column grants on tables that hold user records like emails or card numbers. Only grant select on the exact columns required for the agent task. Finally, send your database logs to a central place where your team can check them daily.

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

Why should Mastra agents use their own Postgres roles?

Separate roles make your audit log useful. When each agent has its own role, the log shows which workflow ran each query.

Does revoking rights from PUBLIC break existing database roles?

It removes default database connect, temporary table, and function run rights for everyone. You should grant needed rights directly to your specific group roles first.

Can Postgres restrict an agent to specific columns in a table?

Yes, Postgres lets you grant the select privilege on specific columns of a table or view. The role can then read or copy only those chosen columns.

The query runs. The emails stay hidden.