Oh man, it really depends(tm). If you are building a small internal app, sure, but you'd often still be better off leveraging a social provider or employee directory.
I work in the auth space (for FusionAuth) and we run into plenty of folks that started out rolling auth themselves. Just username and password right? A bit of hashing, salting and leveraging a built-in crypto library.
But then you need to add account recovery. And then MFA. And then registration. And then progressive registration. And then webhook integration. And then passkeys. And then SAML integration. And the delegated SAML setup. And then and then and then.
You're distracted from your core application by feature requests for your login system.
You have lots of options nowadays. Use a library provided by your framework (Rails, Spring, and Django have them), use a tool like Better Auth, use a third party system like FusionAuth or Auth0. But don't build undifferentiated functionality that impacts your user experience.
PS Of course, where I stand depends on where I sit, but I firmly believe that you should not build an auth system the same way you should not build a database.
This is kinda like the ORM vs no-ORM argument. I think that off-the-shelf auth will accelerate your development for sure (like an ORM) but eventually, you are going to feel constrained by the framework/tool you are using. You will need to work around it. You will find that using it 'correctly' results in poor performance, and so you deviate here and there. Pretty soon you tell yourself, "man I should have just used SQL" or "man, I should have just rolled my own auth". At least ~20 years of software dev has taught me this.
For an MVP or a prototype, I think it's okay to use an off-the-shelf tool. For something serious that will have long-term legs, I would do it myself. I hear all of your concerns and arguments and agree there are a lot of footguns. But again, having spent the better part of my adult life using and interfacing with these tools, I have an innate understanding of how to model auth correctly (separate it from the user, separate users from an 'org' or 'team' entity, etc).
You said it though, 'it depends' is really the right answer here.