> The "interesting" part is how to secure user credentials to login to the RDS instance, and manage connection pools etc, but it's not that difficult
You can run your RDS instances and your Lambda's in the same private VPC. It doesn't secure your credentials per se, but it does prevent anyone else from accessing your database with Lambda.
The "problem" with putting Lambdas into a private VPC is that then you need to do NAT, which means permanent infrastructure (NAT gateways, failover, scaling...). It can be done but there are more headaches than with a pure serverless solution. Unless things have changed since the last time I looked at this stuff.
You just need a private subnet, not a whole separate VPC. You still need NAT to get outbound access, but instead of running a random EC2 instance for the NAT, AWS now has a NAT you can deploy with a click or API call. You still pay for it, but it's at least much easier to set up and there's no maintenance. I assume they have redundancy and failover and such built in also.
You can run your RDS instances and your Lambda's in the same private VPC. It doesn't secure your credentials per se, but it does prevent anyone else from accessing your database with Lambda.