Need Postgresql HTTP API Extension
wiki.postgresql.org
wiki.postgresql.org
Although it is already pretty easy with a couple of lines Perl:
https://metacpan.org/pod/Catalyst::Plugin::AutoCRUD
https://metacpan.org/pod/App::AutoCRUD
http://www.slideshare.net/ldami/app-auto-crud
:)
;)
I've seen people refuse to use LDAP, and request a JSON (not rest) API instead, thus disregarding the security benefits of using a protocol that has 20 years of experience for dealing with access control and authentication.
Postgres has a tremendously powerful and efficient network protocol. There is great value in using and understanding it, because not every problem is a nail that can be hammered with a few GET requests.
Not really. HTTP/JSON is just a transport mechanism, which is not particularly secure in LDAP (it just uses SSL). The layers above of the protocol can simply use HTTP/JSON as the transport mechanism, like multiple existing LDAP-to-JSON gateways show. There are no security benefits being lost.
There is great value in using and understanding it, because not every problem is a nail that can be hammered with a few GET requests.
That would be a good argument against a proposal to replace the existing protocol.
But HTTP is a resource manipulation mechanism: GET/POST/PUT/DELETE/... It's not optimized for transporting data.
Postgres, however, does provide a protocol that is optimized for transporting data [1]. It's also very simple to use, and full of thousands of features that are bug free. Which is why I find it odd to try to recreate a stable and efficient protocol into something less optimized, just so that a minority of developers can use http requests...
[1] http://www.postgresql.org/docs/current/static/protocol.html
Resource manipulation is primarily transporting data (resource representations) -- sure, DELETE, HEAD, and some of the auxiliary verbs aren't so much about that, but GET / PUT / POST are.
What is the reasoning for this except "simple access"? It's not simple when it adds another API on top of an already existing one, that it needs to be kept in sync with. If Android cannot use the normal binary API (which is nowdays pretty secure, compact, bug-free), then Android needs to be fixed.
app <- http -> web middleware <-> libpq <-> postgres
You would just have:
app <- http -> postgres
HTTP is also pretty secure, compact, and bug-free, and has the added advantages of being the universal firewall tunnelling protocol and implementing every authentication mechanism ever invented. It also does compression-on-the-wire, which postgres still doesn't have for its byte-packed protocol, and you don't need as much extra code to interact with it because everybody has an http client already.
What is the reasoning for using the postgres protocol? I'm not really seeing any advantages compared to HTTP.
It's also a lot faster and easier to parse. With HTTP you don't even know how big the request you need to parse is until you've done some parsing.
That obviously defeats the purpose of a firewall, so firewalls will either be eliminated, or will go deeper to do filtering.
"[HTTP is] compact"
Not particularly.
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.
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?
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.
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.
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
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.
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.
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.
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.
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.
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>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.
SQL is a pain to parse, having a JSON API would make things much more hackable.
Compression can reduce the data size overheads, and HTTP auth can deal with security issues too.
I'd imagine it looks something like this when it is finished - http://blog.dreamfactory.com/add-a-rest-api-to-any-sql-db-in.... The concept is similar although I've not used it personally myself. Given that it is open source perhaps Postgresql can learn from the user experience this product has to offer.
IMHO BI one of the reasons BI departments struggle to deliver is because of the technical debt acquired over time. A big part of this debt is in the application layer. Data exists but you need to jump through modelling tools to show it to your end user.
After building an extremely complex Web app, which, at the end of the day is CRUD with tonnes of business logic, I have felt the need for the following:
* Not being forced to write a JSON API layer for data coming straight out of a DB table * Not having to design & implement SQL semantics in an HTTP API, ie limit & offset, or JOINING with a table to get records of an an associated model, or filtering using complex logic. Basically anything that would've been straightforward if written as a bunch of SQL queries. * over time as our app has grown, we have found ourselves implementing a lot of CHECK & TRIGGER CONSTRAINTS on the DB to ensure no bug in the app layer messes up the data invariants EVER. Basically, sometimes I wish I could've written my app logic in the DB itself and be done with it. The old approach of exposing a a custom procedure (plPG/Sql) for each business function.
Actually I think of a step forward and wonder why we need an OS stack in the first place. Just boot directly into the DB server and let it use the bare hardware to deliver extreme performance.
Having worked with an application mostly written in (Oracle) PLSQL, frankly, don't do that. It's a pain to maintain, a pain to test, a pain to version. And PLSQL is fairly awful. For extra pain, use DOM manipulation in PLSQL.
> Actually I think of a step forward and wonder why we need an OS stack in the first place. Just boot directly into the DB server and let it use the bare hardware to deliver extreme performance.
That's what unikernels are for [1], though not exactly bare metal in this case.
I probably wouldn't use it for an application, but for back office things like monitoring and stats it would be great.
Love the new json/jsonb datatypes and this would be a great way to use those.
I'd personally prefer pg to mongo and even without the HTTP API I could see myself using it in the future, though with an HTTP API there would seem no reason not to use pg.
That would allow some time to sort out the tricky aspects like authentication and access control, and also to see what the performance hit is like and find ways to minimize it.
* abstract away the actual tables behind views, so users don't have direct access to the tables, and you can still modify your tables
* add a table-api to abstract away direct crud actions on your data. Table api's are 3G plsql modules that contains business logic to validate and update date (in the database).
* use vpd (virtual private database) to automatically rewrite queries to include security rules (user and rbac security handled by the database)
* have multiple database object owners (scheme) to split up the database object into different modules. Never use scheme owners to access database from your client as a regular app user.