This means a more complex database level of roles and privileges, which may be it’s own can of worms, but if you have to choose between problems to have, what would you select?
The postgres roles in particular were a recurring mess until we built provisioning that stuff in to our onboarding automation. If you are going to have individual named users, I highly recommend taking humans out of the process, we make too many mistakes.
And yes, there are multiple advantages to enforcing access control as close to the data as possible. I tend to think of databases as "data structures that can defend themselves" - not just security, but type and data validation, relational references, etc. all can both save you from bugs and help you find them earlier.
What you're describing is what RLS (row-level security) is for, where you log into a generic global "app_user" user with certain permissions that don't include things like admin tables etc, and then define the specific user that is using the session via session variables.
The SaaS build of the app even exposes this in the URLs. Everyone is on the same codebase, and there’s just some additional database logic to connect the right deployments to the right databases.