There's 3 key differences I can think off:
- Using ENV variables typically means they will be in plaintext and visible in the GUI to anyone that can view the function. Reading them at run-time, means that only those principals with access to the secret in SM can view it.
- If you wish to change the secret (e.g. rotate the DB credentials), then using ENV variables you'd need to redeploy the function. This is to ensure any running functions referencing the older variables gets reset.
- Also, by calling SM during run-time, there will be a log into Cloudtrail of when the function pulled down the secret, but with ENV variables, it'll only be logged when you deploy the function (because that's when it was pulled down).
Now some quick caveats, even for calling during runtime, you'd typically call it during init time (outside your handler) rather than on every invocation -- otherwise you'd be charged for each invocation. But if you do this, you'll only log the first call in Cloudtrail, and you won't be able to rotate the password consistently :(. I understood RDS Proxy allows you to store DB credentials at the proxy rather than at the lambda function, but haven't explored this yet.
KMS allow for encryption of ENV variables as well -- again not something I've explored though.