
Need Postgresql HTTP API Extension - xmulti
https://wiki.postgresql.org/wiki/HTTP_API
======
esaym
This is a good idea.

Although it is already pretty easy with a couple of lines Perl:

[https://metacpan.org/pod/Catalyst::Plugin::AutoCRUD](https://metacpan.org/pod/Catalyst::Plugin::AutoCRUD)

[https://metacpan.org/pod/App::AutoCRUD](https://metacpan.org/pod/App::AutoCRUD)

[http://www.slideshare.net/ldami/app-auto-
crud](http://www.slideshare.net/ldami/app-auto-crud)

:)

~~~
draegtun
Here's the video of that YAPC::EU 2014 presentation by Laurent Dami -
[http://www.youtube.com/watch?v=DF93WU2wTEI](http://www.youtube.com/watch?v=DF93WU2wTEI)

------
jvehent
At what point did HTTP and JSON become synonymous to Simple? Yes, a protocol
and a data format can be convenient when applied to a specific problem, but
that doesn't mean that _everything_ should use them.

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.

~~~
icebraining
_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._

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.

~~~
jvehent
Sure, you can treat HTTP as a transport mechanism, which is what we've all
been doing for the last 10 years because opening ports on firewalls sucks.

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](http://www.postgresql.org/docs/current/static/protocol.html)

~~~
dragonwriter
> But HTTP is a resource manipulation mechanism: GET/POST/PUT/DELETE/... It's
> not optimized for transporting data.

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.

------
lazyjones
Pointless extra API layers is why our software is slow.

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.

~~~
asuffield
Why would it be an extra API layer? This appears to be building an HTTP API
directly onto postgres, so that instead of:

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.

~~~
jeffdavis
The postgres protocol is stateful, which is necessary for multi-statement
sessions (and obviously for multi-statement transactions), as well as COPY.
Emulating it in a stateless protocol is much messier.

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.

------
themgt
“Every program attempts to expand until it can speak HTTP. Those programs
which cannot so expand are replaced by ones which can.”

~~~
jonny_eh
Who are you quoting?

~~~
agildehaus
It's Zawinski's law applied to HTTP instead of mail.

~~~
jonny_eh
Link for the lazy:
[http://en.wikipedia.org/wiki/Jamie_Zawinski#Zawinski.27s_law...](http://en.wikipedia.org/wiki/Jamie_Zawinski#Zawinski.27s_law_of_software_envelopment)

------
andrewstuart2
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.

~~~
imanaccount247
>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

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

~~~
imanaccount247
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.

------
cinbun8
This is quite useful to have. Several tech oriented companies do automatically
create a data service when a new database / table needs to be exposed. No
reason why Postgresql cannot do the same.

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...](http://blog.dreamfactory.com/add-a-rest-api-to-any-sql-db-in-minutes).
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.

------
waffle_ss
There's a little-known repo I'm keeping an eye on that is attempting to do
just this using Haskell:
[https://github.com/begriffs/dbapi](https://github.com/begriffs/dbapi)

------
mmsimanga
I work in Business Intelligence(BI) building data warehouses. I think such
functionality could be useful. I see many posts with security concerns, in a
BI/analytics environment where you don't really modify data but just analyse,
being able to access the data without an application layer could be very
useful.

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.

------
saurabhnanda
I think there is immense value in extending the capabilities of an SQL data
store so that it can communicate over HTTP.

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 &amp; implement SQL semantics in an HTTP API, ie limit &amp; 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 &amp; 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.

~~~
mercurial
> over time as our app has grown, we have found ourselves implementing a lot
> of CHECK &amp; 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.

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.

1: [http://openmirage.org/](http://openmirage.org/)

------
davidhariri
"I like this idea in principle, but in practice I wonder about the
implications of it. Normally the database is a sort of special-purpose
component with its own resources and scaling methods. I think if someone
actually did apply the full power of this system to build their whole web
application logic into the database server they might run into problems
if/when they need to scale up due to coupling the database and application
layer. Everyone who didn't build their web application logic in the database
would pay the performance penalty for parsing / authenticating requests in a
scripting language instead of in compiled C code. The database layer is one
possibly rare place where people will be counting milliseconds of overhead per
request as a problem." This.

------
sitharus
While I'm unsure of what a http query api would look like it would be hugely
useful. Every language has some sort of http and xml or Json library, and so
do many monitoring tools.

I probably wouldn't use it for an application, but for back office things like
monitoring and stats it would be great.

------
fingerprinter
I would very much welcome this.

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.

------
michaelbuckbee
There's a nginx -> postgres module: Frickle that supports at least the
outbound portions of this.

[http://labs.frickle.com/nginx_ngx_postgres/](http://labs.frickle.com/nginx_ngx_postgres/)

~~~
justincormack
Also a Lua driver for openresty [https://github.com/azurewang/lua-resty-
postgres](https://github.com/azurewang/lua-resty-postgres) \- Lua in postrges
is great for doing authentication layers.

------
jeffdavis
This seems like it should live as a layer above postgres for a while, and
after it gets widespread adoption the project could become more of a standard
part of a postgres distribution.

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.

------
ollysb
I'd love to see a jsonapi[1] interface to postgres. I'm mainly building ember
apps at the moment and the rails layer is really just an interface straight
onto the database(all the logic is client side).

[1] [http://jsonapi.org](http://jsonapi.org)

~~~
andrewstuart2
That works fine as long as you never need security or robust application
logic. Somebody with CURL could really mess up your day.

~~~
ollysb
I can't see any reason why application logic would be any less robust on the
client vs the server. With regards to authorization there's examples from the
nosql world, e.g. couchbase's sync gateway[1]. You could argue that you might
as well use rails instead but a proxy that's focused on just authorization can
be far more performant.

[1] [http://docs.couchbase.com/sync-gateway](http://docs.couchbase.com/sync-
gateway)

~~~
andrewstuart2
The reason it's less robust is that I could use curl to send a DELETE request
for records I shouldn't be able to delete. Or POST some data saying my order
is paid in full. Business logic that matters at all has to be validated by
some authority. The client is never good enough.

~~~
troyk
you can do the same with psql, rails bin commands, php, or anything else that
can talk to the db

~~~
mercurial
Which is precisely why your DB server is behind your firewall.

------
davyjones
I was thinking of a pg-as-a-service (got pgaas.com/pgsafe.com) solution not
too different along these lines. Does the idea have any merit? Would people be
interested?

------
abrkn
This needs to happen! Imagine an environment in which curl is available but
you cannot install the client libraries for PostgreSQL.

------
mrfancypants
It mightn't be a good idea to have such tight coupling between data schema and
API...

~~~
joevandyk
You can use views or stored functions to abstract stuff...

------
nicerobot
[http://htsql.org](http://htsql.org)

