Access keys can be provisioned and downloaded straight onto the box from the service. Sure, a compromise is bad, but only exposes the secrets that would be available on the pwned box regardless.
So in other words – a unique key is generated every time a virtual machine is created. It's fully automated, never shared between instances, and never needs to be handled manually. That key will give the virtual machine permission to access other AWS services, in this case the AWS Secrets Manager.
So as long as you're using EC2 instances, you won't need to worry about securely passing a 'master password' to your VMs in order for them to access secrets.
I exist, therefore I can.
Having your personal AWS keys compromised should also be a non-issue because of MFA. My keys that I keep in OSX Keychain grant zero permissions, except the ability to assume a more privileged role, but that requires MFA.
FWIW, we're using Parameter Store for secrets and it works great. Given that I just finished that set up just weeks ago, I'm in no rush to jump on the Secrets Manager wagon based on what I'm seeing.
The only problem with both services is the 4k character limit. We have some big cert chains and such that exceed that, so we had to build the tools such that they could put/get a chunked secret. Wasn't too bad though.
You do still have to figure out a way to securely provide the root credential to your service so that it can fetch the secrets from the secret manager, correct? Otherwise this would be magic of a kind I think is impossible.
If my questions aren't too far off in the weeds, then this service sounds like a personal password manager but for a service rather than a person, though I'm sure AWS's service has finer-grained controls than just the all-or-nothing master passphrase. Similar risks apply: an attacker obtaining the master passphrase is a major issue, losing the master passphrase is devastating (though recoverable here because you probably didn't lose your personal AWS login credentials), and unavailability of the password database is catastrophic. But the usability benefits of having everything in one secure place, behind a service managed by experts, should outweigh those risks.
I have more questions about the credential-rotation feature, but this is enough for now.