Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Databases have failed the web

josephg.com

Post reply on HN
Print view View original

101–110 of 144 posts

Re: Databases have failed the web

#101

Post by snuxoll »

mixmastamyk wrote: post #90
snuxoll wrote: post #35

The only part of this I agree with is his comment on database permissions. Every modern SQL database has a concept of users and permissions that are divorced from your application, you're left with three options all of which are flawed. 1. Handle security inside your application. This is the worst choice if users need to get a LIST of records they have access to and it's determined by something more than a simple WHE…

Am looking at building a multitenant app with pg, so thanks for this comment. Know of any resources that discuss these patterns at length?

A lot of it is technology stack dependent, unfortunately.

Option 1 can be implemented when using an ORM with decent support for something like Active Record scopes, with LINQ you could have a method on your model that returns a pre-filtered IQueyrable, with JPA you'd use the criteria API. If you're doing hand written SQL then have fun adding it to all of your queries or using some query builder to add the security filters. Either way, this approach is prone to human error.

Option 2 is a little simpler, assuming you understand the RLS functionality of your database. At the start of your HTTP request before you touch the database you SET ROLE to your tenant or user (you'll have to write some code to create these database users), and in some hook that is called when a connection is returned to your pool you'll RESET ROLE.

Personally, I've had a hard time finding anything decent on handling security in multi-tenant applications or where security goes beyond simple ownership checks. Maybe I'll get around to writing a series of articles on my blog someday.

Re: Databases have failed the web

#102

Post by moron4hire »

bluGill wrote: post #78
moron4hire wrote: post #47

Earlier quoted context omitted.

Because traditionally the database was managed by IT, not by engineering. IT came up with the idea that it should take forever to approve changes to the database, so engineering invented ways around their can-don't attitude. Can you imagine any CTO approving a web-based signup page being allowed to change the users to the database?!

Note that IT has good reason for the complex change rules: if they make ANY change to the data base, everyone needs to change. The change itself is simple, but in many cases thousands of programs access the database. If taxes don't go out on time because the program that runs once a year at tax time didn't get updated - the best case is the CTO goes to prison, things get worse quickly. When prison is a very real risk…

Nobody ever went to jail because a software defect made their tax filing late. Don't be ridiculous. It's this sort of over-aggrandizement of IT's supposed responsibility that is the cause of the problem. IT acts like they are a thin, red line single-handedly holding compliance and hackers at bay. The god complex needs to end.

Re: Databases have failed the web

#103

Post by z3t4 »

So you want to implement a new feature, and test it locally, then run tests to make sure you didn't break anything, and when you are done, upload the changes to the production server. This is sane devops, nothing fancy, yet impossible with todays databases. You basically have to manually do the same changes you did in development to the production database, then test if it works on live data in production. Then your finger might slip when writing an SQL query and you have to reset all production data from backup.

Re: Databases have failed the web

#104

Post by bluGill »

moron4hire wrote: post #102
bluGill wrote: post #78

Earlier quoted context omitted.

Note that IT has good reason for the complex change rules: if they make ANY change to the data base, everyone needs to change. The change itself is simple, but in many cases thousands of programs access the database. If taxes don't go out on time because the program that runs once a year at tax time didn't get updated - the best case is the CTO goes to prison, things get worse quickly. When prison is a very real risk…

Nobody ever went to jail because a software defect made their tax filing late. Don't be ridiculous. It's this sort of over-aggrandizement of IT's supposed responsibility that is the cause of the problem. IT acts like they are a thin, red line single-handedly holding compliance and hackers at bay. The god complex needs to end.

IT is still a risk. High level executives can go to prison for some failures, and tax issues are on the list. It isn't clear if courts will accept software issues as an excuse.

Re: Databases have failed the web

#105

Post by cookiecaper »

fragmede wrote: post #36
agentultra wrote: post #3

I serve many, many millions of rows, collect real-time statistics, push real-time updates, and maintain data integrity and consistency... all from a single database server. If our workloads require it we're prepared to scale out horizontally. I'm really looking forward to PostgreSQL 10's new parallel query features for some of our analytics work. Stack Overflow runs everything across what, 4 MS SQL Servers in total?…

The articles point is that there's a bit more to Stack Overflow than the four database servers it sits on, and because MS SQL is treated as a monolith,a stupid amount of development time, the world over, goes into doing things the database, arguably, could be configured to do itself. I click the accept answer button on my web browser, stack overflow stack reads the cookie my browser presented, does a bunch of logic i…

Right, he's basically saying that he wants the browser to behave identically to the desktop, where all logic is executed locally and direct connections are made out to external databases for storage. I assume this is because he's a language hipster and resents ever having to look at something that's not JavaScript.

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.

Re: Databases have failed the web

#106

Post by carsongross »

dragonwriter wrote: post #98
carsongross wrote: post #97

Look, kids. You are going to have to have an execution layer you can trust somewhere, you can't just expose your data store directly to the outside world or you run into an infinite number of security issues[1]. (Not that this is stopping people from doing exactly this these days.) That somewhere is going to be a server side execution environment, and it will be separate from your data store so you can scale these co…

> Look, kids. You are going to have to have an execution layer you can trust somewhere, you can't just expose your data store directly to the outside world or you run into an infinite number of security issues The security issues your source points to in new, SQL-like (but not SQL) general query APIs (GraphQL, etc.) are actually already fairly completely addressed in modern relational databases, which are designed fo…

Yeah, no.

You can't expose generalized query functionality without either encoding the security constraints in the datastore query layer (which is really, really hard, we are talking column-level security constraints and worse) or limiting the expressiveness of your query layer.

That's the trade off and, in most real world systems, the data stores are wide open to the developer because expressivity wins. Yes, the technology is there to secure databases at the row level, for example, but people don't use it. They just use a user/pass with total access.

A lot of folks are tossing HTTP endpoints on data stores without thinking through the security/expressiveness tradeoff.

Re: Databases have failed the web

#107

Post by dpark »

taeric wrote: post #99
dpark wrote: post #80

Earlier quoted context omitted.

> 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.) 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…

