How to Configure Goose Postgres Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up goose postgres read only access, create a dedicated database role with select rights. Then, configure the Goose agent to log in using only that restricted role. Many teams start by giving their AI agent an existing developer account. That shortcut lets the agent alter schemas, delete rows, or read secret tables by mistake. A strict read role stops the agent from changing data while it inspects your tables. How goose postgres read only access works in PostgreSQL PostgreSQL manages ta

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 goose postgres read only access, create a dedicated database role with select rights. Then, configure the Goose agent to log in using only that restricted role. Many teams start by giving their AI agent an existing developer account. That shortcut lets the agent alter schemas, delete rows, or read secret tables by mistake. A strict read role stops the agent from changing data while it inspects your tables.

How goose postgres read only access works in PostgreSQL

PostgreSQL manages table permissions through roles and privileges. Instead of granting rights to each user, admins can set up group roles. Individual agent accounts then join that group role to get the right permissions. For a read-only setup, the admin grants the SELECT privilege to the role. In PostgreSQL, the SELECT privilege lets a role read from tables, views, and materialized views. You can grant SELECT on an entire table or limit it to specific columns. Keep in mind that the SELECT privilege also permits the COPY TO operation.

Still, simply creating a new role is not enough to lock down a database. Every database role by default gets privileges assigned to the special PUBLIC role. By default, PUBLIC allows database connections, temporary table creation, and function execution. If a function modifies data, an agent with execution rights can still trigger writes. The object owner must revoke those default PUBLIC rights to keep the role strict. Also, databases created before PostgreSQL 15 let PUBLIC create new objects in the public schema. Upgraded databases keep this old setting unless an admin revokes the create right from PUBLIC. PostgreSQL 15 and later versions remove that default public schema creation right out of the box.

What native roles solve and what they leave open

Native database roles solve the problem of accidental writes and schema changes. When Goose connects with a clean read role, the database blocks delete and update queries. The agent can explore schema designs and run test queries without breaking production tables. Column-level SELECT grants also let admins keep certain sensitive columns off limits.

Still, native database permissions only check who logs in at the start of a session. They do not inspect what individual queries do in flight during that session. A read-only agent with table access can still pull millions of customer records at once. If a table holds emails, phone numbers, card numbers, or national IDs, SELECT reads them all. Managing column grants across hundreds of tables takes lots of manual work as schemas change. Also, storing static database credentials on developer machines creates a key management risk. Teams need a way to inspect queries and rewrite matched fields in results dynamically.

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.

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 a PostgreSQL read-only role still change data through functions?

Yes, because the special PUBLIC role grants execution rights on functions and procedures by default. If a function writes data, a read role can run it unless you revoke execution rights.

Does PostgreSQL allow read-only grants on specific columns?

Yes, PostgreSQL lets admins grant the SELECT privilege on specific columns of a table or view. This limits which fields the Goose agent can read when it queries that table.

Why can my read-only agent create tables in the public schema?

Databases older than PostgreSQL 15 let the PUBLIC role create objects in the public schema. On older or upgraded databases, an admin must revoke that create privilege from PUBLIC.

The query runs. The emails stay hidden.