DDL statements in Postgres need to be in their own transactions. So its possible you are trying to create the schema and create tables (or do other DML) in the same migration?

If you haven’t already, try to separate the schema creation into its own migration and then anything that uses that schema into other migrations.

(and apologies if you’ve already done that, not totally clear from the above)

Hi @fireproofsocks ,

I quickly set up a new phoenix project from scratch using phx.new.

I simply added two migrations:

20221011044913_schema.exs

defmodule Migrations.Repo.Migrations.Schema do
  use Ecto.Migration

  def change do
    execute("CREATE SCHEMA core")
  end
end

20221011044917_table.exs

defmodule Migrations.Repo.Migrations.Table do
  use Ecto.Migration

  def change do
    create table(:posts, prefix: "core") do
      add(:title, :string, default: "Untitled")
      add(:body, :text)

      timestamps()
    end
  end
end

I did nothing else.

Then I ran:

mix ecto.create
mix ecto.migrate

Everything worked as expected:

image602×374 21.1 KB

Can you please show the full stacktrace for this error?

Can you please also share your Repo config?

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.

The more I think about this, the more I think that the app shouldn’t be involved with any of this “DBA” stuff. The app can define schemas and tables but even that gets sticky because the database user that the app uses may or may not have permissions to create schemas etc. It feels like such a Catch-22 or chicken-and-egg problem.

The thing that really makes the case for me is the fact that PostGres doesn’t store the roles and grants in the app’s database – they get stored somewhere OUTSIDE of that database, so dropping and re-creating the database won’t fix any roles/permissions problems. As soon as you start dealing with users/roles/permissions, you fly off the radar of what Ecto can control. To me, that starts getting into DBA territory because it’s beyond what the app needs to know about to do its job.