README.md
1About
2=====
3`ngx_postgres` is an upstream module that allows `nginx` to communicate directly
4with `PostgreSQL` database.
5
6Response is generated in `rds` format, so it's compatible with `ngx_rds_json`
7and `ngx_drizzle` modules.
8
9
10Community fork
11==============
12
13This is a fork that adds a bunch of new features:
14
15Postgres additions (by @Inviz)
16* **prepared statements** (bound directly to nginx variables). For each connection in pool it keeps its own set of prepared statements.
17* **json output** option to format rows as json arrays
18* postgres_rewrite can now make **redirects** (with interpolations)
19* **`errors`** and **`no_errors`** rewrite conditions that look for `error:` or `errors:` in json response
20
21Server additions (by @cryptofuture)
22* backported **socket** support for pg connection
23* **dynamic module** loading support
24
25Status
26======
27This is a **fork** which is less battle-tested that mainline project. Original version of the module is production-ready and it's compatible with following nginx
28releases:
29
30- 0.7.x (tested with 0.7.60 to 0.7.69),
31- 0.8.x (tested with 0.8.0 to 0.8.55),
32- 0.9.x (tested with 0.9.0 to 0.9.7),
33- 1.0.x (tested with 1.0.0 to 1.0.11),
34- 1.1.x (tested with 1.1.0 to 1.1.12).
35- 1.2.x (tested with 1.2.3 to 1.2.3).
36- 1.3.x (tested with 1.3.4 to 1.3.4).
37
38
39Configuration directives
40========================
41postgres_server
42---------------
43* **syntax**: `postgres_server {ip[:portnum]|unix:/socket/dir} [port=portnum] [dbname=dbname] [user=user] [password=pass]`
44* **default**: `none`
45* **context**: `upstream`
46
47Set details about the database server. Additional port parameter is offered to connect to unix socket with alternative port numbers.
48
49
50postgres_keepalive
51------------------
52* **syntax**: `postgres_keepalive off | max=count [mode=single|multi] [overflow=ignore|reject]`
53* **default**: `max=10 mode=single overflow=ignore`
54* **context**: `upstream`
55
56Configure keepalive parameters:
57
58- `max` - maximum number of keepalive connections (per worker process),
59- `mode` - backend matching mode,
60- `overflow` - either `ignore` the fact that keepalive connection pool is full
61 and allow request, but close connection afterwards or `reject` request with
62 `503 Service Unavailable` response.
63
64
65postgres_pass
66-------------
67* **syntax**: `postgres_pass upstream`
68* **default**: `none`
69* **context**: `location`, `if location`
70
71Set name of an upstream block that will be used for the database connections
72(it can include variables).
73
74
75postgres_query
76--------------
77* **syntax**: `postgres_query [methods] query`
78* **default**: `none`
79* **context**: `http`, `server`, `location`, `if location`
80
81Set query string (it can include variables). When methods are specified then
82query is used only for them, otherwise it's used for all methods.
83
84This directive can be used more than once within same context.
85
86
87postgres_rewrite
88----------------
89* **syntax**: `postgres_rewrite [methods] condition [=]status_code`
90* **default**: `none`
91* **context**: `http`, `server`, `location`, `if location`
92
93Rewrite response `status_code` when given condition is met (first one wins!):
94
95- `no_changes` - no rows were affected by the query,
96- `changes` - at least one row was affected by the query,
97- `no_rows` - no rows were returned in the result-set,
98- `rows` - at least one row was returned in the result-set.
99
100When `status_code` is prefixed with `=` sign then original response body is
101send to the client instead of the default error page for given `status_code`.
102
103By design both `no_changes` and `changes` apply only to `INSERT`,
104`UPDATE`, `DELETE`, `MOVE`, `FETCH` and `COPY` SQL queries.
105
106This directive can be used more than once within same context.
107
108
109postgres_output
110---------------
111* **syntax**: `postgres_output rds|text|value|binary_value|none`
112* **default**: `rds`
113* **context**: `http`, `server`, `location`, `if location`
114
115Set output format:
116
117- `rds` - return all values from the result-set in `rds` format
118 (with appropriate `Content-Type`),
119- `text` - return all values from the result-set in text format
120 (with default `Content-Type`), values are separated by new line,
121- `value` - return single value from the result-set in text format
122 (with default `Content-Type`),
123- `binary_value` - return single value from the result-set in binary format
124 (with default `Content-Type`),
125- `none` - don't return anything, this should be used only when
126 extracting values with `postgres_set` for use with other modules (without
127 `Content-Type`).
128
129
130postgres_set
131------------
132* **syntax**: `postgres_set $variable row column [optional|required]`
133* **default**: `none`
134* **context**: `http`, `server`, `location`
135
136Get single value from the result-set and keep it in $variable.
137
138When requirement level is set to `required` and value is either out-of-range,
139`NULL` or zero-length, then nginx returns `500 Internal Server Error` response.
140Such condition is silently ignored when requirement level is set to `optional`
141(default).
142
143Row and column numbers start at 0. Column name can be used instead of column
144number.
145
146This directive can be used more than once within same context.
147
148
149postgres_escape
150---------------
151* **syntax**: `postgres_escape $escaped [[=]$unescaped]`
152* **default**: `none`
153* **context**: `http`, `server`, `location`
154
155Escape and quote `$unescaped` string. Result is stored in `$escaped` variable
156which can be safely used in SQL queries.
157
158Because nginx cannot tell the difference between empty and non-existing strings,
159all empty strings are by default escaped to `NULL` value. This behavior can be
160disabled by prefixing `$unescaped` string with `=` sign.
161
162
163postgres_connect_timeout
164------------------------
165* **syntax**: `postgres_connect_timeout timeout`
166* **default**: `10s`
167* **context**: `http`, `server`, `location`
168
169Set timeout for connecting to the database.
170
171
172postgres_result_timeout
173-----------------------
174* **syntax**: `postgres_result_timeout timeout`
175* **default**: `30s`
176* **context**: `http`, `server`, `location`
177
178Set timeout for receiving result from the database.
179
180
181Configuration variables
182=======================
183$postgres_columns
184-----------------
185Number of columns in received result-set.
186
187
188$postgres_rows
189--------------
190Number of rows in received result-set.
191
192
193$postgres_affected
194------------------
195Number of rows affected by `INSERT`, `UPDATE`, `DELETE`, `MOVE`, `FETCH`
196or `COPY` SQL query.
197
198
199$postgres_query
200---------------
201SQL query, as seen by `PostgreSQL` database.
202
203
204Sample configurations
205=====================
206Sample configuration #1
207-----------------------
208Return content of table `cats` (in `rds` format).
209
210 http {
211 upstream database {
212 postgres_server 127.0.0.1 dbname=test
213 user=test password=test;
214 }
215
216 server {
217 location / {
218 postgres_pass database;
219 postgres_query "SELECT * FROM cats";
220 }
221 }
222 }
223
224
225Sample configuration #2
226-----------------------
227Return only those rows from table `sites` that match `host` filter which
228is evaluated for each request based on its `$http_host` variable.
229
230 http {
231 upstream database {
232 postgres_server 127.0.0.1 dbname=test
233 user=test password=test;
234 }
235
236 server {
237 location / {
238 postgres_pass database;
239 postgres_query SELECT * FROM sites WHERE host='$http_host'";
240 }
241 }
242 }
243
244
245Sample configuration #3
246-----------------------
247Pass request to the backend selected from the database (traffic router).
248
249 http {
250 upstream database {
251 postgres_server 127.0.0.1 dbname=test
252 user=test password=test;
253 }
254
255 server {
256 location / {
257 eval_subrequest_in_memory off;
258
259 eval $backend {
260 postgres_pass database;
261 postgres_query "SELECT * FROM backends LIMIT 1";
262 postgres_output value 0 0;
263 }
264
265 proxy_pass $backend;
266 }
267 }
268 }
269
270Required modules (other than `ngx_postgres`):
271
272- [nginx-eval-module (agentzh's fork)](http://github.com/agentzh/nginx-eval-module),
273
274
275Sample configuration #4
276-----------------------
277Restrict access to local files by authenticating against `PostgreSQL` database.
278
279 http {
280 upstream database {
281 postgres_server 127.0.0.1 dbname=test
282 user=test password=test;
283 }
284
285 server {
286 location = /auth {
287 internal;
288
289 postgres_escape $user $remote_user;
290 postgres_escape $pass $remote_passwd;
291
292 postgres_pass database;
293 postgres_query "SELECT login FROM users WHERE login=$user AND pass=$pass";
294 postgres_rewrite no_rows 403;
295 postgres_output none;
296 }
297
298 location / {
299 auth_request /auth;
300 root /files;
301 }
302 }
303 }
304
305Required modules (other than `ngx_postgres`):
306
307- [ngx_http_auth_request_module](http://mdounin.ru/hg/ngx_http_auth_request_module/),
308- [ngx_coolkit](http://github.com/FRiCKLE/ngx_coolkit).
309
310
311Sample configuration #5
312-----------------------
313Simple RESTful webservice returning JSON responses with appropriate HTTP status
314codes.
315
316 http {
317 upstream database {
318 postgres_server 127.0.0.1 dbname=test
319 user=test password=test;
320 }
321
322 server {
323 set $random 123;
324
325 location = /numbers/ {
326 postgres_pass database;
327 rds_json on;
328
329 postgres_query HEAD GET "SELECT * FROM numbers";
330
331 postgres_query POST "INSERT INTO numbers VALUES('$random') RETURNING *";
332 postgres_rewrite POST changes 201;
333
334 postgres_query DELETE "DELETE FROM numbers";
335 postgres_rewrite DELETE no_changes 204;
336 postgres_rewrite DELETE changes 204;
337 }
338
339 location ~ /numbers/(?<num>\d+) {
340 postgres_pass database;
341 rds_json on;
342
343 postgres_query HEAD GET "SELECT * FROM numbers WHERE number='$num'";
344 postgres_rewrite HEAD GET no_rows 410;
345
346 postgres_query PUT "UPDATE numbers SET number='$num' WHERE number='$num' RETURNING *";
347 postgres_rewrite PUT no_changes 410;
348
349 postgres_query DELETE "DELETE FROM numbers WHERE number='$num'";
350 postgres_rewrite DELETE no_changes 410;
351 postgres_rewrite DELETE changes 204;
352 }
353 }
354 }
355
356Required modules (other than `ngx_postgres`):
357
358- [ngx_rds_json](http://github.com/agentzh/rds-json-nginx-module).
359
360Sample configuration #6
361-----------------------
362Use GET parameter in SQL query.
363
364 location /quotes {
365 set_unescape_uri $txt $arg_txt;
366 postgres_escape $txt;
367 postgres_pass database;
368 postgres_query "SELECT * FROM quotes WHERE quote=$txt";
369 }
370
371Required modules (other than `ngx_postgres`):
372
373- [ngx_set_misc](http://github.com/agentzh/set-misc-nginx-module).
374
375Testing
376=======
377`ngx_postgres` comes with complete test suite based on [Test::Nginx](http://github.com/agentzh/test-nginx).
378
379You can test core functionality by running:
380
381`$ TEST_NGINX_IGNORE_MISSING_DIRECTIVES=1 prove`
382
383You can also test interoperability with following modules:
384
385- [ngx_coolkit](http://github.com/FRiCKLE/ngx_coolkit),
386- [ngx_echo](github.com/agentzh/echo-nginx-module),
387- [ngx_form_input](http://github.com/calio/form-input-nginx-module),
388- [ngx_set_misc](http://github.com/agentzh/set-misc-nginx-module),
389- [ngx_http_auth_request_module](http://mdounin.ru/hg/ngx_http_auth_request_module/),
390- [nginx-eval-module (agentzh's fork)](http://github.com/agentzh/nginx-eval-module),
391- [ngx_rds_json](http://github.com/agentzh/rds-json-nginx-module).
392
393by running:
394
395`$ prove`
396
397
398License
399=======
400 Copyright (c) 2010, FRiCKLE Piotr Sikora <info@frickle.com>
401 Copyright (c) 2009-2010, Xiaozhe Wang <chaoslawful@gmail.com>
402 Copyright (c) 2009-2010, Yichun Zhang <agentzh@gmail.com>
403 All rights reserved.
404
405 Redistribution and use in source and binary forms, with or without
406 modification, are permitted provided that the following conditions
407 are met:
408 1. Redistributions of source code must retain the above copyright
409 notice, this list of conditions and the following disclaimer.
410 2. Redistributions in binary form must reproduce the above copyright
411 notice, this list of conditions and the following disclaimer in the
412 documentation and/or other materials provided with the distribution.
413
414 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
415 "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
416 LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
417 A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
418 HOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
419 SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
420 LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
421 DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
422 THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
423 (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
424 OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
425
426
427This software includes also parts of the code from:
428
429- `nginx` (copyrighted by **Igor Sysoev** under BSD license),
430- `ngx_http_upstream_keepalive` module (copyrighted by **Maxim Dounin**
431 under BSD license).
432
433
434See also
435========
436- [ngx_rds_json](http://github.com/agentzh/rds-json-nginx-module),
437- [ngx_drizzle](http://github.com/chaoslawful/drizzle-nginx-module),
438- [ngx_lua](http://github.com/chaoslawful/lua-nginx-module),
439- [nginx-eval-module (agentzh's fork)](http://github.com/agentzh/nginx-eval-module).
440