Hacker News Reader

Comment by andrewstuart2 | Hacker News Reader

I don't think this will catch on, nor do I think it really should. Nearly all applications out there need a robust security layer to keep people from messing with things they shouldn't have permissions to.

Additionally, you should always validate business logic on the back end. Otherwise it's trivial to, say, give yourself a 10% raise. Or confirm that you got the new high score.

Even if used as a backend protocol, there's just way too much overhead in HTTP requests and responses.

I can see use for something like this as a dev tool if only given private network access for only dev tools, but aside from that I'm not sure it's that worthwhile.

View on HN

>Nearly all applications out there need a robust security layer

How does using HTTP preclude that?

>Additionally, you should always validate business logic on the back end.

Uh huh? So how is talking to the back end going to prevent that?

  How does using HTTP preclude that?

Well, some of the discussion suggests client apps (web browsers, mobile apps etc) can talk directly to the database server, and the traditional database server security model is at the table level.

So if I had insert and delete permissions on the Hacker News comments table to insert or delete my own posts, I could insert and delete every user's posts.

It's possible to imagine hacks that might work around this problem (e.g. comments-table-per-user, or liberal use of stored procedures) but none of them sound like particularly good ideas.

You are inventing the problem you want to exist. The traditional database server security model does not allow any application users to have permissions to any tables at all, only views and stored procedures.

i don't think this is a valid counterargument -- even if access is only given to views / stored procs you would have the same issue of a client connection directly to the HTTP api having too coarse-grained security privileges since they could be executed with any input params (unless there was some kindof extra security layer spanning front-end to db layer? sounds crazy)

but this could be a moot point as long as db communication is only done server-side so it can be behind company firewall anyway -- after all, isn't that why the current db security model works? No one should EVER post ajax to the db, essentially. & i dont think anyone is suggesting that, i hope

I don't know how to make it any clearer. You connect as user A. You authenticate. You can only do things user A is given permission to do. Yes, people are suggesting talking directly to the DB. We want that because we already use our DB this way anyways, but we have to have a completely unnecessary web layer sitting in front of it just to pass the request directly back to the database without doing anything to it.

> the traditional database server security model is at the table level.

Actually, many RDBMSs (postgres included) provide security at the command, relation (table/view), and column level.

With that, and appropriate view definitions and triggers, you can essentially as fine-grained permissions as you'd like. We manage this all client side because while much of the theory of this has been around for servers and held up as the ideal model since very early in the RDBMS era, a lot of the necessary features were not implemented in many servers (or implemented so wildly inconsistently between servers as to make practical, rather than theoretical, knowledge sharing in the community difficult.) That's less true now, though it still requires implementation-specific knowledge for the SQL implementation you are using.

HTTP doesn't preclude robust security at all. But how is a database-direct API layer going to enforce Role Based Access Control? Or really any access control model? Database security is at best per-command and per-table, when true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row. Example: user x has access to change payment details (all in the same table of course), but not those for user y. A straight-up HTTP API layer won't have the flexibility to enforce that, or it will be entirely too complex in order to support every possible business rule set.

As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in? Sure, you can validate that on the client side, but I can just as easily use anything that will make an http request to make that same http request without doing any business rules validation. Heck, I can alter javascript while it's running just to circumvent business logic if I so desire.

>Or really any access control model?

The same way you already do?

>Database security is at best per-command and per-table, when true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row.

Stored procedures already solve all the problems you are imagining. Sane people already use them anyways.

>As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in?

The same way it already does? If your database is not enforcing those rules then you are already doing it very wrong.

> Database security is at best per-command and per-table

per command and per relation is more common (though many databases, including Postgres, actually also include column-level permissions, as well), but relations aren't restricted to base tables.

> true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row.

The effects of per column and per row security can (even if per-column permissions aren't directly available), in most DB engines, be managed using either views, stored procs, or both.

> As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in?

A few options:

(1) Order submission is a call to a stored proc which performs the validation then does any necessary table updates, or

(2) Order submission is through insert to a table (or view) which has an appropriate BEFORE INSERT trigger that performs the validation,

(3) Order submission is an uncondition insert to a table, but that table is just an "incoming_orders" table that isn't particularly actionable -- the actionable views on that table (executable_orders and orders_with_payment_errors, say) are views that based on joins that validate the payment information, including gift cards.

There's probably other database-side ways of dealing with that problem, as well.

Theoretically postgres already has a robust layer of security and validation; although it may not be used for the typical web app. It seems questionable to have additional API level checks when the database already implements the same security features in a more tested manner. I don't want to set two levels of permissions for exactly the same thing that may behave differently.

Why not expose something similar to a prepared statement with the HTTP API? That lets you define input data types, and only queries that have been explicitly enabled could be run.

> Theoretically postgres already has a robust layer of security and validation; although it may not be used for the typical web app.

What Postgres has in terms of validation is essentially a not terribly sophisticated type system, plus stored procedures in PL/pgSQL, Perl, Python or Tcl. These are frankly a pain to manage. In terms of security, well... outside of stored procedures, there is no special way of restricting a given connection/user to only rows with the right foreign key in a table. On the other hand, many web framework have some sort of authorization framework.

There are `security_barrier` VIEWs. They give a "way of restricting a given connection/user to only rows with the right foreign key in a table". Still you have to create them all (not a simple task for complex DB scheme).

That'd make in a very simple case (an app with a user_profile table) one view per user? This doesn't sound particularly appealing...

"one view per user" - you can have a single VIEW, that filters rows based on user name ("owner_user_name" column in the base table). Or use some kind of mapping table for "user_name" <-> "table PK id". It gets more complicated with inherited user roles, but still manageable.

They are not a pain to manage at all, and what is so lacking with the type system?

>In terms of security, well... outside of stored procedures, there is no special way of restricting a given connection/user to only rows with the right foreign key in a table

"If I specifically exclude the way to do this, there is no way to do this"? Also views work fine.

It is never entirely obvious where this kind of user/row level security should be placed. Personally I don't like using frameworks with complicated authorisation systems that I don't understand. I don't think it makes sense to use that kind of system for a hand written SQL heavy project which is what this API is undoubtly aimed at.

I would rather integrate user validation into normal queries, or have it handeled at the edge with an application firewall along with other kinds of input validation.

You could always do something like this...

  INSERT INTO test
  select %(inputdata)
  where %(cookie) = hmac(%(userid), 'serverkey','sha256' )
  and char_length(%(inputdata)) < 1000

Having a new application-layer transport doesn't impact any of these things. We're talking about how queries and their results are exchanged between a client and the sql server here.

SQL is a pain to parse, having a JSON API would make things much more hackable.

Yet most NoSQL stores have HTTP interfaces and they work fine, while being convenient to work with.

Compression can reduce the data size overheads, and HTTP auth can deal with security issues too.

Reply on news.ycombinator.com