In a development environment, i would be hugely inconvenient to be without ‘create db’ privilege. This is what the getting started guide covers. In a production environment, you are not going to run mix ecto.create so you don’t need it.

The short answer is: yes it’s possible. The little longer answer is that it’s possible but you’re going to get away from what the majority of tutorial level information is available and have to have some understanding of the toolkit.

I’m going to try to give some pointers to get you going in the right direction. Unfortunately, it’s going to be limited as I’m pretty ill right now and don’t have the stamina to get as in-depth as I should; but I don’t want to leave this question waiting for too long either.

First, you could set up multiple repositories: one for privileged access such as running migrations and the like, and another for regular unprivileged access used by the application. This can be done with multiple repo modules/configs such as:

defmodule PrivilgedRepo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

defmodule AppRepo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

And a config/*.ex something like:

config :my_app, PrivilegedRepo,
  database: "ecto_simple",
  username: "owner",
  password: "owner",
  hostname: "localhost"

config :my_app, AppRepo,
  database: "ecto_simple",
  username: "application",
  password: "application",
  hostname: "localhost"

And then use the appropriate repository in the appropriate context.

In my application, the application does something similar except that I don’t use config/*.ex for anything Ecto related. I have a privileged database role (CREATEDB as well as group member with admin option for some special groups, but can only create new roles using special SECURITY DEFINER database functions which create specific low privilege roles I need to create at runtime). Then I have (very) low privileged roles with which the Elixir application/Ecto connects for business logic interactions.

In the cases above, I’m starting the Ecto Repos in my code more explicitly by calling Ecto.Repo.start_link/1 (Ecto.Repo — Ecto v3.14.0). The privileged role is transient (generic sense) in that I start my Privileged Repo just before using it and shut it down as soon as whatever task I was using it for is finished. Other low privileged roles are continuously connected, but even then I’m starting them up manually using Ecto.Repo.start_link/1, in part because at compile time I don’t know what low privileged connections are actually going to exist.

I’d suggest reading the Ecto Dynamic Repositories documentation even if you aren’t needing to use dynamic repos… the knowledge for getting dynamic repos running is very helpful in getting a handle on the moving parts of how Ecto connects to repos and can provide insights into the problem you’re describing (Replicas and dynamic repositories — Ecto v3.14.0).

Anyway, hope this helps a bit.