> This approach might seem horrifyingly outside-the-box but has a lot of advantages, and some of the reasons we didn't do things this way historically have been solved in recent years.
From a development standpoint, I definitely think this seems easier! My concerns about this would be almost entirely about security. How does this approach avoid leaking credentials that people could use to access the database outside of the application? More generally, how does this approach deal with the increased attack surface for the database from exposing it to the open internet?
See my other post for a discussion of non-user security topics.
Every app user has a database user, mapped 1:1, so there are no credentials to leak. The DB credentials are the user credentials. In the demo app repository I implement open signup with email confirmation using stored procedures/a server extension. It works by creating a guest user with a well known password, but it's locked down so that the only things it can do are run a couple of stored procedures. Those then send an email to confirm ownership of the address before creating a real DB user.
Open work: don't use passwords, mint SSL client certificates during signup with the private key stored in the OS keystore. Now there's no cookies or passwords to steal! Also, OAuth integration.
The guest user / open signup is certainly a weakness for the demo, which is public on the internet (a dedicated cluster though). It's locked down as much as possible but there are probably lots of ways to screw with the server even without being granted access to anything. And there are no rate limits on signup. It's really a very basic exploration.
That said, there are quite a lot of CRUD apps in the world that don't need to support lots of simultaneous users, or where you can just add read replicas quite cheaply. Think internal apps, dashboards, etc.
From a development standpoint, I definitely think this seems easier! My concerns about this would be almost entirely about security. How does this approach avoid leaking credentials that people could use to access the database outside of the application? More generally, how does this approach deal with the increased attack surface for the database from exposing it to the open internet?