I use Authelia, and my users are backed by my LDAP server. I basically managed my users directly in LDAP through Apache Directory Studio. If you want something with built-in user management, I believe Keycloak might be a good place to start.

Also, you can use Nextcloud as an OIDC provider and let it be your SSO provider. For apps that don't support OIDC, you may be able to put them behind OAuth2Proxy and point that your Nextcloud.

It depends on the protected app. Some let you delegate to external auth and will create the account (e.g. Nextcloud) and others may expect some kind of HTTP header containing an identifier to associate with an existing local user (e.g Calibre Web). Most apps I've worked with that support OAuth2 usually handle creating the account in the app automatically - the app takes care of that, not the SSO provider.

You'd need to read the docs for the apps you want to have behind SSO to see how each works with external users.