How to Configure Mastra Postgres Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up mastra postgres read only access by creating a restricted database role. You then give that role read permissions on specific tables and columns. Many teams connect their AI agents to databases using standard app keys. Those keys often hold full write and delete rights. When an AI agent uses a broad role, one bad query can change data. Giving the agent a read-only role stops unwanted writes at the database level. How mastra postgres read only access works in PostgreSQL PostgreSQL m

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 mastra postgres read only access by creating a restricted database role. You then give that role read permissions on specific tables and columns. Many teams connect their AI agents to databases using standard app keys. Those keys often hold full write and delete rights. When an AI agent uses a broad role, one bad query can change data. Giving the agent a read-only role stops unwanted writes at the database level.

How mastra postgres read only access works in PostgreSQL

PostgreSQL manages permissions through roles. You can set up group roles to manage permissions for many users at once. You then add individual user accounts to that group role. For an AI workflow, you create a dedicated role just for the agent.

Next, you grant the SELECT privilege to that role. In PostgreSQL, the SELECT privilege lets a role read from a table or view. It also works on materialized views and other table-like objects. You can grant SELECT on an entire table or on specific columns. Note that the SELECT privilege also lets the user run COPY TO exports.

You must also check the special PUBLIC role. The PUBLIC role grants permissions to every role on the database system. By default, PUBLIC gets CONNECT and TEMPORARY privileges on databases. It also gets EXECUTE privileges on functions and procedures. The object owner can revoke these default grants at any time.

In PostgreSQL 15 and newer, users cannot create objects in the public schema by default. Older databases, or upgraded ones, may still allow this behavior. An admin can fix this by revoking CREATE on the public schema from PUBLIC.

What native database roles solve and what they miss

Native database roles do a great job of blocking direct data changes. If a Mastra agent tries to drop a table, PostgreSQL rejects the command. Group roles and column grants help you limit which tables the agent queries. This setup handles basic login and table rules well.

Still, database roles only check who logs in and which objects they touch. They do not inspect what individual queries do in flight. A read-only role with table access can still pull every row at once by default. If a table holds emails, phone numbers, or card numbers, the query returns them. Granting column privileges takes manual work across every single table.

Auditor Read-Only Access + Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Also, the PUBLIC role gets EXECUTE rights on functions by default. If a function writes data, a read-only user might still run it. You also have to store a database password in the Mastra host environment. Rotating that key means updating both the database and the app settings.

Managing schema drift and sensitive columns

Database schemas change often as teams ship new features. When developers add new tables, they must update column grants for the agent role. If someone forgets a column rule, the agent might read new national IDs. Teams often grant whole-table read rights just to avoid breaking the app.

Static grants also treat every query from the Mastra app the same way. You cannot see which prompt or user triggered a specific database read. To control data flow and rewrite matched fields in results, you need query-level checks.

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 PostgreSQL read-only role still export table data?

Yes. The PostgreSQL SELECT privilege allows a role to read rows and run COPY TO. This means the role can export data from any table it can read.

How do I stop a read-only role from creating tables in the public schema?

PostgreSQL 15 and later block this by default. On older or upgraded databases, an admin must revoke CREATE on the public schema from PUBLIC.

Can I restrict a Mastra agent to specific columns in a table?

Yes. PostgreSQL lets you grant the SELECT privilege on specific columns of a table or view. You must list the safe columns when you set up the grant.

The query runs. The emails stay hidden.