How to Configure RDS Postgres Schema Migration
You set up an rds postgres schema migration by assigning a dedicated database role. That role needs permission to alter tables and create objects in your target schema. Many teams take a shortcut and use the main admin account for every deployment. Broad roles increase risk because one bad script can drop tables across all schemas. A better setup separates the role that changes tables from the role that reads data. How native roles work for an rds postgres schema migration PostgreSQL uses rol
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 an rds postgres schema migration by assigning a dedicated database role. That role needs permission to alter tables and create objects in your target schema. Many teams take a shortcut and use the main admin account for every deployment. Broad roles increase risk because one bad script can drop tables across all schemas. A better setup separates the role that changes tables from the role that reads data.
How native roles work for an rds postgres schema migration
PostgreSQL uses roles to control who can create or change objects inside a database. Teams can set up group roles to manage permissions when many users need access. You grant individual users or deployment runners membership in that group role. This keeps permission management simple as your engineering team grows.
Schema permissions also depend on your database version and past upgrades. In PostgreSQL 15 and later, regular users cannot create objects in the public schema by default. Older databases, or ones upgraded from earlier versions, still allow this through the PUBLIC role. The special PUBLIC role grants permissions to every role on the system. An admin must revoke create permissions on the public schema from PUBLIC to lock it down. By default, PUBLIC also gets permission to connect to databases and create temporary tables. It also gets permission to execute functions and procedures unless the owner revokes those defaults.
When a migration role creates a new table, that role becomes the object owner. Only the owner or a superuser can alter or drop that table later. If different engineers run migrations under their own user accounts, ownership gets split up. The next migration will fail when one user tries to change another user's table. Using a shared group role for ownership prevents these permission errors during deployments.
How to separate app roles from migration roles
A clean setup uses at least two distinct roles for each service. The migration runner uses the owner role to create tables, indexes, and views. The running app uses a separate role that only reads and writes row data. You can even grant read access on specific columns of a table or view. Note that read permission in PostgreSQL also allows a role to export table data.
Default permissions play a big part when you split these two roles. When the migration role creates a new table, the app role cannot read it yet. You must set up default permissions for the migration role before it runs changes. That way, any new table created by the migration role grants access to the app role. If a human engineer runs a migration under a personal role, those defaults will not apply. The deployment succeeds, but the app crashes because it cannot read the new table.
End-to-End Encryption + API Schema Validation: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they leave open
Database roles do a good job of deciding who can log in and own objects. They stop a reporting user or an app role from dropping a production table. They also keep schema ownership consistent when you use shared group roles.
Still, static roles cannot inspect what an individual statement does once a session starts. A migration role must have permission to alter and drop tables to do its job. Native roles cannot tell the difference between a safe column addition and an accidental drop. They also do not require a second person to approve a risky schema change in flight. Finally, sharing a migration password across build servers makes it hard to audit who ran what. And if an engineer uses that role to inspect data, raw emails or card numbers appear.
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 sits inline in the wire protocol beside your database as a sidecar for your Postgres. For every plain or prepared query an agent or human runs against production, hoop.dev decides to pass, mask, block, or escalate it for review.
When a tool or agent sends SQL, hoop.dev Postgres controls block banned statements before execution or hold risky ones for human approval via an AI Analyzer you configure. 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
Why did my new table break app queries after a migration?
Default permissions only apply to the specific role that creates the new database object. If you ran the migration under a different user role, the app role got no permissions. Always switch to the shared owner role before you run schema changes.
Should the public schema keeps the session active for schema migrations?
No, you should revoke create access on the public schema from the PUBLIC role. Databases created in PostgreSQL 15 and later already block this by default. Grant create permissions only to your dedicated migration role on the schemas it owns.
Can a migration role hide sensitive columns from engineers who debug migrations?
PostgreSQL lets you grant read access on specific columns to restricted reporting roles. Still, a role that owns the table for migrations has full read access to all columns by default. Native permissions alone cannot rewrite emails or phone numbers in query results for the table owner.
Related guides
In this post