We ended up using the Nitrokey HSM platform and have been very pleased with the results.
I'd love to get hold of one of my old units from that vendor and try to attack it. I assume it hasn't been done as they're so expensive. It's basically the ultimate in security through obscurity, as it's too expensive to get one you're prepared to break.
That, and it's very important to choose the right vendor. I've worked with pretty much all of them over the years, and some of them can be a nightmare to deal with. Some of the devices themselves are INCREDIBLY frustrating and should never have seen the light of day (much like some of their sales staff!)
In the case where an HSM is used for e.g. database encryption, one absolutely still cares.
The attacker can politely ask the HSM to decrypt all the data. On the bright side, you didn't lose the key, and your HSM should at least securely log the fact that all your data just walked out the door.
For SSL offload the situation is less dire, but it is totally realistic that heartbleed might cause issues for a system with HSM assisted offload (e.g. leaking plaintext) because the protocol processing happens in software. Any sane architecture should protect the actual private key though.
In general, the right way to think about HSMs is that they protect your keys, not your data. If you care more about the data than the keys, you absolutely need to care about the security of your host.
If state actors or universal mitm or coffee shop attacks are primary threats then loss of private key is a very bad outcome for SSL offload. And this is what SSL is meant to help woth.
For many (more?) sites though, the issue was loss of session plaintext (e.g api creds, basic auth creds, pii or credit cards). For a lot of applications where just hitting the webserver remotely is a lot easier than MITM, this was actually the more severe problem.
However, you'd still be vulnerable to someone gaining root access on your Vault server. A true HSM uses physically secure memory and a dedicated cryptoprocessor to make it impossible for the secret to ever be leaked, even if the host OS is compromised. This isn't really something that's possible in software alone.
(In fact, the Vault documentation even mentions that you might still want to use a dedicated HSM for security, since they implement a plugable mechanism for secret storage.)
That said... you could use a TPM chip or ARM TrustZone to implement true TPM functionality. It's still a hardware based solution, but it's hardware that's much more affordable and possibly already inside your computer.
In contrast, while you could think of, say, an ssh key agent as a sort of "software security module", such things are inherently vulnerable to low-level attacks that side-step the intended interface. Say, a kernel exploit that grabs the decrypted key straight from the agent's memory.
So if you need to keep your secrets safe even if you unwittingly use them on a comprised OS, you can only pull that off with a hardware module.
In a previous role we used a major vendor's HSM to protect our private keys. VERY expensive kit, more expensive than the load balancers and servers combined.
We needed to use Elliptic Curve keys for a particular customer - so it got even more expensive as we had to buy:
1. A license from the LB vendor to use the HSM
2. A licence from the HSM vendor to use EC with the LB.
... even though they trumpeted these announcements of how radically great they were together we found:
1. The integration didn't work, full stop.
2. The version of OpenSSL we had to use (supplied) was about 18 months out of date
3. The specially b0rked version of OpenSSL supplied didn't support EC via a HSM
Even better - when Heartbleed came out I had a patch from RedHat on day 1. The load balancer?
Nope - nothing on their website - I had to create a ticket which said 'we are aware of the issue', at which point the ticket was closed. I questioned this and was told they couldn't keep it open, I had to create a new ticket every few weeks to find out whether they'd actually deigned to assign a bug id to the issue.
The HSM vendor just said nothing, zero, until a new version of the firmware was silently released 4 months later.
The whole industry is shit. I'd rather have a farm of Yubikeys than one of those HSMs.