I don't want to keep connection strings in prod in files (appsettings.json), I'd prefer them to be stored as environment vars for kestrel, just makes CI/CD easier.

Searching through the forum, documentation and around online didn't really help me all that much, but a little guess and test revealed the answer.

This is how I did it:

You'll need to create a systemd service if you haven't already.

Inside of the service file, you can define the following, and Umbraco will pick it up:

Environment=ConnectionStrings__umbracoDbDSN="<your connection string>"
Environment=ConnectionStrings__umbracoDbDSN_ProviderName: "Microsoft.Data.SqlClient"