Stack Overflow runs everything across what, 4 MS SQL Servers in total?
How has that "failed the web?"
Stack Overflow runs everything across what, 4 MS SQL Servers in total?
How has that "failed the web?"
DBs have auth systems already, but we mostly bypass those and reimplement auth in the stateless layer. DBs have programming capabilities but we mostly bypass that and build business logic in the stateless layer. DBs are great at storing and maintaining relations and relationships but much of the other functionality is little-used.
I would even argue that DBs have overstepped their bounds already with what programming abilities some have built-in. They should be focused on reading and writing structured data efficiently and correctly, not much more than that. Which database to use should depend on access patterns and structural requirements. Let the application logic be handled at a higher layer instead of mixing responsibilities everywhere.
Most RDBMSs were built to be exposed on untrusted networks, and their built-in security has explicit mechanisms to address this.
And they have a stringly typed interface! It sucks! When both the languages inside and outside the database are typed, why should the interface between them not be?
It is just unfathomably bad.
I'm not sure how much an issue this really is, but it's what the GP is talking about.
When the language in the database is typed (SQL) and the language outside is typed too (c#, typescript, etc), it would be nice to keep that type information.
An ORM doesn't count since it is just a bad attemot to work around the inherent problems with SQL as an interface.
Uh, no. Nearly all database access protocols maintain type information for the resultsets returned to the client. That's definitely the case for postgres (cf. RowDescription in https://www.postgresql.org/docs/devel/static/protocol-messag... , accessible to the provided client library via PQftype,cf. https://www.postgresql.org/docs/devel/static/libpq-exec.html... )
There's plenty cases to be made against RDBMs. No need to invent more.
If ActiveRecord (Rails) allowed you to pass a free-form ruby function and built a procedure and executed it in SQL, I'd be all for it, but I'm not writing a one-off procedure and mantaining it.
Noone cares if a large query takes 10 seconds or 11 seconds with ActiveRecord enough to blow developer time on it.
This is sane and good design. The db auth layer is for db management, not frontend auth (are you seriously advocating this?).
> DBs have programming capabilities but we mostly bypass that and build business logic in the stateless layer.
This is just (initial) design / programmer laziness. I bet if you start to scale up, you will think a lot about moving your business logic further to the real data for safety and for performance reasons. This is the point when you realise that db systems are very different and switching from one to another is not that easy...
The auth layer of PostgreSQL is very advanced, up to row level security if you need that. [1] It was never intended to be "just for administration", quite the contrary! Moreover, some applications rely exclusively on a central PostgreSQL service rather than a central web service, such as "Artikel23" which is (was?) a CRM whose user management, encryption and security stuff is 100% handled by PostgreSQL.
[1] https://www.postgresql.org/docs/current/static/ddl-rowsecuri...
Yes, but why? The database already has a table of database users, and the different permissions they're allowed. (Well, hopefully your webapp doesn't just do everything as the root DB user with * privs.) Is there that much to be gained from the database having a second table of users that you've created, and then you get to reimplement a bunch of logic in your app server on top of the database so that you have... users and permissions?
Or, would you rather swap a regular user entry into and away from a maintenance role when you hire/fire someone? (Certainly doable, but seems extreme.)
You also gain the ability to severely lock down destructive permissions to the database behind fairly rigid authentication rules.
How is this more extreme or more cumbersome than deleting an admin account?
For that matter, you could also just give admins a separate admin account even if they are also users. When I worked in "IT support", I had a separate admin account even though I had a user account in the same system.
The argument seems to be that the logical separation is not desired. But that implies that if I were an employee of Google, I should have a specially flagged Google account. And if I were to leave, I would lose that flag. Which means it should probably not be a simple flag, anymore, but a date gated property which may go off an on several times.
And already, that sounds more complicated than just keeping your application accounts distinct and completely separate from your organizations accounts.
The separate system your company's employees are tracked is is also likely not integrated with your app database's auth system. I kind of doubt that the various Postgres/MySQL/Whatever databases at Google have accounts in them for all of Google's employees who might need access. Instead they build more tooling on top of the same accounts the app uses to access the DB. Or maybe they create JIT accounts for admin functionality. As it stands today, though, most places don't use the DB auth at all really.
> But that implies that if I were an employee of Google, I should have a specially flagged Google account.
No. You have two accounts if you work at Google. Your personal account is not your corp account. That doesn't mean your corp account doesn't exist in the same systems as your personal account, though.
> And already, that sounds more complicated than just keeping your application accounts distinct and completely separate from your organizations accounts.
Maintaining two entirely separate auth systems does not sound less complicated to me.
What you're actually arguing for is keeping app auth separate from corp auth. And that's pretty reasonable, but app admin is app auth, and must move with the app.
More generally, though, anyone buying your app/company will want it integrated with whatever auth they already have anyway, so you can look forward to a big user migration regardless.
Now, it is not uncommon to actually do this in three parts. A set of users that covers administration. Often made by tools that guarantee corporate level access to these accounts. And then a separate system built on top of them that does the application.
If you have truly "admin" users in the system; then yes, they should be separate from corp. And should be a flaggable part of a normal user's account. Mixing these with who can create the tables that are required for the system to run seems awkward, at best.
Consider, why not support all users being able to just login to the systems that run the application? It is ultimately the same argument, no?
I don't know what this means.
I don't think there's a good argument for having multiple auth systems in general, much less three. It's a lot of redundancy for little reason. It ends up complicating every app as they end up needing to support multiple systems to support both end users and administrators.
I wouldn't be surprised to find that Google has a single auth system for all users, including employees, and that they've wired up their databases to honor the single auth source.
And how is that a different argument than what you are proposing here?
How is it extreme; granting or dropping permissions to a particular Role for one user account is certainly not more extreme than dropping or addin a user account in that situation, which is the alternative.
> You also gain the ability to severely lock down destructive permissions to the database behind fairly rigid authentication rules.
Actually, no, you don't, because multiuser RDBMSs (well, most of the commercial ones, plus Postgres, and I'm pretty sure Firebird; MySQL/MariaDB may be weak here, they often seem to be in things that I'd like to say are generally true of RDBMSs) built in security and auth systems already give you this.
My point is that there is not a single user of my applications that has the ability to drop tables from a database. In your world, there would be some. But again, why? Why should any given user of GMail, for example, be able to delete any accounts on GMail? I can see why an employee of Google would be able to. But why a user of GMail?
Only if the DB is used by multiple apps and not designed correctly for that use (e.g., all apps use direct access to base tables rather than app-specific views.)
Unfortunately, this is more common than it should be, but the solution is to stop doing that.
> This is just (initial) design / programmer laziness.
I disagree, it's just good practice. I've never seen a system evolve towards SQL based business logic, only away from it. SQL is meant for storage, that's it, obviously there needs to be a coding layer to move data in and out of it efficiently but that's where it should end.
Granted, I'm not saying it's a good idea. I do see a vast array of benefits, primarily in the sense of scalability, from separating the business logic from the DB, but it's an interesting topic to ponder nonetheless.
Note that in PostgreSQL, user defined functions can be written in many languages, including SQL but also Python (very well supported) and PHP (not so well supported, but doable).
See the docs for NOTIFY/LISTEN for an easy built in solution. Use a trigger to write to a log table for durability.
And the time travel functions.
All with the powerful query engine.
There are projects to stream all operations straight into Kafka.
The nice thing about Postgres is that it's also extensible. PipelineDB, Citus, etc.
I click the accept answer button on my web browser, stack overflow stack reads the cookie my browser presented, does a bunch of logic in the app server, then does a database lookup. It then takes the web request, does a bunch of other logic with it in the app server again, then... runs some more SQL against the database.
Stack Overflow, and I, and presumably you as well, have written tons of custom code that sits between the web browser and database, but the article's thesis is this is stupid because if the database "just" spoke http, then it could do the majority of that natively, no Ruby/Python/JavaScript/.Net or anything needed.
The issue then becomes scaling. Instead of having half a dozen database nodes you need to keep in sync you could now dozens or hundreds. Possibly even thousands on some applications.
The point of databases is they're meant to be a monolithic repository of persistent data. The point of web servers is that they're supposed to "dumb" and interchangeable. You meant to be able to spin up new instances to scale to demand. This gets greatly more complex if your application is also your database.
It's also not true that databases haven't evolved for the web. We now have NoSQL databases, JSON support in many RDBMS and in-memory databases for caching. Running your web application inside your database is already possibly. There have been posted on HN discussing people doing this. But it's really not practical for much beyond a small scale blog or private (IP whitelisted) API server. ie the kind of thing that the application be hosted on a single node instance and potentially with the DB sharing the same VM too. The moment you need to scale sideways, even just to a small few nodes, it becomes more advantageous to have your database separated from your application servers.
It's also worth mentioning that NoSQL encompasses a much broader spectrum of database engines than just key-value stores. Technically any relational database can also be NoSQL so long as it's query language isn't SQL (or even not just SQL if you go by the "not only SQL" definition of NoSQL like some do. Though i find that interpretation too broad personally).
There are multiple reasons that people stopped shipping big blobs with application logic and now rely on backend servers, but database servers having "failed the web" are not among them.
It's hard enough to learn a programming language the first time that I can see how ORMs and frameworks relieve the burden of having to learn SQL, RDBMSs, etc.
However it's a convenient lie that seems to rarely ever be corrected.
The reality is that ORMs are exactly the wrong abstraction. They introduce premature pessimization into your application. They are not structured around how your data should be designed but how data-structures in your language of choice are designed. And left uncorrected they lead many developers to the mistaken belief that RDBMSs are slow, inflexible, etc, etc.
It turns out that relational algebra gives us great tools for querying complex data structures and relationships and building consistent, correct abstractions.
RDBMS servers have great facilities for managing and interacting with our data. Procedural languages, authentication, authorization, inter-process communication, etc, etc.
Showing a consistent interface for data update and query.
When there are multiple teams developing against the same DB server, they might be able to see most or all of the tables, but they each might implement updates/selects (SQL queries) that exhibit extremely different results - which either present or update the data in potentially incompatible or inconsistent ways.
This ultimately manifests itself as errors/bugs which can be very difficult to figure out what went wrong where and why (and correcting the data becomes a nightmare).
Things get even worse when you have people querying the data via ad-hoc methods (like ODBC to an Excel file or something weird like that). Essentially, it is possible (likely) that every team sees a slightly (or greatly) different view from each other.
Coupling the BL closer to the DB level would prevent this; whether via the tools mentioned (views, triggers, etc) or something else. Ultimately, app developers shouldn't be hitting the DB tables directly, but going through these interfaces.
Of course - that brings up a host of problems on its own (table updates become an issue of coordination and conflict between teams, etc) - and there's also the issue of implementing versioning of the business logic (and how that relates to current/past DB structure)...
...but implementing it would solve a lot of headaches (and create more I guess).
/sigh
Makes sense if you can cache only portions of the result, and/or the query optimizer or database layout actually struggle with the join.