The database was merely an example, there are other shared resources that are going to run into the same problem, like caches. Still, it's weird to imply that database users are meant to map to application users without any kind of justification other than "you can do it" but OK, let's assume that we can. Let's consider Postgres, which will have absolutely no problem creating 10,000 users (not sure about 100,000 or 1,000,000, but we can put that aside anyhow.) You basically have two potential options here:
- The most logical option is to continue to use database pooling as you currently do, and authenticate as a single user. Then, when handling a user request, you can impersonate a specific database user. Only problem is, if you do this, the protection you get is entirely discretionary: the connection is still absolutely authenticated to a user that can do more, and all you have to do is "reset role" and go on your way. So you can do this, but it doesn't help you with server exploits.
- The other way to handle this is by having each request get a separate database connection which is actually authenticated to a specific user. That will work and provide the database level guarantees. However, for obvious reasons, you definitely can't share a global database pool with this approach. That's a problem, because each Postgres connection will cost 5-10 MiB or so. If you had 10,000 active users, you would spend 50-100 GiB on just per-connection resources on your database server box. This solution scales horribly even when the scale isn't that crazy.
And this is all assuming you can actually get the guarantees you need just using the database layer. To that I say, good luck. You'll basically need to do most of your logic in the database instead of the application layer and make use of features like row-level security. You can do this, but it's an extremely limiting architecture, not the least of which because databases are hard to scale except vertically. If you run into any scenario where you outgrow a single database cluster, everything here goes out the window.
Needless to say, nobody does this, and they're totally right. Having a database assist in things like authorization and visibility is not a terrible idea or anything, but all of this taken together is just not very persuasive.
And besides. Postgres itself, along with basically all of the other major databases, are also not necessarily memory-safe. Having external parties have access to your database connection pretty much puts you back at square one for defenses against potential unknown memory safety bugs, making this entire exercise a bit pointless...
Most server software today intermingles data that should be strictly isolated because it's convenient. I don't buy your arguments about efficiency either, because despite having no data isolation web software is still comically (tragically) slow.
- Even if you can afford to eat CPU costs, memory limitations are inflexible.
- Again, this moves the problem to the database. The database is still written in C, and still deals with multi-tenant workloads in the same process, and you assume your application server might get RCE'd, which means now someone has a connection to your database, already authenticated, and it may very well have memory safety or other bugs. You can't escape this problem by moving it.
- The most logical option is to continue to use database pooling as you currently do, and authenticate as a single user. Then, when handling a user request, you can impersonate a specific database user. Only problem is, if you do this, the protection you get is entirely discretionary: the connection is still absolutely authenticated to a user that can do more, and all you have to do is "reset role" and go on your way. So you can do this, but it doesn't help you with server exploits.
- The other way to handle this is by having each request get a separate database connection which is actually authenticated to a specific user. That will work and provide the database level guarantees. However, for obvious reasons, you definitely can't share a global database pool with this approach. That's a problem, because each Postgres connection will cost 5-10 MiB or so. If you had 10,000 active users, you would spend 50-100 GiB on just per-connection resources on your database server box. This solution scales horribly even when the scale isn't that crazy.
And this is all assuming you can actually get the guarantees you need just using the database layer. To that I say, good luck. You'll basically need to do most of your logic in the database instead of the application layer and make use of features like row-level security. You can do this, but it's an extremely limiting architecture, not the least of which because databases are hard to scale except vertically. If you run into any scenario where you outgrow a single database cluster, everything here goes out the window.
Needless to say, nobody does this, and they're totally right. Having a database assist in things like authorization and visibility is not a terrible idea or anything, but all of this taken together is just not very persuasive.
And besides. Postgres itself, along with basically all of the other major databases, are also not necessarily memory-safe. Having external parties have access to your database connection pretty much puts you back at square one for defenses against potential unknown memory safety bugs, making this entire exercise a bit pointless...