Repository files navigation

ngx_postgres is an upstream module that allowsnginx to communicate directlywithPostgreSQL database.

Response is generated inrds format, so it's compatible withngx_rds_jsonandngx_drizzle modules.

This module is production-ready and it's compatible with following nginxreleases:

• 0.7.x (tested with 0.7.60 to 0.7.69),
• 0.8.x (tested with 0.8.0 to 0.8.55),
• 0.9.x (tested with 0.9.0 to 0.9.7),
• 1.0.x (tested with 1.0.0 to 1.0.11),
• 1.1.x (tested with 1.1.0 to 1.1.12).
• 1.2.x (tested with 1.2.3 to 1.2.3).
• 1.3.x (tested with 1.3.4 to 1.3.4).

• syntax:postgres_server ip[:port] dbname=dbname user=user password=pass
• default:none
• context:upstream

Set details about the database server.

• syntax:postgres_keepalive off | max=count [mode=single|multi] [overflow=ignore|reject]
• default:max=10 mode=single overflow=ignore
• context:upstream

Configure keepalive parameters:

• max - maximum number of keepalive connections (per worker process),
• mode - backend matching mode,
• overflow - eitherignore the fact that keepalive connection pool is fulland allow request, but close connection afterwards orreject request with503 Service Unavailable response.

• syntax:postgres_pass upstream
• default:none
• context:location,if location

Set name of an upstream block that will be used for the database connections(it can include variables).

• syntax:postgres_query [methods] query
• default:none
• context:http,server,location,if location

Set query string (it can include variables). When methods are specified thenquery is used only for them, otherwise it's used for all methods.

This directive can be used more than once within same context.

• syntax:postgres_rewrite [methods] condition [=]status_code
• default:none
• context:http,server,location,if location

Rewrite responsestatus_code when given condition is met (first one wins!):

• no_changes - no rows were affected by the query,
• changes - at least one row was affected by the query,
• no_rows - no rows were returned in the result-set,
• rows - at least one row was returned in the result-set.

Whenstatus_code is prefixed with= sign then original response body issend to the client instead of the default error page for givenstatus_code.

By design bothno_changes andchanges apply only toINSERT,UPDATE,DELETE,MOVE,FETCH andCOPY SQL queries.

This directive can be used more than once within same context.

• syntax:postgres_output rds|text|value|binary_value|none
• default:rds
• context:http,server,location,if location

Set output format:

• rds - return all values from the result-set inrds format(with appropriateContent-Type),
• text - return all values from the result-set in text format(with defaultContent-Type), values are separated by new line,
• value - return single value from the result-set in text format(with defaultContent-Type),
• binary_value - return single value from the result-set in binary format(with defaultContent-Type),
• none - don't return anything, this should be used only whenextracting values withpostgres_set for use with other modules (withoutContent-Type).

• syntax:postgres_set $variable row column [optional|required]
• default:none
• context:http,server,location

Get single value from the result-set and keep it in $variable.

When requirement level is set torequired and value is either out-of-range,NULL or zero-length, then nginx returns500 Internal Server Error response.Such condition is silently ignored when requirement level is set tooptional(default).

Row and column numbers start at 0. Column name can be used instead of columnnumber.

This directive can be used more than once within same context.

• syntax:postgres_escape $escaped [[=]$unescaped]
• default:none
• context:http,server,location

Escape and quote$unescaped string. Result is stored in$escaped variablewhich can be safely used in SQL queries.

Because nginx cannot tell the difference between empty and non-existing strings,all empty strings are by default escaped toNULL value. This behavior can bedisabled by prefixing$unescaped string with= sign.

• syntax:postgres_connect_timeout timeout
• default:10s
• context:http,server,location

Set timeout for connecting to the database.

• syntax:postgres_result_timeout timeout
• default:30s
• context:http,server,location

Set timeout for receiving result from the database.

Number of columns in received result-set.

Number of rows in received result-set.

Number of rows affected byINSERT,UPDATE,DELETE,MOVE,FETCHorCOPY SQL query.

SQL query, as seen byPostgreSQL database.

Return content of tablecats (inrds format).

http {    upstream database {        postgres_server  127.0.0.1 dbname=test                         user=test password=test;    }    server {        location / {            postgres_pass   database;            postgres_query  "SELECT * FROM cats";        }    }}

Return only those rows from tablesites that matchhost filter whichis evaluated for each request based on its$http_host variable.

