5 ms·
Simple API with Nginx and PostgreSQL
- kclay 13y agoPretty neat, what about SQL injections?
- osi 13y agolooks like that door is wide open
- ricny046 13y agoThis example is not secured against SQL injections but you can do it easily. Look at the documentation here: https://github.com/FRiCKLE/ngx_postgres/ https://github.com/FRiCKLE/ngx_postgres/
- jeffdavis 13y agoTo clarify, it looks like "postgres_escape" is the way to do escaping. Unfortunately, it seems a bit awkward to differentiate between empty and NULL strings. That's something to be careful of. Also, I really think this should be included in the blog post, even if it's simple. Protecting against SQL injection is not optional, so leaving it out only muddies the comparison with more traditional frameworks. Also, there's always going to be someone that copies and pastes it without thinking much about it. That being said, this is very cool and I hope to see more development and exploration in this area.
- ricny046 13y agoGood point. I will update the examples in the blog post as soon as possible.
- ams6110 13y agoFrom the docs re postgres_escape: "Because nginx cannot tell the difference between empty and non-existing strings, all empty strings are by default escaped to NULL value." This behavior actually is what anyone who has used Oracle is accustomed to (empty string is NULL). I don't recall the default behavior in Postgres but Postgres is "Oracle-ish" in a lot of ways so I would be surprised if this is not the default there as well.
- jeffdavis 13y agoThe "empty string is NULL" behavior you describe is a standards violation. Postgres does not follow Oracle off of that particular cliff; postgres does differentiate between the two.
- sixbrx 13y ago... And Oracle is going to change that any day now for varchar, which is why they've been hounding us to use "varchar2" from time immemorial. (They wouldn't have any other reason to encourage varchar2, would they?) :)
- hedonist 13y agoYeah but by the time we've taken care of injection issues (and handled all the other gotchas that haven't been articulated yet), we've written about as much boilerplate as we would for just about any other scripting language / framework, wouldn't we? Don't get me wrong -- it's still a pretty neat idea. It's just there's that "other 90%" (i.e. the necessary work to create anything seriously production grade) that always comes to mind when evaluating a new interface.
- ams6110 13y agoI would implore blog writers to not do this. I realize it makes the code easier to read, but even if you loudly disclaim "THIS CODE IS INSECURE DO NOT USE IT AS-IS" there are plenty of copy/paste coders in the world who will do exactly that, perhaps even intending to come back later and add in the protections, but never actually get around to it.
- taf2 13y agovery cool, add in some lua scripting and you can get some pretty solid single purpose endpoints: http://wiki.nginx.org/HttpLuaModule http://wiki.nginx.org/HttpLuaModule maybe not perfect for a whole app but maybe depending on your goals.
- moens 13y agoCan you recommend a public repo which demonstrates use of the HttpLuaModule? [Edited to add:] Another comment mentions the Lapis framework which gives [me] a good starting point. @subs: thanks!
- justincormack 13y agoThere is a presentation here that goes through the basics. http://www.londonlua.org/scripting_nginx_with_lua/index.html http://www.londonlua.org/scripting_nginx_with_lua/index.html
- iamlacroix 13y agoYou can also check out a simple example[1] used for the TechEmpower Benchmarks[2]. [1]: https://github.com/TechEmpower/FrameworkBenchmarks/tree/master/openresty https://github.com/TechEmpower/FrameworkBenchmarks/tree/mast... [2]: http://www.techempower.com/benchmarks/ http://www.techempower.com/benchmarks/
- chanux 13y agoHope this will help you http://blog.cloudflare.com/pushing-nginx-to-its-limit-with-lua http://blog.cloudflare.com/pushing-nginx-to-its-limit-with-l... This helped me setting up nginx/lua/redis setup on Ubuntu https://github.com/ehazlett/nginx-rt-log/blob/master/readme.md https://github.com/ehazlett/nginx-rt-log/blob/master/readme....
- joevandyk 13y agoIf you created stored procedures in postgresql that accepted a json/hstore argument, you'd have a pretty flexible/fast system here. i.e. PUT to "/api/blah" with {"id": 1, "name": "Joe"} could call the update_blah stored proc passing in that json.
- bb6xt 13y agoInteresting. I'd like to see how you implement role based access control and sql injection safeguards. How would you define complex validation schemes? In stored procedures?
- terabytest 13y agoSimple, you'd use a framework.
- bb6xt 13y agoI thought as much. If the goal is to not write code then writing stored procedures is akin to writing code, just in a different language.
- stevewilhelm 13y agoHow does one do user authentication and resource authorization?
- mp3jeep01 13y agoYou can easily do AuthBasic in nginx with a user file
- jahewson 13y agoPresumably with an 'api_key' table and an 'Authorization' header. Alternatively, Nginx can do cookies and have if-then logic to check them.
- jallmann 13y agoAuth Basic can be done directly against postgres, plus a few more modules (headers-more and eval was how I did it, IIRC). Could probably do it with just openresty too. After authentication, you have the credentials needed check authorization, it'd probably just involve a few changes to the SQL in the article.
- nickstinemates 13y agoIs this a "look how cool/flexible nginx is!" article or a real proposal about how to do API's?
- leftnode 13y agoHopefully the former. It would be difficult to build anything non-trivial with it. However, I suppose you could build a basic counter or key-value system over HTTP with it.
- ams6110 13y agoIt is by definition difficult to build anything non-trivial regardless. This looks like a very nice way to build small simple REST APIs especially if you're already using nginx and postgres anyway.
- BadassFractal 13y agoOn an unrelated note, timestamptz is advisable over timestamp.
- dragontamer 13y agoTimestamptz is one amazing reason to use Postgres over MySQL frankly. Proper time + timezone storage is quite useful.
- asdasf 13y agoPostgresql doesn't offer proper timezone storage, or in fact any timezone storage. Timestamptz is just a UTC timestamp that is converted to/from your connection-specified timezone. You completely lose the "what timezone was this originally specified in" data.
- tobych 13y agoAnd see this StackOverflow question: http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170 http://stackoverflow.com/questions/9571392/ignoring-timezone...
- akurilin 13y agoThe point is that the timestamp type is meant more for calendaring, unless you're manually normalizing the dates you're passing into postgres to a certain timezone like UTC. Timestamptz is what you actually want 99% of the time.
- asdasf 13y agoYes, timestamptz is the one you want 100% of the time. But there are a ton of people who don't realize it doesn't work the way they think it does. Lots of people think it stores the timezone, and it is really important to understand that is not the case when designing your app. Postgresql is actually completely lacking in the ability to store and validate timezones, as well as converting values to other timezones in useful ways. It is quite possibly the biggest weak spot postgresql has.
- egeozcan 13y agoIf you use lapis[1] things will be much easier. [1]: http://leafo.net/lapis/ http://leafo.net/lapis/
- Oculus 13y agoI wonder what the difference in performance would be compared to NodeJS and Express.
- cgarvis 13y agohttp://www.techempower.com/benchmarks/#section=data-r6&hw=i7&test=db&f=g-8-0 http://www.techempower.com/benchmarks/#section=data-r6&hw=i7...
- jahewson 13y agoI really like the simplicity of this approach, but are the Postgres calls asynchronous? If not, it won't scale...
- mfenniak 13y agoI think the answer is yes, it performs the calls asynchronously. I've been searching through the source code for the postgres module (https://github.com/FRiCKLE/ngx_postgres https://github.com/FRiCKLE/ngx_postgres), and it appears to use the libpq asynchronous API (http://www.postgresql.org/docs/9.2/static/libpq-async.html http://www.postgresql.org/docs/9.2/static/libpq-async.html). Of course, one could easily use the async API in a non-async manner, so I'm not 100% certain.
- hbbio 13y agoThe MySQL driver is async, the Postgres one should be as well.
- dangayle 13y agoI made it to the bottom and thought the article was cut off. Turns out, it really is that simple. (Sans the sql injection and auth stuff). Fantastic.
- rgbrenner 13y agoI thought the same thing.. page could use some whitespace at the end or a footer.
- stevoski 13y agoThis article gives a good example of why you SHOULD consider using an existing framework to create a REST API. I see no concern about authentication, authorisation, scalability, protection from SQL injection attacks, nor making the output easily parseable by third-party applications. None of these are issues you can simply say, "I'll deal with that later when it becomes a problem." They are reasons why an existing framework is helpful. Any example of a web application using a database should NOT be using string concatenation for adding field values to an SQL statement. Ever. Really.
- rgbrenner 13y ago"protection from SQL injection attacks" In the module he's using, there is a postgres_escape function.. so there's no reason he couldn't have used it. http://labs.frickle.com/nginx_ngx_postgres/README http://labs.frickle.com/nginx_ngx_postgres/README
- sehrope 13y agoUnfortunately when defaults are inherently insecure, they lead to people building insecure systems. If every single parameterized SQL command needs to include (possibly multiple) escapes then it'll be missed in some places. It's unnecessary anyway. It would be way better would be if the parameters were bound as named parameters. Ex: location ~ /articles/(?<id>\d+) { postgres_pass database; rds_json on; postgres_query HEAD GET "SELECT * FROM articles WHERE id = $id"; postgres_rewrite HEAD GET no_rows 410; } See how $id has no quotes around it? The DB driver should parse the parameter and bind it as a string in that position. If you need to use it as a different data type (ex: integer) then you can do an explicit type conversion. That's how you prevent SQL injection.
- jeffdavis 13y ago"The DB driver should parse the parameter and bind it as a string in that position. If you need to use it as a different data type (ex: integer) then you can do an explicit type conversion." Not sure exactly what you mean here. The protocol and libpq support sending literal values entirely outside of the query itself. There is no reason for the DB driver to do any parsing.
- diminoten 13y agoCan this be done with redis, or memcache?
- scragg 13y agoWouldn't you be throwing away your ability to add a caching layer. I guess their are ngx modules for Memcache/Redis but coding even a simple api in nginx.conf can get difficult? I still like the idea of nginx being able to communicate with different datastores. For example, let's say you want to serve a file but it requires authentication, you can pass some signed request via query string and cross check it in session in redis/postgres wherever.
- bdcravens 13y agoDoesn't couchdb accomplish this? (out of box REST API with little config)
- Ixiaus 13y agoCouchDB's support for "views" and using comet/websockets (have they implemented websockets yet?) to communicate with a JS app served from the DB itself... Riak does too - any DB that implements a RESTful interface to the database instead of a binary protocol. CouchDB is particularly well-suited for this use-case though (I would never use Riak in that fashion).
- evan2m 13y agoExactly. Couchdb is preferable to this for so many reasons. The author is effectively using the Nginx configuration language for server-side scripting. The result ends up looking a lot like a bad PHP crud app. With couchdb, the REST API is already built in.
- ams6110 13y agoExcept if you already have a postgres-based system and you want to build a simple REST api around some small piece of it.
- euphemize 13y agoGreat for a quick little hack, but I wouldn't want to maintain that conf file once the project grows.
- eksith 13y agoWell, this is for a very simple REST API and the post acknowledges that much. I mean if this does become a "project", I'm sure it's time to move it to a proper framework, but as you say for a quick hack, it's a pretty nice way to do it (with proper sanitization, of course). I'd also venture that this is an order of magnitude faster and more responsive than a framework so there are definitely some plusses and minuses to consider.
- euphemize 13y agoyep agreed. if you're comfortable with nginx and just want to test something quickly, this is many times better than downloading a framework, setting up, routing, etc.
- level09 13y agoThis is amazing, always wondered if I could move things from the app server to the web server level. but are there any performance benchmarks ? I suppose nginx would have a better performance.
- nickstinemates 13y agoVery curious - why would you want to do that?
- twism 13y agoAn oldie but goodie on this sort of stuff on nginx ... http://agentzh.org/misc/slides/nginx-conf-scripting/nginx-conf-scripting.html#1 http://agentzh.org/misc/slides/nginx-conf-scripting/nginx-co...
- AYBABTME 13y agoWhy use a SQL database when all you really want is a key-value store?
- DrJokepu 13y agoBecause PostgreSQL is very good at being a key-value store?
- gbog 13y agoWho says he wants a key value store? This setup is powerful by its simplicity. Only two layers. And stored procedures can help you do validation, so one could imagine a full Twitter like implemented this way (lest the ui, obviously)
- AYBABTME 13y agoThe API he implements suggests a key-value scheme.
- ricny046 13y agoIt was a bad move not include protection against SQL injection to begin with. I was thinking about keeping it as simple as possible and just show a simplified example. But I think this was a bad thing to do so I have updated the example so it now uses postgres_escape. And yes, this approach should only be used for very simple APIs. If you're building something bigger, use a framework.
- purple_horse 13y agoIf you want a really good REST API for Postgres you're better off using HTSQL. http://htsql.org http://htsql.org
- EmmanuelOga 13y agoEvery time I see something like this I think it is an opportunity to finally do something with http://www.eclipse.org/Xtext/ http://www.eclipse.org/Xtext/. How cool would it be to come up with a simple (external!) DSL to mount SQL queries over HTTP routes, and transform the whole thing to a nginx configuration file?
- davyjones 13y agoI want to chime in that I have been using a similar setup: nginx, ngx-postgres, postgresql 9.2.4 in production for a while. It has been very snappy and rock solid till this point. I used android async-http to talk to the server from my app to have everything online. This is an intranet app so latency is not really an issue.
- ghostdiver 13y agoPostgres already offers HTTP interface: http://wiki.postgresql.org/wiki/HTTP_API http://wiki.postgresql.org/wiki/HTTP_API
- rrjanbiah 13y agoI'm looking for information on how to return homepage or template files from such pure REST interface. If you have any idea, please share.