Absolutely correct, yes, the authentication process needs to be handled outside the app.
While it's possible to have a login stored proc, this is not strictly necessary. If you have e.g. an OAuth2 server which is separate from your application, you can use those tokens for authentication with your database.
If the application is completely compromised (e.g. someone has root on server), then a malicious party would be able to act as any logged in user by dumping tokens.
However, what they would not be able to do is arbitrarily dump your database, bypass your auditing, or mess up your application invariants.
While it's possible to have a login stored proc, this is not strictly necessary. If you have e.g. an OAuth2 server which is separate from your application, you can use those tokens for authentication with your database.
If the application is completely compromised (e.g. someone has root on server), then a malicious party would be able to act as any logged in user by dumping tokens.
However, what they would not be able to do is arbitrarily dump your database, bypass your auditing, or mess up your application invariants.