How to Configure Goose MySQL Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up goose mysql permissions, create a dedicated database user for your migration tool. Grant that user rights to change schemas and write to the version tracking table. Many engineering teams take a shortcut and use a full root account instead. Broad permissions let a bad script drop tables or delete live customer rows. How native goose mysql permissions work Goose reads migration files from your source repo and runs them in order. It tracks applied changes inside a dedicated state tabl

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 mysql permissions, create a dedicated database user for your migration tool. Grant that user rights to change schemas and write to the version tracking table. Many engineering teams take a shortcut and use a full root account instead. Broad permissions let a bad script drop tables or delete live customer rows.

How native goose mysql permissions work

Goose reads migration files from your source repo and runs them in order. It tracks applied changes inside a dedicated state table in your target database. When the tool starts, it logs in using a standard database user account. MySQL checks the host and user account against its internal grant tables.

To do its job, the tool needs two specific sets of database rights. First, it needs data rights on the state table to read and write versions. Second, it needs schema rights on your app tables to build or alter structures. You can group these rights into a named MySQL role for easier management. Then you assign that role to the specific migration user account.

You should also limit the host address where the migration user can log in. This stops developers from using the migration login from their home networks. Teams often pair this setup with a dedicated database schema just for migrations. That way, the tool only touches the target schema and nothing else.

What native database roles solve and what they miss

Native MySQL roles do a great job of blocking unauthorized network logins. They also stop a migration user from reading system tables in other schemas. If your app has ten databases, grants can lock the tool to one. This setup meets basic security rules for separating app users from admin tools.

Still, static grants only check who logs in and which schema they touch. They cannot check what an individual SQL statement actually does in flight. A migration user must have the right to alter and drop tables. With those rights, a broken script can wipe out an entire production table. MySQL sees a valid grant and runs the destructive command right away.

MySQL Access Governance + AI Agent Permissions: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static roles also leave gaps when developers need to debug a failed migration. Someone has to share the migration password or grant broad personal rights. Shared keys sit on laptops and inside build servers for months at a time. When a query fails or leaks data, native logs show the shared role name. You cannot tell which team member ran the command or see masked fields like emails.

Best practices for safer database migrations

Start by splitting your database users into two clear roles for daily work. Give your running app a role that only reads and writes table rows. Give your migration runner a separate role that can alter schemas and tables. Never let the running app use the migration role during normal traffic.

Next, keep your migration user restricted to read and write only necessary tables. If a migration must read sensitive rows, watch out for exposed data in logs. Masking tools can rewrite matched fields like phone numbers or card numbers in results. Finally, review every migration file before you let the tool run it in production. Human checks catch destructive drop statements that native database grants will always allow.

Inspect every MySQL query and response in flight

Static grants and network rules block bad logins. They cannot pause a risky query for review or mask emails in a result set. By design, hoop.dev is an open-source proxy that runs as a sidecar for your MySQL. It reads plain queries, prepared statements, and cursors on the wire.

First, hoop.dev guardrails block banned actions before MySQL runs them. An AI Analyzer you configure can score intent, and hoop.dev data masking rewrites sensitive string columns in results. Every query and verdict goes to the resource audit trail. Find the open-source binary on GitHub.

Frequently asked questions

What minimum grants does Goose need in MySQL?

The user needs select, insert, and delete rights on the version tracking table. It also needs create, alter, index, and drop rights on the target app schema.

Should my app use the same MySQL user as Goose?

No, you should always separate the app user from the migration runner user. The app only needs row rights, while the migration tool needs schema change rights.

How do I handle failed migrations in production?

Check the version table to see which migration step failed during the run. Fix the broken SQL state by hand, then update the version row to match.

The query runs. The emails stay hidden.