http {    upstream database {        postgres_server  127.0.0.1 dbname=test                         user=test password=test;    }    server {        location / {            postgres_pass   database;            postgres_query  SELECT * FROM sites WHERE host='$http_host'";        }    }}

Pass request to the backend selected from the database (traffic router).

http {    upstream database {        postgres_server  127.0.0.1 dbname=test                         user=test password=test;    }    server {        location / {            eval_subrequest_in_memory  off;            eval $backend {                postgres_pass    database;                postgres_query   "SELECT * FROM backends LIMIT 1";                postgres_output  value 0 0;            }            proxy_pass  $backend;        }    }}

Required modules (other thanngx_postgres):

• nginx-eval-module (agentzh's fork),

Restrict access to local files by authenticating againstPostgreSQL database.

http {    upstream database {        postgres_server  127.0.0.1 dbname=test                         user=test password=test;    }    server {        location = /auth {            internal;            postgres_escape   $user $remote_user;            postgres_escape   $pass $remote_passwd;            postgres_pass     database;            postgres_query    "SELECT login FROM users WHERE login=$user AND pass=$pass";            postgres_rewrite  no_rows 403;            postgres_output   none;        }        location / {            auth_request      /auth;            root              /files;        }    }}

Required modules (other thanngx_postgres):

• ngx_http_auth_request_module,
• ngx_coolkit.

Simple RESTful webservice returning JSON responses with appropriate HTTP statuscodes.

http {    upstream database {        postgres_server  127.0.0.1 dbname=test                         user=test password=test;    }    server {        set $random  123;        location = /numbers/ {            postgres_pass     database;            rds_json          on;            postgres_query    HEAD GET  "SELECT * FROM numbers";            postgres_query    POST      "INSERT INTO numbers VALUES('$random') RETURNING *";            postgres_rewrite  POST      changes 201;            postgres_query    DELETE    "DELETE FROM numbers";            postgres_rewrite  DELETE    no_changes 204;            postgres_rewrite  DELETE    changes 204;        }        location ~ /numbers/(?<num>\d+) {            postgres_pass     database;            rds_json          on;            postgres_query    HEAD GET  "SELECT * FROM numbers WHERE number='$num'";            postgres_rewrite  HEAD GET  no_rows 410;            postgres_query    PUT       "UPDATE numbers SET number='$num' WHERE number='$num' RETURNING *";            postgres_rewrite  PUT       no_changes 410;            postgres_query    DELETE    "DELETE FROM numbers WHERE number='$num'";            postgres_rewrite  DELETE    no_changes 410;            postgres_rewrite  DELETE    changes 204;        }    }}

Required modules (other thanngx_postgres):

• ngx_rds_json.

Use GET parameter in SQL query.

location /quotes {    set_unescape_uri  $txt $arg_txt;    postgres_escape   $txt;    postgres_pass     database;    postgres_query    "SELECT * FROM quotes WHERE quote=$txt";}

Required modules (other thanngx_postgres):

• ngx_set_misc.

ngx_postgres comes with complete test suite based onTest::Nginx.

You can test core functionality by running:

$ TEST_NGINX_IGNORE_MISSING_DIRECTIVES=1 prove

You can also test interoperability with following modules:

• ngx_coolkit,
• ngx_echo,
• ngx_form_input,
• ngx_set_misc,
• ngx_http_auth_request_module,
• nginx-eval-module (agentzh's fork),
• ngx_rds_json.

by running:

$ prove

Copyright (c) 2010, FRiCKLE Piotr Sikora <info@frickle.com>Copyright (c) 2009-2010, Xiaozhe Wang <chaoslawful@gmail.com>Copyright (c) 2009-2010, Yichun Zhang <agentzh@gmail.com>All rights reserved.Redistribution and use in source and binary forms, with or withoutmodification, are permitted provided that the following conditionsare met:1. Redistributions of source code must retain the above copyright   notice, this list of conditions and the following disclaimer.2. Redistributions in binary form must reproduce the above copyright   notice, this list of conditions and the following disclaimer in the   documentation and/or other materials provided with the distribution.THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOTLIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FORA PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHTHOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOTLIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANYTHEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USEOF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

This software includes also parts of the code from:

• nginx (copyrighted byIgor Sysoev under BSD license),
• ngx_http_upstream_keepalive module (copyrighted byMaxim Douninunder BSD license).

• ngx_rds_json,
• ngx_drizzle,
• ngx_lua,
• nginx-eval-module (agentzh's fork).