./ahmedhashim

Most migration libraries allow you to easily tack on a GRANT statement right after the CREATE TABLE statement it corresponds with. Now our analytics team needs read-access to the shiny new invoices table, so we append grant select on invoices to analytics_ro to the end of the migration that creates it, and remember to revoke it on down migration.

I’ve authored many of these myself. I stopped. Migrations should only contain information about your data. Roles and grants belong in the platform repo or infra repo that provisions the database itself.

Ownership

Say you work on billing. Six months after invoices deploys, finance signs a deal with a new vendor. Their connector requires read access. So their engineer opens a PR against your service repo. That’s where every grant lives. Nobody on your team knows who this vendor is or if they should see invoice amounts. Someone approves the PR after a week just to make it go away. That grant has nothing to do with your service’s schema. It’s a fact about who inside your org needs to read your data. And that fact will change whenever teams or vendors come and go.

A year later, security comes knocking on your door asking who can read users.email. You grep years of migrations to find the answer. If the grants lived in your platform repo, it’d be one file. Tools like Terraform also show you a plan before anything is applied. They can even catch it when someone creates grants by hand in production. The credential running your migrations no longer needs CREATEROLE. It just needs ownership of its schema.

Replays

Elsewhere, a new hire clones the repo and runs the migrations against their empty local database. They fail with role "analytics_ro" does not exist, since roles are global to a Postgres cluster and only prod has created that role. Someone puts the grant in a do block that checks first. Someone else seeds fake roles into CI so the tests pass.

Staging is a whole other story. Ideally dev/staging/prod would all share roles, but staging never got the warehouse connector added and prod picked up a vendor role nobody added anywhere else. Something that’s correct to grant in prod will error in staging, and a migration can only specify one or the other behavior.

Now suppose a release goes bad and you roll back a migration that happened to contain a grant. Its down migration revokes the grant, even though the vendor’s connector has been relying on it for months, and the warehouse sync fails. Rolling back a schema change shouldn’t deny the warehouse access.

New tables

Shifting grants to the platform repo incurs the obvious concern that every new table needs another PR in another repo before it’s readable by anyone. Postgres has built-in support for default privileges. The platform repo can set them once per schema for the role that runs migrations:

alter default privileges for role app_migrator in schema billing
  grant select on tables to analytics_ro;

Now every table app_migrator creates in billing moving forward is automatically readable by analytics_ro on creation, and your application’s runtime role gets access the same way. The only time you still need a PR against the platform repo is to add a new consumer or access to sensitive data, which is where that decision should be made anyways.

Remember default privileges only apply to objects created after setting the default privileges. When moving an existing database over, \dp lists all the grants you already have in psql. Those get committed first to the platform repo. After that first commit, any grant you see in a migration is just a code review comment.