How to Configure Goose Redshift Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up goose redshift read only access by creating a limited database user. You grant that user select rights on chosen schemas and pass those keys to Goose. Many teams rush this step and share an admin user with the agent. That choice lets a confused model drop tables or change live business data. A strict read role stops write commands at the database engine level. How to set up goose redshift read only access First, an admin logs into the data warehouse to create a dedicated role. This

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 redshift read only access by creating a limited database user. You grant that user select rights on chosen schemas and pass those keys to Goose. Many teams rush this step and share an admin user with the agent. That choice lets a confused model drop tables or change live business data. A strict read role stops write commands at the database engine level.

How to set up goose redshift read only access

First, an admin logs into the data warehouse to create a dedicated role. This role exists only for the AI agent and human data readers. You grant usage permissions on the target schema to this new role. Next, you grant select rights on the tables inside that schema. You must also alter default privileges for future tables in that schema. Otherwise, the agent will fail to read new tables created next week.

After the role is ready, you create a specific database user for Goose. You assign the read role to this user and set a strong password. Some teams use cloud identity roles instead of static passwords for this step. Both methods rely on the warehouse to check permissions after the user logs in.

Finally, you configure the local Goose extension or tool settings on the developer machine. You enter the cluster endpoint, database name, port, and user keys. When you ask the agent a question, it opens a session to the cluster. It reads the schema metadata and runs select queries to find your answer.

What native database roles solve and what they miss

Native warehouse roles do one job very well. They block the agent from running insert, update, delete, or drop commands. If the model writes a bad command that alters rows, the warehouse rejects it. This protects your tables from accidental edits or deletions during a task.

Still, a read role only decides who logs in and which tables open. It does not inspect what individual select statements do in flight. Once the session starts, the agent can read every column in an allowed table. If a customer table holds emails, phone numbers, or card numbers, the agent pulls them. Those raw values go straight into the model prompt window and local chat logs.

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.

Managing sensitive data and heavy queries in agent workflows

Data warehouses hold millions of rows across wide analytical tables. An AI agent does not always know the size of a table before querying. It might run a full table scan without a row limit or date filter. Even with a read role, one bad query can slow down the whole cluster. Other teams will see their dashboards freeze while the agent pulls massive result sets.

Static database keys on developer laptops also create a tracking problem. When five engineers share one read user for their local agents, audit logs blur. The warehouse log shows the shared role ran a query, not which person asked. Also, revoking access means rotating keys on every single laptop by hand. Teams need a way to tie each query to a real user identity. They also need a way to rewrite sensitive fields in results before models read them.

Add runtime guardrails and masking beside your databases and APIs

Login rules, role grants, and CI checks govern who connects to your stack. Yet any allowed script, engineer, or AI agent still runs commands with the full scope of its role. For workloads on Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes, SSH, or internal HTTP services, hoop.dev sits beside the service as an open-source proxy, a sidecar for your database or API.

Before a query runs, hoop.dev guardrails check the statement against your rules. An AI Analyzer you configure can score intent or hold risky calls for human review. Next, hoop.dev data masking rewrites sensitive fields in responses, and each verdict is logged per listener. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Can Goose accidentally delete tables with a read role?

No, a proper read role blocks all write and drop commands at the database level. The warehouse engine will return a permission error if the agent tries to alter data.

Does a read role hide sensitive columns like emails or card numbers?

No, standard table select grants let the user read every column in that table by default. To hide emails or national IDs, you need column grants or dynamic data masking.

Why do shared read keys make warehouse audits difficult?

Shared keys log every query under the same generic database username. You cannot tell which engineer or local agent session actually ran a specific query.

The query runs. The emails stay hidden.