Automatically updating Ecto pool connections credentials

I’m using HashiCorp Vault to safely store secrets for my system. These secrets are read during system initialization (via runtime.exs).

One of the features that Vault gives is creating database credentials with TTL, meaning that your database connection will be available for a specific time (say, 1 hour) and then you will need to ask vault for a new credential and restart the connection.

This way you never have a perpetual credential to the database limiting the window for a data leak in case your credentials are compromised.

This can easily be done using Ecto dynamic repos, but this means that I will have to manage the connection pool by myself.

So, is there some way to add support for dynamic credentials with TTL for the already implemented Ecto pool?

My guess is that I would be able to configure my repo something like this:

config :ecto, Repo,
   update_credentials: &MyCredModule.get_credentials/0

This function would return a tuple, something like

  %{username: "...", password: "..."}, # New credentials
  ~U[2020-01-01 01:01:00.000000Z] # TTL or when pool needs to get new credentials again

I thought about creating an issue in Ecto’s GitHub suggesting something like this, but decided to try here first in case a solution already exists.

PS. I already saw some topics here discussing this, but they are old and/or don’t have any solution, so I decided to create a new one instead of resurrecting an older one.


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.

1 Like

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()

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

#### 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 = 🌈.
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 :slightly_smiling_face: