> As you said, there are plenty of local options that you only need to run.
I think managed databases are a good analogy here. While I might run my own PostgreSQL/MariaDB instance, many out there won't be overjoyed at the idea of actually needing to run and manage the damned thing, as well as set up some kind of alerting and handling the need to eventually scale it up, in addition to backups and failover.
> It also has the largest risk of compromise and data leaking from any service you may use...
PII is definitely a big concern, even if something like password hashes aren't too useful on their own (provided that they're salted), though in cases like that it might actually make a lot of sense to utilize a widely used and tested solution that's specialized for this particular use case.
In many cases, thousands of people across the globe will be able to develop something and squash any bugs in it better than you might be able to do individually or with your own team, though there might be a few exceptions out there. Auth is probably not one of the cases where you want to write code without a lot of eyes on it.
> ...the largest amount of potential lock-in...
This is debatable: standards like OAuth2 and OIDC technically make many of the solutions and libraries way more pluggable and make it easier to choose between various implementations, depending on your needs.
Of course, something like Keycloak also has its own API (as do many of the cloud offerings) so if you build too much automation around a particular implementation, then that advantage partially goes out the window.
> ...and the least need for integration.
I'm not sure about this, it probably depends on your architecture. If you have a monolithic web app, then you probably don't need a separate turnkey/SaaS solution, whereas if you have an ever growing number of services, whilst you want to manage authentication and accounts against all of them centrally, then something like Keycloak (or one of the cloud alternatives) become way more lucrative.
That said, I'd still opt for self-hostable options whenever possible, albeit I also don't trust cloud based password managers and such, preferring something like KeePass instead. I've probably just come to a different conclusion in regards to usability/responsibility/features/security than some other people.
Sadly, there aren't that many good options out there at the moment, apart from Keycloak. For example, IdentityServer is promising, but went in a commercial direction: https://duendesoftware.com/products/identityserver#pricing