nginx: Storing POST data in PostgreSQL

Question

An nginx server provides a simple REST interface, using a PostgreSQL instance as the backend. The nginx should insert POST data (already in JSON format) into a database table. Unfortunately, the $request_body containing the POST data is filled by nginx only on fastcgi_pass, proxy_pass, …

The ngx_form_input doesn’t help either, as it expects the POST data in key-value format. I tried ngx_echo, but this leads to an internal server error:

location ~ "^/api/v1/dummy/$" {
    auth_basic              "Restricted";
    auth_basic_user_file    /usr/local/etc/nginx/.htpasswd;

    if ($request_method != POST) {
        return 405;
    }

    client_max_body_size    100k;
    client_body_buffer_size 100k;

    echo_read_request_body;

    postgres_pass       postgresql;
    postgres_escape     $json =$request_body;

    postgres_query      POST "INSERT INTO mytable (data) VALUES ('$json')";
    postgres_rewrite    POST changes    201;
    postgres_rewrite    POST no_changes 204;
}

It seems that ngx_echo doesn’t work together with ngx_postgres. Are there any other ways to get the request body data?

score 2 · Accepted Answer · answered Mar 29 '19 at 13:24

Both echo_read_request_body and postgres_pass directives work in content phase. Only one module would work in this case.

The problem here that nginx is async in its nature. Nginx may initiate upstream connection before full request body is received.

Using OpenResty you can force nginx to read the whole request body by lua_need_request_body. Take care about client_body_buffer_size and client_max_body_size. Include empty rewrite_by_lua*.

Another possible solution is to write Lua code, for example within set_by_lua_block and read the full request body, keep in mind that it may be buffered into a file, use ngx.req.get_body_file to check it.

Eventually, that’s the only thing that works. I added a `rewrite_by_lua_block` to get the request body with `ngx.req.read_body()` and `ngx.req.get_body_data()`. Thank you for the clarification on why both modules can’t be used at the same time. — laserbrain, Mar 29 '19 at 17:27

score 1 · Answer 2 · answered Oct 18 '20 at 15:03

I agree with Alexander Altshuler. Nginx does not support capturing request_body in the rewrite phase in which the postgres_escape directive is running.

I have modified the ngx_postgres module in my GitHub branch capture-request-body. But my pull request is unlikely to be accepted.

This branch added the POST request capture feature. You can save requests to the PostgreSQL database, for example, using such a configuration in this branch

location /dlr/sms
{
    allow all;
    postgres_escape_request_body on;
    postgres_pass     database;
    postgres_query    POST      "SELECT table2sms.treat_sms_dlr('$request_body')";
    postgres_rewrite  POST      changes 200;
    postgres_rewrite  POST      no_changes 400;
    postgres_output   value;
}

nginx: Storing POST data in PostgreSQL

2 Answers2