The usual approach I’ve seen when working with other users is to run Terraform in automation and have that automation run some other software prior to running Terraform in order to gather any necessary credentials, and then populate whatever environment variables or configuration files each provider expects before running Terraform.
In that case, Terraform itself is unaware of where exactly the credentials come from and that concern can be handled by any other software you choose.
In the specific design you described where AWS secrets manager contains the credentials needed for your other providers it seems like your wrapping automation would need to be preconfigured with some AWS credentials (perhaps in the standard AWS configuration file, or indirectly via an external process as I linked in my previous comment) which it could then use to access AWS Secrets Manager to dynamically fetch the datadog/postgres/pagerduty/etc credentials and populate environment variables like
PAGERDUTY_TOKEN, etc before running Terraform.
Another variant of this I’ve seen often is that organizations using HashiCorp Vault will give their automation a
VAULT_TOKEN which has access to issue itself credentials for all of the other services, in which case it’s only the Vault token that needs to be pre-configured in the automation and everything else can be obtained and set dynamically in environment variables in the same way.
Because Terraform’s providers consume credentials the same way as other tools for a particular service typically would, it’s possible to use existing software that isn’t Terraform specific, such as aws-vault for AWS credentials.