How to Configure Goose Postgres Credentials

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up goose postgres credentials, create a dedicated database role with narrow table rights. Then pass those login details to your migration tool through local secret variables. Teams often use a shared admin login to run schema updates fast. That broad role lets a single bad script drop tables or read private rows. How to set up goose postgres credentials with database roles A safe setup starts inside the database before you run a single migration file. First, you create a group role tha

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 credentials, create a dedicated database role with narrow table rights. Then pass those login details to your migration tool through local secret variables. Teams often use a shared admin login to run schema updates fast. That broad role lets a single bad script drop tables or read private rows.

How to set up goose postgres credentials with database roles

A safe setup starts inside the database before you run a single migration file. First, you create a group role that holds the exact schema rights needed. You can then grant individual user accounts membership in that group role. This keeps permission management clean when many people or bots run database jobs.

Next, you must check what the special PUBLIC role allows on your instance. By default, PUBLIC gives every role the right to connect to the database. It also lets any role create temporary tables and run functions or procedures. Object owners can revoke these default rights so migration accounts stay locked down.

In PostgreSQL 15 and newer, users cannot create objects in the public schema by default. Older databases, or ones upgraded from old versions, still allow this open access. On those systems, an admin must revoke create rights on the public schema from PUBLIC. After you lock down the defaults, grant your migration role only the schema rights it needs.

For read checks during a job, you can grant select rights on specific columns. The select privilege works on tables, views, and materialized views. Keep in mind that select rights also allow the copy out command on those columns. Once the role is ready, store the login string in your runner secret store.

What native database roles solve and what they miss

Native roles do a good job of deciding who can log into the server. They also set hard walls around which schemas a role can read or change. If a developer only needs to view migration status, a read-only role works well. It blocks accidental table drops from that account before they ever start.

Still, a migration runner needs rights to alter tables, add columns, and update rows. Once you grant those rights to a role, the database trusts every command it sends. Native roles cannot tell the difference between a planned migration and a destructive query. If an engineer or an automated agent uses those credentials, they hold full alter power.

Ephemeral Credentials + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static credentials also create a storage problem across laptops and build servers. A login string sitting in a local file can leak into logs or chat history. Anyone who grabs that string gets the exact same schema rights as the migration tool. You also lose clear tracking of which human ran which statement when teams share one role.

Why query-level checks matter for schema tools

Database security requires more than just checking a username and password at login time. Teams need to see and control individual SQL statements while the session is active. A valid migration might add an index, while a bad script might dump customer emails. To the database role system, both actions look fine if the role has table rights.

When you inspect traffic at the statement level, you can block high-risk commands in flight. You can allow schema changes from approved workflows while stopping raw data exports. If a query returns sensitive fields like phone numbers or card numbers, masking rewrites matched fields in results. This keeps raw user records out of terminal logs during a migration check.

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

Should I use the postgres superuser for schema migrations?

No, you should never use the superuser account for routine schema jobs. Create a scoped role that owns only the target schema and nothing else. This limits the damage if a script has a bug or if the key leaks.

Why can a new migration role still create objects in my database?

Databases made before PostgreSQL 15 let the PUBLIC role create objects in the public schema. Upgraded databases often keep this old setting unless an admin changes it. Revoke create rights from PUBLIC and grant them only to your migration role.

How do I stop migration logs from exposing sensitive user data?

First, grant select rights only on the specific columns that the migration needs to check. Avoid running broad select queries inside migration files that print output to the console. For deeper safety, use data masking to rewrite emails and national IDs in query results.

The query runs. The emails stay hidden.