Oh man – like always, I was the problem.
I had to isolate the CREATE SCHEMA statement to its own migration so it happened inside its own transaction (per @kip’s suggestion).
But I didn’t realize until this morning that I had duplicated module names in my migrations (the warning flew by too fast for me to notice last night). So that meant that one of my migration modules was essentially ignored (i.e. replaced by another)… and predictably, things did not work. So now that I’ve had a few cups of coffee, I think I’m back on track for this. Thank you!!
…
The longer response here gets a bit philosophical… i.e. when should IaC solutions (like Terraform) step in to set things up vs. when should migrations set things up? E.g. if an AWS RDS instance gets created with an admin user (not a super-admin), whose job is it to set up the user for the app and its permissions?
One solution is put all of that db setup into Terraform, but then the local dev environment might not match the production one. Another solution would be to have the app bootstrap this stuff… but there are some caveats.
The trick is that there would have to be a runtime config that would prevent Ecto from starting normally because the normal Ecto config could be referencing a user/password that don’t exist yet. (i.e. the database wouldn’t have a role with those credentials yet). So… you could start up the app without Ecto and then prompt a user to manually run migrations via Ecto.Migrator.run/3. For this, a human would use the admin username and password and run the migrations (these credentials would be securely stored OUTSIDE the app), which would create the limited user for the app, create schemas, create tables, etc.. Then you could restart the app with Ecto running normally because only then would the app’s limited user exist.
The other part of this approach would be for the day-to-day development to ensure mix ecto.migrate works. Basically, you’d want to make sure the migrations didn’t try to recreate a user that already exists etc.
I might need more coffee, but I think the plan is doable… I’m just not sure if this is an efficient way of solving it.
