By default, the Credentials provider does not persist data in the database. However, you can still create and save any data in your database, you just have to provide the necessary logic, eg. to encrypt passwords, add rate-limiting, add password reset functionality, etc.
That is exactly the complexity I wouldn't want. With just SSO it is enough to send a redirect URL to the browser and on the callback set a cookie. No js needed. If your service gets compromised and someone leeks the credentials, just log everyone out.
I should have been more clear.
I meant for self hosting.
Though realistically, even if the service is provided for the public, you could just use an instance of keycloak or something similar with open registration. That's what an association I'm close to is doing already.