Automatically updating Ecto pool connections credentials

Start a genserver which receives credentials on start and every X minutes, it should update config using Application.put_env and invoke Repo.stop - this triggers connections recreation with the new config.

Wouldn’t that crash all queries that are currently running in the pool? I guess in some systems that is ok, but in mine, I need to avoid this as much as possible.

An improvement to that would be to somehow flag the connection as “must_die” and when that connection becomes idle (it finished the current transaction), then it would kill itself and consequently, a new one would be created with the newer credentials.

So, I think I found a good (not great) workaround to it:

defmodule DBAuth do
  def configure(opts) do
    # Get username and password from vault or something else
    {username, password} = Credentials.get_new_cred()

    opts
    |> Keyword.replace!(:username, username)
    |> Keyword.replace!(:password, password)
  end
end

#### runtime.exs:
config :my_db, MyRepo,
  configure: {DBAuth, :configure, []},
  disconnect_on_error_codes: [:insufficient_privilege],
  ...

So, basically, I added a module DBAuth with a configure function that queries Vault, retrieves the new credentials, and updates the opts parameter with it.

This function is used when you set your repo configuration using configure: {DBAuth, :configure, []}.

This will ensure that you will request a new credential every time a new connection is created, but this alone doesn’t handle closing the connection when the credential expires.

To handle that, I added the line disconnect_on_error_codes: [:insufficient_privilege] to my repo configuration. What that line does is inform Ecto that if some query returns error :insufficient_privilege (which is the error I receive when my credential expires), that connection should be killed.

With these changes now my connections are automatically (in a lazy fashion) updated when the credential expires. The only issue is that the query that triggered the :insufficient_privilege error will fail with that error.

Maybe there is some way to handle that in the repo layer with some retry strategy, but I’m not aware how.

But regardless you can handle that in your end checking the error and retrying or simply using a lib like this one.

What do you guys think? Any suggestions are appreciated.

An update to this subject: thanks to a recent update in db_connection, it’s now possible to cleanly ask connections to disconnect (they will automatically reconnect), using disconnect_all/3.

To test this, I’ve written a minimal example here: GitHub - ahamez/vault_ecto: Vault + Ecto = 🌈 · GitHub.
The gist of it is simply this:

{_, %{pid: pid}} = Ecto.Repo.Registry.lookup(MyRepo)
DBConnection.disconnect_all(pid, 1_000)

I hope it will help