You probably already have a separate system that is regionally and logically separated from your application to control access to company resources. 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 fl…

> You probably already have a separate system that is regionally and logically separated from your application to control access to company resources.

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.

Re: Databases have failed the web

#108

Post by cookiecaper »

josephg wrote: post #89
thehardsphere wrote: post #15

The conceit of this article seems to be that all web applications are merely CRUD apps that do nothing but talk to the database, thus databses are "failing the web" for... not being more than just a database. I see a number of problems with this: 1. Not every web app is merely CRUD. Some of us work on apps that do quite a bit more than CRUD, and the CRUD part is relatively small and boring. 2. There's very little res…

Author here. I think CRUD apps are the most boring example. But think about something like Slack. There's some data and queries, sure - but the app also needs realtime feeds. It needs to have triggers running notification tasks off changes made. There's lots of stuff that need to happen as a result of data changes. Doing all that stuff purely at the application layer is an architectural mess. What you really want is…

Your complaint is that SQL databases aren't message brokers? SQL servers have been abused as message brokers for decades. It's not the use case they're designed for, and while a plugin that integrates this type of functionality into PgSQL would be interesting, there's no reason to expect them to assume that problem space.

If you want the database to handle the incoming message streams, then set your consumers to call a stored procedure. That should alleviate your concern with conflicting data models.

Re: Databases have failed the web

#109

Post by WrtCdEvrydy »

tomc1985 wrote: post #86
WrtCdEvrydy wrote: post #82

Earlier quoted context omitted.

I'm not writing a one-off procedure in RAW SQL. If ActiveRecord could turn functions into SQL equivalents, it would be awesome.

I know, they're so scary aren't they?

No, in terms of Return on Investment, it just isn't worth it for me.

Noone cares if a large query takes 10 seconds or 11 seconds with ActiveRecord enough to blow developer time on it.

Re: Databases have failed the web

#110

Post by kisstheblade »

josephg wrote: post #89
thehardsphere wrote: post #15

The conceit of this article seems to be that all web applications are merely CRUD apps that do nothing but talk to the database, thus databses are "failing the web" for... not being more than just a database. I see a number of problems with this: 1. Not every web app is merely CRUD. Some of us work on apps that do quite a bit more than CRUD, and the CRUD part is relatively small and boring. 2. There's very little res…

Author here. I think CRUD apps are the most boring example. But think about something like Slack. There's some data and queries, sure - but the app also needs realtime feeds. It needs to have triggers running notification tasks off changes made. There's lots of stuff that need to happen as a result of data changes. Doing all that stuff purely at the application layer is an architectural mess. What you really want is…

Why can't they (the "workers") just listen on postgres?

https://www.postgresql.org/docs/9.0/static/sql-notify.html

Post reply on HN