How to Configure Goose Postgres Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up goose postgres data masking by creating restricted database roles and views. You then point the AI agent to those limited database objects. Teams often connect local coding agents directly to development or staging databases. They use a standard developer role so the agent can inspect tables and debug issues. When that role has broad read access, the agent pulls raw rows into its context. This exposes sensitive fields like emails, phone numbers, card numbers, and national IDs during

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 postgres data masking by creating restricted database roles and views. You then point the AI agent to those limited database objects.

Teams often connect local coding agents directly to development or staging databases. They use a standard developer role so the agent can inspect tables and debug issues. When that role has broad read access, the agent pulls raw rows into its context. This exposes sensitive fields like emails, phone numbers, card numbers, and national IDs during routine tasks.

How native goose postgres data masking works

PostgreSQL gives database admins native tools to limit what a role can read. The SELECT privilege allows reading from specific columns of a table or view. Admins can create a dedicated group role for AI agents and grant membership to users. Instead of granting access to a whole table, the team grants SELECT only on safe columns.

Another native approach uses database views or materialized views to transform sensitive columns. A view can return static text or partial strings in place of card numbers or emails. The admin revokes table access from the agent role and grants SELECT on the view. When Goose runs a query, Postgres checks the role permissions before returning any rows.

Admins also need to check default permissions on the database. The special PUBLIC role grants rights to every role on the system. By default, PUBLIC allows database connections, temporary table creation, and function execution. Object owners must revoke these defaults if they want strict control over what Goose can run.

In PostgreSQL 15 and later, regular users cannot create objects in the public schema by default. On older databases or upgraded systems, admins must revoke CREATE on the public schema from PUBLIC. This stops an agent from writing new tables or altering views to bypass column limits.

What native roles solve and what they leave open

Native roles and column grants work well for fixed schemas and predictable queries. They ensure that a specific database user only reads approved columns or views. They also block the agent from dropping tables or modifying production records.

Data Masking (Static) + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, static views and column grants require heavy upkeep across fast-moving databases. Every time an engineer adds a table or column, someone must update the views and grants. If an engineer forgets to update a grant, the agent query fails or reads raw data. Also, blocking an entire column breaks queries when the agent only needs to count rows or join tables.

Native roles also decide who logs in, not what individual statements do in flight. If a developer runs Goose with their own database login, the agent inherits their full permissions. Postgres cannot tell if a query came from a human or an automated agent loop. It cannot rewrite matched fields like phone numbers or national IDs on the fly based on context.

Why query-level controls matter for AI agents

AI agents explore schemas, sample rows, and run multiple queries in seconds to solve a task. A single broad query can pull thousands of customer emails and card numbers into the prompt history. Once that data enters the agent context, it often goes to external model providers.

To keep development fast, teams need controls that inspect results in flight. Instead of breaking queries with missing column grants, runtime masking rewrites matched fields in results. The agent still sees valid data types and row counts, so its logic keeps working. Engineers do not have to maintain hundreds of duplicate views across every database.

Check every Postgres query on the wire

Database roles and network rules decide who can open a session. They do not read what each SQL statement does after login. With hoop.dev, you run an open-source proxy, a sidecar for your Postgres. It runs beside your database and reads the Postgres wire protocol. Your apps and agents keep their login details and change only the host and port in the connection string their tool uses.

Before a query runs, hoop.dev guardrails check the statement by its effect, table, or pattern. They refuse bad deletes or drops with a real Postgres error. On the way back, hoop.dev data masking rewrites sensitive values in returned rows based on your rules. Every query and verdict goes to the audit trail. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and generates evidence for HIPAA, GDPR, and PCI DSS. See the code on GitHub.

Frequently asked questions

Can PostgreSQL mask columns without creating separate views?

Standard PostgreSQL uses column-level SELECT grants to block access to specific columns completely. To return altered values for emails or card numbers natively, you must query through a view or function.

Why does Goose still read sensitive tables when using a restricted role?

The database may still grant read rights through the special PUBLIC role or inherited group roles. Check the table privileges and revoke unwanted default grants from PUBLIC.

What happens when Goose queries a column it does not have permission to read?

PostgreSQL rejects the entire SELECT statement and returns a permission error. The agent must then rewrite its query to exclude the restricted column before it can read any rows.

The query runs. The emails stay hidden.