ABOUT:
------
ngx_postgres is an upstream module that allows nginx
to communicate directly with PostgreSQL database.

Response is generated in RDS format, so it's compatible with:
rds_json, ngx_drizzle and ngx_oracle modules.


STATUS:
-------
This module is production-ready and it's compatible
with following nginx releases:

- 0.7.x (tested with 0.7.65 to 0.7.67),
- 0.8.x (tested with 0.8.0 to 0.8.48).


CONFIGURATION DIRECTIVES:
-------------------------

  postgres_server ip[:port] dbname=dbname user=user password=pass (context: upstream)
  -----------------------------------------------------------------------------------
  Set details about the database server.

  default: none


  postgres_keepalive off | max=count [mode=single|multi] [overflow=ignore|reject] (context: upstream)
  --------------------------------------------------------------------------------------------------- 
  Configure keepalive parameters:
  - max      - maximum number of keepalive connections (per worker process),
  - mode     - backend matching mode,
  - overflow - either "ignore" the fact that keepalive connection pool
               is full and allow request, but close connection afterwards
               or "reject" request with 503 Service Unavailable response.

  default: max=10 mode=single overflow=ignore


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

  default: none


  postgres_query [methods] query (context: http, server, location)
  ----------------------------------------------------------------
  Set query string (it can include variables). When methods are specified
  then query is used only for them, otherwise it's used for all methods.

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

  default: none


  postgres_rewrite [methods] condition status (context: http, server, location)
  -----------------------------------------------------------------------------
  Rewrite response status code when given condition is met (first one wins!):
  - no_changes - no rows were affected by SQL query (by design this applies only to
                 INSERT, UPDATE, DELETE, MOVE, FETCH and COPY SQL queries),
  - changes    - at least one row was affected by SQL query (by design this applies
                 only to INSERT, UPDATE, DELETE, MOVE, FETCH and COPY SQL queries),
  - no_rows    - no rows were returned in the result-set,
  - rows       - at least one row was returned in the result-set.

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

  default: none


  postgres_output none|value|row|rds [row] [column] (context: http, server, location)
  -----------------------------------------------------------------------------------
  Set output format:
  - rds   - return output in RDS format (with appropriate Content-Type),
  - row   - return all values from a single row from the result-set in clear text,
            values are separated by new line (with default Content-Type),
  - value - return single value from the result-set in clear text (with default Content-Type),
  - none  - don't return anything, this should be used only when extracting values
            with "postgres_set" for use with other modules (Content-Type isn't set).

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

  default: rds


  postgres_set $variable row column [optional|required] (context: http, server, location)
  ---------------------------------------------------------------------------------------
  Get single value from the result-set and keep it in $variable. When requirement level
  is set to "required" and value is either out-of-range, NULL or zero-length, then nginx
  returns 500 Internal Server Error. Such condition is silently ignored when requirement
  level is set to "optional" (default).

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

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

  default: none


  postgres_escape $escaped $unescaped (context: http, server, location)
  ---------------------------------------------------------------------
  Escape and quote $unescaped variable. Result is stored in $escaped
  variable which can be safely used in SQL queries.

  default: none


  postgres_connect_timeout timeout (context: http, server, location)
  ------------------------------------------------------------------
  Set timeout for connecting to the database (in seconds).

  default: 10


  postgres_result_timeout timeout (context: http, server, location)
  -----------------------------------------------------------------
  Set timeout for receiving result from the database (in seconds).

  default: 30


CONFIGURATION VARIABLES:
------------------------

  $postgres_columns
  -----------------
  Number of columns in received result-set.


  $postgres_rows
  --------------
  Number of rows in received result-set.


  $postgres_affected
  ------------------
  Number of rows affected by INSERT, UPDATE, DELETE, MOVE, FETCH or COPY SQL query.


  $postgres_query
  ---------------
  SQL query, as seen by PostgreSQL database.


EXAMPLE CONFIGURATION #1:
-------------------------
http {
    upstream database {
        postgres_server     127.0.0.1 dbname=test
                            user=monty password=some_pass;
    }

    server {
        location / {
            postgres_pass   database;
            postgres_query  "SELECT * FROM cats";
        }
    }
}

Return content of table "cats" (in RDS format).


EXAMPLE CONFIGURATION #2:
-------------------------
http {
    upstream database {
        postgres_server     127.0.0.1 dbname=test
                            user=monty password=some_pass;
    }

    server {
        location / {
            postgres_pass   database;
            postgres_query  SELECT * FROM sites WHERE host='$http_host'";
        }
    }
}

Return only those rows from table "sites" that match "host" filter
which is evaluated for each request based on its $http_host variable.


EXAMPLE CONFIGURATION #3:
-------------------------
http {
    upstream database {
        postgres_server     127.0.0.1 dbname=test
                            user=monty password=some_pass;
    }

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

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

Required modules (other than ngx_postgres):
- nginx-eval-module (agentzh's fork).


EXAMPLE CONFIGURATION #4:
-------------------------
http {
    upstream database {
        postgres_server        127.0.0.1 dbname=test
                               user=monty password=some_pass;
    }

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

Restrict access to local files by authenticating against PostgreSQL database.

Required modules (other than ngx_postgres):
- ngx_http_auth_request_module,
- ngx_coolkit.


EXAMPLE CONFIGURATION #5:
-------------------------
http {
    upstream database {
        postgres_server       127.0.0.1 dbname=test
                              user=monty password=some_pass;
    }

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

Simple RESTful webservice returning JSON responses and appropriate HTTP status codes.

Required modules (other than ngx_postgres):
- ngx_rds_json.