You could be right but you have to keep in mind that this just adds several hours -- days if we're talking database non-experts which indeed most of us programmers are not -- and it is just one item in an other huge bucket-list of TODO items.
Having a single DB user reduces time expense while also adding much-needed simplicity. Application-level logging of "who did action X and when" are pretty easy to implement in every major web framework anyway.
Having implemented it, I don’t really agree. It adds a layer of security and auditability at the database level; logging works even if someone logs into the database from a cli and modifies it directly; and you can implement roles in such a way that prevent changes to the audit logs. None of this is possible at the application level.
To me this is one of those things that looks hard until you look deeply. Yes, it does take more time to do things right. But not that much more.
Honestly, if your application is bound to a database but you’re not a database expert, it’s a gap you should probably consider filling. So much effort is put into making the application layer do things that the database layer can already do, but the database is generally much more efficient and correct.
You’re really overstating your case. There is nothing intrinsic to frameworks that makes this difficult.
It’s basically a few lines of SQL code emitted as part of database transaction setup. The frameworks I’ve used have had the ability to register a transaction initialiser during pool setup. Other than that the framework doesn’t even have to know about it. Most of the “complexity” is on the database side, but it’s really not that hard.
“Will any stakeholder pay for it?”. If your stakeholders are serious about application security, then I would expect so.
(I’ll just add that I know of at least one product that does this - Postgraphile sets the database role up front for requests from the web. I seem to recall that Supabase does something similar.)
> “Will any stakeholder pay for it?”. If your stakeholders are serious about application security, then I would expect so.
I want to live in your world where they apparently care. In mine they never did. 20+ years career.
> You’re really overstating your case. There is nothing intrinsic to frameworks that makes this difficult.
You could be right that I am overstating it, though you also shouldn't underestimate the "blessed" ways that web frameworks do stuff. In practically all that I worked on they just wanted you to chuck either a database URL or break it apart on host/port/user/pass/db-name and didn't make any effort to dynamically create / drop users as the application is creating and deleting them.
> The frameworks I’ve used have had the ability to register a transaction initialiser during pool setup.
As said above, I know it's doable. What I am saying is that nobody ever gave me the time to do it and with time I stopped caring. Turned out that putting the currently logged in users in global logger and telemetry contexts is good enough. ¯\_(ツ)_/¯
I see this “system user” all the time in large integrations projects. It is indeed simpler and many times works just fine but it sure is nice to have something like a “run-as” capability so the far side system can apply user specific security policies based on who is accessing the system vs only the system user profile/role.
The run-as thingy is done at application level. I didn't find it hard to plug into my maintained apps' logic to insert audit log entries like that (you have access to the currently signed-in user anyway).
Having a single DB user reduces time expense while also adding much-needed simplicity. Application-level logging of "who did action X and when" are pretty easy to implement in every major web framework anyway.