Changing credentials to PostgreSQL without downtime

Questions / Help
, ,
1

My API app relies on Ecto to access PostgreSQL DB. I’d like to change the user and password, without restarting the app. How would you go about it?

We’re using Repo’s init/2 callback to read new DATABASE_URI value from ENV.

Is there a way I could tell the supervisor to re-read the config?

This question is inspired by an excellent feature of Heroku’s PostgreSQL, which makes it possible to rotate production credentials without app downtime [link].

2

Stoping the repo (supervisor) abnormally would be a way to force that, but it won’t be totally without downtime. More like a network hickup. The only way to have no downtime at all is by first starting new connections before stoping the old ones, as described in the docs of heroku. I’m not aware of any functionality within ecto for doing that, but you might be able to build the necessary code around an ecto repo to enable such a behaviour. I imagine it would be similar to tools handling master and readonly-replica usage with ecto, which under the hood also use multiple repos.

3

I had the same concept in my application (still very much in development). You can use :configure option. This is function that is ran before each connection, so this will allow fetching new credentials. Also you do not need to shutdown the DB connection after a time, just use it until it will go down on it’s own (credentials are checked only on connection, so if it will became old, then connection can still be on). I want to integrate Vault database backend into my Ecto connection, and this is how I want it to roll.

4 Likes
4

This might help
https://www.jannikarndt.de/blog/2018/08/rotating_postgresql_passwords_with_no_downtime/

I heard about in https://www.scalingpostgres.com Scaling PostgreSQL podcast

5

This is useful, thank you! Tested it, works flawlessly:

iex(1)> System.put_env("DATABASE_URL", "postgres://newuser:newpassword@12.34.56.78:5432/app-my-prod-db")
:ok
iex(2)> MyApp.Repo |> Process.whereis() |> Process.exit(:kill)
true
iex(3)> # the pid fo Repo has changed, the app is now using new credentials

At least this is an improvement over re-deploying the whole app to pick up new ENV value on the start.

Do you know if all process three beneath Repo supervisor are forced to abruptly stop as well? I’m mostly worried about those processes that are in the middle of handling a DB transaction.

Is it possible to tell Repo supervisor to shutdown gracefully, e.g. so that all DB worker processes finish in case they were in the middle of doing smth useful? (then I’d expect Repo supervisor to be revived by app supervisor).

@preciz thanks, I’m not sure I it’s useful in my case. The article says: “change the application and then deactivate the old role”, but doesn’t go into detail about how exactly to change it.

6

Super cool! I’d love to see something like that too. I imagine an automated process that would rotate production DB credentials periodically by first programmatically telling Vault to generate a user/password pair, then asking the app to pick up a new pair from Vault.

7

As I said, there is no reason to stop connection when not needed, just run existing connection and let it pick new credentials when it will need to restart (for example due to error). Till then there is no reason to disconnect.

1 Like
8

That makes sense, but perhaps I’m missing something. Can you elaborate how do I do that with a code example?

1 Like