Is it possible to run migrations as a different user?

Questions / Help
,
1

Hello! I am wondering if it is possible to run Ecto migrations with a different user than the regular user. Sometimes sysadmins restrict database access so the handle used by the app cannot drop or create tables. Has anyone set up a separate Ecto user/repo only for migrations?

2

Migrations are ran by the OS user if you are running through mix

su - <username> -c "mix ecto.migrate"

edit: I just noticed you might be talking about different DB user.

Take the user & password - connection string - from the environment variable and set them just before running migrations.

prod.exs

database_url =
  System.get_env("DATABASE_URL") ||
    raise """
    environment variable DATABASE_URL is missing.
    For example: ecto://USER:PASS@HOST/DATABASE
    """
  • running MIX_ENV=prod DATABASE_URL="ecto://app:pass@" app start
  • migration MIX_ENV=prod DATABASE_URL="ecto://super:superpass@" mix ecto.migrate
1 Like
3

Yes, of course it can be done. Not that easy with “regular” mix ecto.migrate, but there is several ways to do so:

  1. Create additional environment for migrations (for example MIX_ENV=migrate) and then create config/migrate.exs where you configure options for your migrations user (additionally you can add preferred_cli_env: ["ecto.migrate": :migrate] to not need to remember to set proper environment each time you run migrations).
  2. Create custom migration task where you will do:
    {:ok, pid} = MyApp.Repo.start_link(name: :migrations, user: "foo", password: "bar")
Ecto.Migrator.run(MyApp.Repo, :up, all: true, dynamic_repo: :migrations)
:ok = MyApp.Repo.stop(pid)
  3. Use environment variable with config/runtime.exs or Repo.init/2 callback (often DATABASE_URL is used) and then provide different user each time you run application in different scope.

What approach you will use depends fully on your needs and use cases.

2 Likes
4

I should clarify that this must happen on a deployed release without any Mix, more like Deploying with Releases — Phoenix v1.5.8 where I attach to running instance.

5

In that case only option 2 will work for you.

2 Likes
6

Ah, thank you! I think this line should be

Process.exit(pid, :normal)

It works!