Prepared statement execution filter - Postgres

This filter can intercept the execution of a prepared statement and change the value of the parameter for the prepared statement.

More specifically, it intercepts packets of type Bind and allows you to modify the value of the parameters for the prepared statement.

Parameters

Query patterns

Optional. If specified, one or more patterns (separated by newlines) for the SQL of the prepared statement being executed. If not specified, then all prepared statement executions will potentially result in the execution of this filter (depending on the other parameters).

Examples:

select first_name, last_name, balance FROM biz.customers where IDENT = $1

This is a literal query: the SQL for the prepared statement must match exactly what is specified here. It's often helpful to use a request logging filter to figure out exactly what the statement looks like when it is sent to the database, as it may not be exactly what you have in the client (especially for the parameter placeholders).

regex:INSERT INTO biz\.customers \(IDENT, \w+_name\) VALUES \(\$1, \$2\)

This is a regular expression, which means that all special characters must be escaped.

Parameter patterns

Optional. If specified, one or more number=pattern expressions, separated by newlines, where number is an integer indicating the (zero-based) index of the parameter, and pattern is:

• a literal value, e.g. 42 or Jane Smith, or

• a regular expression, e.g. regex:4[5-9] or REGEX:(Smith)|(Jones), or

• a range, e.g.: range:200:500 or range:AAA:AZZ

Examples:

0=42

Specifies that this filter should only be invoked if the first parameter (index 0) has a value of 42 (regardless of type).

1=This is a test

1=regex:This.*not.*

3=range:5.5:5.9

This specifies two possible values for the second parameter (index 1). If either of these is satisfied, and the range for the fourth parameter (index 3) is also satisfied (i.e. the parameter has a value between 5.5 and 5.9 inclusive, regardless of type), then the filter will be run (assuming all the other parameters, if any, are also satisfied).

Client IPs

Optional. A list of IP addresses (IP4 and/or IP6) and/or regular expressions for IP addresses, which can be separated by commas or newlines. 

If specified, only requests from matching IP addresses will cause execution of this filter.

Example: 

12.34.56.78
1234:5678:90ab::01
regex:98\.76\..*
regex:9876:5432:.*

Users

Optional. Zero or more database user names as literals or regular expressions, separated by commas or newlines. If specified, this filter will execute only for those database users.

Example:

jdoe,hshah,mcheng

regex:dev_\w+, regex:prod_\w+

Context

The following variables will always be defined when your JavaScript executes:

• log : the log object

• context : contains all the variables you'll commonly use:

  • packet : the packet being filtered

  • result : the result object, used to cancel the current packet if desired

  • filterContext: an object containing variables attached to this filter. Any changes to this object will be visible to all invocations of this filter.

  • connectionContext: an object containing variables attached to the connection. Any changes to this object will be visible to all filters defined on this connection.