4 ms·

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 wi

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.

fla 12y ago
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.
stettix 12y ago
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.
7952 12y ago
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.
- mercurial 12y ago
  > 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.
  - AlterEgo20 12y ago
    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).
    - mercurial 12y ago
      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...
      - AlterEgo20 12y ago
        "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.
  - imanaccount247 12y ago
    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.
  - 12y ago
imanaccount247 12y ago
>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?
- michaelt 12y ago
  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.
  - imanaccount247 12y ago
    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.
    - fat0wl 12y ago
      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
      - 12y ago

fla 12y ago

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.

stettix 12y ago

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.

7952 12y ago

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.

mercurial 12y ago
> 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.
- AlterEgo20 12y ago
  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).
  - mercurial 12y ago
    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...
    - AlterEgo20 12y ago
      "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.
- imanaccount247 12y ago
  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.
- 12y ago

imanaccount247 12y ago

>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?

michaelt 12y ago
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.
- imanaccount247 12y ago
  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.
  - fat0wl 12y ago
    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
    - 12y ago